Is the where statement reusable? :tea:

[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.