Golang SQLBoiler returns nothing when running orderby

Question

I'm using SqlBoiler on Go to send requests to a PostgreSQL db, but when trying to order the database by one of the fields it returns 0 rows.

At first i run a count (like detailed below) and if the count returns more than or equal to one row then i query the database for all the results.

This returns the correct row count:

res, _ := models.MT(
        Where("(mt_mas = ? or mt_mem like ?) and mt_group = ?", uint(uid), `%"`+strconv.Itoa(uid)+`"%`, bool(mt_group_bool)),
    ).Count(CTX, DB)

This returns no rows, despite the query params being exactly the same:

res, _ := models.MT(
        Where("(mt_mas = ? or mt_mem like ?) and mt_group = ?", uint(uid), `%"`+strconv.Itoa(uid)+`"%`, bool(mt_group_bool)),
        OrderBy(`mt_mas`),
    ).Count(CTX, DB)

This is how I get all the rows after checking the row count:

res, err := models.MT(
        Where("(mt_mas = ? or mt_mem like ?) and mt_group = ?", uint(uid), `%"`+strconv.Itoa(uid)+`"%`, bool(mt_group_bool)),
        OrderBy(`mt_mas`),
    ).All(CTX, DB)

When reading the error from the request above, it prints out <nil> and everything seems fine.

The database is, as mentioned Postgres (version PostgreSQL 13.3), and the columns are as follows:

mt_mas (integer)
This column holds the uid of the owner of this row.
mt_mem (character varying [1000])
This column holds a JSON list of user members uid:s.
mt_group (boolean)
This column shows of this row is a group of not.

Example of database row:
| mt_mas | mt_mem | mt_group | | :----: | :----: | :------: | | 1 | {"1", "2"} | false |

In any case, you don't need the OrderBy in your Count query. You would only use OrderBy when you actually return the rows. — Gari Singh, Jul 31 '21 at 10:30
@Riverans edit the question by adding the generated SQL, preferably for all 3 queries. To enable logging of the generated SQL see: https://github.com/volatiletech/sqlboiler/blob/475493476401b3fd8fd0801eece058e514d284d7/README.md#debug-logging — mkopriva, Jul 31 '21 at 12:02
@mkopriva, Good idea! I can't seem to get it to work. Could I possibly ask of you to provide me an example? I use an exportable DB and CTX variable (defined in our homemade database package) as shown by the code above which might cause some trouble. :S — Riverans, Jul 31 '21 at 15:41
@Riverans if you can't make it work with `boil.DebugMode = true` then I have no idea. I don't use `sqlboiler` myself so I don't know it intimately enough to know why debugging doesn't work for you and what could be done to make it work. — mkopriva, Jul 31 '21 at 15:55
@mkopriva, Okok. I will continue trying and se if I can provide the extra information. Thank you for your help this far! :) — Riverans, Jul 31 '21 at 15:57

score 2 · Accepted Answer · answered Aug 03 '21 at 09:05

Okay, the solution was simpler than expected. @Gari Singh (https://stackoverflow.com/users/5529712/gari-singh) actually pointed this out in the comments above, but i figured i would write it here so the question registers as solved.

The solution is to simply not order in the query that counts the results. So the correct code for counting should only be:

res, _ := models.MT(
        Where("(mt_mas = ? or mt_mem like ?) and mt_group = ?", uint(uid), `%"`+strconv.Itoa(uid)+`"%`, bool(mt_group_bool)),
    ).Count(CTX, DB)

And then running the query to get the actual rows with the order, like this:

res, err := models.MT(
        Where("(mt_mas = ? or mt_mem like ?) and mt_group = ?", uint(uid), `%"`+strconv.Itoa(uid)+`"%`, bool(mt_group_bool)),
        OrderBy("mt_mas"),
    ).All(CTX, DB)

Thank you for your help! :)

Golang SQLBoiler returns nothing when running orderby

1 Answers1