Creating GORM dynamic query with optional paramters

Question

I've been stuck on a GORM issue for about a full day now. I need to be able to filter a messages table on any of 4 things: sender, recipient, keyword, and date range. It also has to paginate. Filtering by sender and recipient is working, and so is pagination. So far this is the query that I have come up with, but it does not seem to work for date ranges or keywords.

Here is how I am selecting from MySQL

db.Preload("Thread").Where(query).Scopes(Paginate(r)).Find(&threadMessages)

I am creating the query like this:

var query map[string]interface{}

Then based on which parameters I am passed, I update the query like this by adding new key values to the map:

query = map[string]interface{}{"user_id": sender, "recipient_id": recipient}

For dates it does not seem to work if I try something like this:

query = map[string]interface{}{"created_at > ?": fromDate}

And for a LIKE condition is also does not seem to work:

query = map[string]interface{}{"contents LIKE ?": keyword}

The reason I chose this approach is that I could not seem to get optional inputs to work in .Where since it takes a string with positional parameters and null positional parameters seem to cause MySQL to return an empty array. Has anyone else dealt with a complicated GORM issue like this? Any help is appreciated at this point.

Answer 1

Passing the map[string]interface{} into Where() only appears to work for Equals operations, or IN operations (if a slice is provided as the value instead).

One way to achieve what you want, is to construct a slice of clause.Expression, and append clauses to the slice when you need to. Then, you can simply pass in all of the clauses (using the ... operator to pass in the whole slice) into db.Clauses().

clauses := make([]clause.Expression, 0)

if mustFilterCreatedAt {
    clauses = append(clauses, clause.Gt{Column: "created_at", fromDate})
}

if mustFilterContents {
    clauses = append(clauses, clause.Like{Column: "contents", Value: keyword})
}

db.Preload("Thread").Clauses(clauses...).Scopes(Paginate(r)).Find(&threadMessages)

Note: If you're trying to search for content that contains keyword, then you should concatenate the wildcard % onto the ends of keyword, otherwise LIKE behaves essentially the same as =:

clause.Like{Column: "contents", Value: "%" + keyword + "%"}

Answer 2

My final solution to this was to create dynamic Where clauses based on which query params were sent from the client like this:

    fields := []string{""}
    values := []interface{}{}

If, for example, there is a keyword param:

    fields = []string{"thread_messages.contents LIKE ?"}
    values = []interface{}{"%" + keyword + "%"}

And to use the dynamic clauses in the below query:

db.Preload("Thread", "agency_id = ?", agencyID).Preload("Thread.ThreadUsers", "agency_id = ?", agencyID).Joins("JOIN threads on thread_messages.thread_id = threads.id").Where("threads.agency_id = ?", agencyID).Where(strings.Join(fields, " AND "), values...).Scopes(PaginateMessages(r)).Find(&threadMessages)