[Question] Update Rank field when Points field is updated

[mohamad-abuzaid]

Hello all, I have this "users" table.
I have this "points" field which determines the users "rank".. rank is selected from a "app_ranks" table lists each rank and the corresponding required points.

What I want is:

• When "points" value is updated, the "rank_id" field should be automatically updated to the corresponding rank id from the "app_ranks" table.

Here is my current code..

---

[1] users.go

`func GetUsersTable(ctx *context.Context) table.Table {
users := table.NewDefaultTable(ctx, table.DefaultConfigWithDriver("mysql"))

info := users.GetInfo().HideFilterArea()

info.AddField("ID", "id", db.Int).FieldSortable()	
info.AddField("Points", "points", db.Int).FieldSortable()
info.AddField("Rank", "rank", db.Varchar).
	FieldJoin(types.Join{
		BaseTable: "app_users",
		Field:     "rank_id",
		Table:     "app_ranks",
		JoinField: "id",
	})

info.SetTable("app_users").
	SetTitle("Users").
	SetDescription("User")

formList := users.GetForm()
formList.AddField("ID", "id", db.Int, form.Default).
	FieldDisplayButCanNotEditWhenUpdate().
	FieldDisableWhenCreate()
formList.AddField("Points", "points", db.Int, form.Number).
	FieldDefault("0").
	FieldOnChooseAjax("rank_id", "/ranks", func(ctx *context.Context) (bool, string, interface{}) {
		points := ctx.FormValue("points")
		var data = make(selection.Options, 0)

		ranks, err := db.WithDriver(globalConn).
			Table("app_ranks").
			Select("id", "rank").
			Where("points_required", "<=", points).
			All()

		if err != nil || len(ranks) == 0 {
			return true, "", data
		}

		rank := ranks[len(ranks)-1]
		data = selection.Options{
			{Text: rank["rank"].(string), ID: rank["id"], Selected: true},
		}

		return true, "OK", data
	})
formList.AddField("Rank", "rank_id", db.Int, form.SelectSingle).
	FieldOptionInitFn(func(val types.FieldModel) types.FieldOptions {
		points := ctx.FormValue("points")

		ranks, err := db.WithDriver(globalConn).
			Table("app_ranks").
			Select("id", "rank").
			Where("points_required", "<=", points).
			All()

		if err != nil || len(ranks) == 0 {
			return types.FieldOptions{
				{Value: "1", Text: "Newbie", Selected: true},
			}
		}

		rank := ranks[len(ranks)-1]
		return types.FieldOptions{
			{
				Value:    strconv.FormatInt(rank["id"].(int64), 10),
				Text:     rank["rank"].(string),
				Selected: true,
			},
		}
	})

formList.SetTable("app_users").
	SetTitle("Create/Edit User").
	SetDescription("Create/Edit User")

return users
 }`

---

[2] users sql

DROP TABLE IF EXISTS "app_users";
CREATE TABLE "app_users" (
"id" int(10) unsigned NOT NULL AUTO_INCREMENT,
"points" int(10) unsigned DEFAULT 0,
"rank_id" int(10) unsigned DEFAULT 1,
"created_at" timestamp NULL DEFAULT CURRENT_TIMESTAMP,
"updated_at" timestamp NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY ("id"),
KEY "rank_id" ("rank_id"),
KEY "lang_id" ("lang_id"),
CONSTRAINT "app_users_rank_id_foreign" FOREIGN KEY ("rank_id") REFERENCES "app_ranks" ("id"),
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

---

[3] ranks sql

DROP TABLE IF EXISTS 'app_ranks';
CREATE TABLE 'app_ranks' (
'id' int(10) unsigned NOT NULL AUTO_INCREMENT,
'rank' varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
'points_required' int NOT NULL DEFAULT 0,
PRIMARY KEY ('id')
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

LOCK TABLES 'app_ranks' WRITE;
INSERT INTO 'app_ranks' ('id', 'rank', 'points_required') VALUES
(1, 'Newbie', 0),
(2, 'Explorer', 100),
(3, 'Pathfinder', 300),
(4, 'Discoverer', 600),
(5, 'Trailblazer', 1200),
(6, 'Guide', 2000),
(7, 'Adventurer', 3500),
(8, 'Navigator', 5000),
(9, 'Wayfinder', 7500),
(10, 'Voyager', 10000),
UNLOCK TABLES;