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
– Gari Singh, Commented Jul 31, 2021 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: github.com/volatiletech/sqlboiler/blob/… — mkopriva
– mkopriva, Commented Jul 31, 2021 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
– Riverans, Commented Jul 31, 2021 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
– mkopriva, Commented Jul 31, 2021 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
– Riverans, Commented Jul 31, 2021 at 15:57

Riverans · Accepted Answer · 2021-08-03 09:05:50Z

2

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

answered Aug 3, 2021 at 9:05

Riverans

3461 silver badge14 bronze badges

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

Collectives™ on Stack Overflow

Golang SQLBoiler returns nothing when running orderby

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related