Golang SQLBoiler append queries dynamically

Question

I'm trying to dynamically run queries against my Postgres database, but can't fully wrap my head around it.

The solution I'm looking for is one where I can set the query dynamically, perhaps by appending parameters to the final query throughout the code, and then have only one instance of the query being executed.

As mentioned in the title I am using SQLBoiler to interface with Postgres.

Here's what I'm looking for in pseudo code:

final_query := QueryMod{
    Where("(mt_mas = ? or mt_mem like ?) and mt_group = ?", uint(uid), `%"`+strconv.Itoa(uid)+`"%`, bool(mt_group_bool)),
}

if a == 1 {
    final_query = append(final_query, And(" and mt_important = ?", bool(false)))
} else {
    final_query = append(final_query, And(" and mt_ness = ?", bool(true)))
}

res_mt_count, err := models.MTs(
    final_query,
).All(CTX, DB)

Thankful for any help along the way! :)

What problems did you encounter trying to implement the pseudo-code using real code? — mkopriva, Aug 11 '21 at 05:53
When running the code above I got the following error: `invalid composite literal type qm.QueryMod` on the line `final_query := QueryMod{`. — Riverans, Aug 11 '21 at 09:45
`QueryMod` is an interface type so yes, that expression is illegal. If you want a slice of a type you just need to prepend `[]` in front of that type. e.g. `final_query := []QueryMod{Where(...)}`. — mkopriva, Aug 11 '21 at 09:47
I tried that, but then i got the following error `cannot use final_query (type []qm.QueryMod) as type qm.QueryMod in argument to models.MTs` on the row `res_mt_count, err := models.MTs(`. Any idea how to move forward? — Riverans, Aug 11 '21 at 14:56
You can declare a custom slice type that implements the `QueryMod` interface. Then either use that instead of `[]QueryMod{...}` or keep using `[]QueryMod` but convert `final_query` to the declared slice type before passing it to `MTs`. Or, also, you could just redefine `MTs` to accept `[]QueryMod` instead of just `QueryMod`. — mkopriva, Aug 11 '21 at 15:03
Example of a custom `QueryMod` slice type: https://play.golang.org/p/F_jHJGKP53C — mkopriva, Aug 11 '21 at 15:06
Thank you your example worked like a charm! I will post the answer below! Thanks again mkopriva! :) — Riverans, Aug 11 '21 at 19:33

score 3 · Answer 1 · answered Aug 11 '21 at 19:35

mkopriva solved my problems with the following solution:

type QueryModSlice []qm.QueryMod

func (s QueryModSlice) Apply(q *queries.Query) {
    qm.Apply(q, s...)
}

func main() {
    mods := QueryModSlice{
        qm.Where("(mt_mas = ? or mt_mem like ?) and mt_group = ?", uint(uid), `%"`+strconv.Itoa(uid)+`"%`, bool(mt_group_bool)),
    }

    if a == 1 {
        mods = append(mods, qm.And(" and mt_important = ?", bool(false)))
    } else {
        mods = append(mods, qm.And(" and mt_ness = ?", bool(true)))
    }

    res_mt, err := models.MTs(mods).All(CTX, DB)

}

Thanks a bunch! :)

Golang SQLBoiler append queries dynamically

1 Answers1