Is the where statement reusable? 🍵

[sswanv]

Hello, now there is a need for paging, you need to find out the data and total number of the corresponding page, and whether the conditional statement can be reused, example:

queryStmt := SELECT(User.AllColumns, UserInvite.AllColumns).
	FROM(UserInvite.INNER_JOIN(User, User.UserID.EQ(UserInvite.UserID))).
	WHERE(UserInvite.InviteUserID.EQ(Int64(9527)))

countStmt := SELECT(COUNT(User.UserID)).
        FROM(UserInvite.INNER_JOIN(User, User.UserID.EQ(UserInvite.UserID))).
	WHERE(UserInvite.InviteUserID.EQ(Int64(9527)))

[go-jet]

Hi @sswanv . Yes, you can reuse the statement. According to your example, the only difference is the projections:

var projectionList ProjectionList

if isCount {
	projectionList = ProjectionList{COUNT(User.UserID)}
} else {
	projectionList = ProjectionList{User.AllColumns, UserInvite.AllColumns}
}


queryStmt := SELECT(projectionList).
	FROM(UserInvite.INNER_JOIN(User, User.UserID.EQ(UserInvite.UserID))).
	WHERE(UserInvite.InviteUserID.EQ(Int64(9527)))

The same applies for WHERE condition. Take a look at FAQ.

[sswanv]

Thank you for helping !

[sswanv]

Hi, @go-jet, there is one more question.
Using jet, I don't know how to design a reasonable paging conditional query. Now the columns of jet are specified by variables in the table. I like this design very much, but it also makes the conditional query inflexible (this is my understanding. ), please give more advice!

func (a *UserInviteAction) GetFiled(name string) Column {
	for _, column := range User.AllColumns {
		if column.Name() == name {
			return column
		}
	}
	return nil
}

func (a *UserInviteAction) GetInviteUser(ctx context.Context, db qrm.DB, searchInfo *search.SearchInfo, userID int64, isAdminUser bool) {
	var sortInfo struct {
		Sort  string
		Order string
	}
	var pageInfo struct {
		Page     int64
		PageSize int64
	}

	queryStmt := UserPool.SELECT(userInviteExpectAutoSetColumns).
		FROM(UserInvite.INNER_JOIN(User, User.UserID.EQ(UserInvite.UserID))).
		WHERE(UserInvite.Depth.EQ(Int64(1)))

	column := a.GetFiled(sortInfo.Sort)
	if column != nil {
		if sortInfo.Order == "DESC" {
			queryStmt = queryStmt.ORDER_BY(column.DESC())
		} else {
			queryStmt = queryStmt.ORDER_BY(column.ASC())
		}
	}
	queryStmt.OFFSET(pageInfo.Page * pageInfo.PageSize).LIMIT(pageInfo.PageSize)

	// TODO There is also a query for the total number of items based on conditions,
	// which is actually the demand for paging query. I don’t know how to write it well.
}

[go-jet]

You can retrieve total number of items in single query. There are couple of ways. One way is to use COUNT(*) OVER().
Also, when constructing a query, it's a bit cleaner to construct clauses first and then the main query. For instance:

var userInviteExpectAutoSetColumns ProjectionList
// construct projection list

var orderBy OrderByClause
// construct orderBy 

queryStmt := SELECT(
		userInviteExpectAutoSetColumns, 
		COUNT(STAR).OVER().AS("total_count"),
	).
	FROM(
		UserInvite.
			INNER_JOIN(User, User.UserID.EQ(UserInvite.UserID)),
	).
	WHERE(UserInvite.Depth.EQ(Int64(1))).
	ORDER_BY(orderBy).
	OFFSET(pageInfo.Page * pageInfo.PageSize).
	LIMIT(pageInfo.PageSize);

var dest struct {
	model.User                  // guess
	Invites []model.UserInvite  // guess 

	TotalCount int64
}

err := queryStmt.QueryContext(ctx, db, &dest)

[sswanv]

What is the best way to pass the orderby parameter through http body, because the column of jet is formulated in the form of variables, I am a little confused, I hope your reply !

[go-jet]

string should be fine. GetFiled method looks good, except column can't be nil, because pagination requires rows to be in some order.