Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

OrWhere wrong in docs #1346

Open
iwasherefirst2 opened this issue Jan 25, 2024 · 3 comments · May be fixed by iwasherefirst2/sqlboiler#1
Open

OrWhere wrong in docs #1346

iwasherefirst2 opened this issue Jan 25, 2024 · 3 comments · May be fixed by iwasherefirst2/sqlboiler#1

Comments

@iwasherefirst2
Copy link

iwasherefirst2 commented Jan 25, 2024

This is a question about the docs, so I skip the "issue question", as they are not relevant for my issue.

I am still trying to figure out how to use where in query-building to create complex statments (like (A || B ) & (C || D) or something). I guess there is no type safe query.

But, what I think is wrong in the docs, is this example at https://github.com/volatiletech/sqlboiler#query-mod-system:

Where("(name=? and age=?) or (age=?)", "John", 5, 6)
// Expr allows manual grouping of statements
Where(
  Expr(
    models.PilotWhere.Name.EQ("John"),
    Or2(models.PilotWhere.Age.EQ(5)),
  ),
  Or2(models.PilotAge),
)

The where function expects first a string, but return type of Expr is a QueryMod. I also don't understand the meaning of Or2(models.PilotAge), as Or2 expects a WHERE query, right?

Would be great if you could expand on this example, how its meant to be used.

@stephenafamo
Copy link
Collaborator

Apologies, the docs are wrong.

Both Where and Expr return QueryMods. Expr can be used to group where mods. It comes it handly when using the where mods that were generated for the models.

Both of them can be used to express (A || B ) & (C || D)

Using qm.Where

qm.Where("(a = ? OR b = ?) AND (c = ? OR d = ?)", a, b, c, d),

Using qm.Expr

qm.Expr(
  models.PilotWhere.A.EQ(a),
  qm.Or2(models.PilotWhere.B.EQ(b)),
),
qm.Expr(
  models.PilotWhere.C.EQ(c),
  qm.Or2(models.PilotWhere.D.EQ(d)),
),

@iwasherefirst2
Copy link
Author

iwasherefirst2 commented Jan 30, 2024

Thank you for the quick reply and the answer.

One more question about the docs, at the same section, it shows a sequence of conditions like this:

// WHERE IN clause building
WhereIn("(name, age) in ?", "John", 24, "Tim", 33) // Generates: WHERE ("name","age") IN (($1,$2),($3,$4))
WhereIn(fmt.Sprintf("(%s, %s) in ?", models.PilotColumns.Name, models.PilotColumns.Age), "John", 24, "Tim", 33)
AndIn("weight in ?", 84)
AndIn(models.PilotColumns.Weight + " in ?", 84)
OrIn("height in ?", 183, 177, 204)
OrIn(models.PilotColumns.Height + " in ?", 183, 177, 204)

But I guess those conditions have to be wrapped also by qm.Expr ?

What about things like

Select("id", "name") // Select specific columns.
Select(models.PilotColumns.ID, models.PilotColumns.Name)
From("pilots as p") // Specify the FROM table manually, can be useful for doing complex queries.
From(models.TableNames.Pilots + " as p")

or

GroupBy("name")
GroupBy("name like ? DESC, name", "John")
GroupBy(models.PilotColumns.Name)
OrderBy("age, height")
OrderBy(models.PilotColumns.Age, models.PilotColumns.Height)

Having("count(jets) > 2")
Having(fmt.Sprintf("count(%s) > 2", models.TableNames.Jets)

Limit(15)
Offset(5)

How to wrap them? the comment in the code of qm.Expr seems to state, that only WHERE constraints can be used it it,
I guess thies means WHERE A in (..), GEQ, LEQ etc.. but not having/limit/offset/select ?

iwasherefirst2 added a commit to iwasherefirst2/sqlboiler that referenced this issue Feb 15, 2024
@iwasherefirst2 iwasherefirst2 linked a pull request Feb 15, 2024 that will close this issue
@stephenafamo
Copy link
Collaborator

Most of the mods can be used multiple times without any issues. WHERE often needs more complex situations, for example:

WHERE (a AND b) AND (c OR d)

That is why qm.Expr exists

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

Successfully merging a pull request may close this issue.

2 participants