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
– mkopriva, Commented Aug 11, 2021 at 5:53
When running the code above I got the following error: invalid composite literal type qm.QueryMod on the line final_query := QueryMod{. — Riverans
– Riverans, Commented Aug 11, 2021 at 9: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
– mkopriva, Commented Aug 11, 2021 at 9: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
– Riverans, Commented Aug 11, 2021 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
– mkopriva, Commented Aug 11, 2021 at 15:03

Riverans · Accepted Answer · 2021-08-11 19:35:37Z

3

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! :)

answered Aug 11, 2021 at 19:35

Riverans

3461 silver badge14 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

glenatron · Accepted Answer · 2024-04-04 12:37:48Z

0

Another way to do this is to use the built-in Expr querymod, which allows you to combine multiple expressions:

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

if a == 1 {
    final_query = qm.Expr(where, qm.And("mt_important = ?", bool(false)))
} else {
    final_query = qm.Expr(where, qm.And("mt_ness = ?", bool(true)))
}

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

answered Apr 4, 2024 at 12:37

glenatron

11.4k14 gold badges68 silver badges122 bronze badges

Collectives™ on Stack Overflow

Golang SQLBoiler append queries dynamically

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related