Gorm: Question: How to concat two OR query with AND?

[tempura-shrimp]

Your issue may already be reported! Please search on the issue track before creating one.

What version of Go are you using (go version)?

1.9.1

Which database and its version are you using?

PG

I wish to achieve something like

SELECT * FROM "stories"  WHERE "stories"."deleted_at" IS NULL AND ( x = '10' OR y = '11' ) AND ( z = '12' OR a = '13')

from document I can only chain query like:

query1 := DB.Where("x", 10).Or("y", 11)
query2 := DB.Where("z", 12).Or("a", 13)

but i cannot concat query1 and query2, is there any way to achieve this?

[weidongyao]

I think it's a bug of Gorm to do this, the combination of and&or will be useless. Maybe splicing sql is a way to solve this problem, but not elegant. If getting new idea, please keep me informed. Thanks.

[jhartman86]

@lincanli wondering this exact same thing and have a question on StackOverflow: https://stackoverflow.com/questions/53213551/how-to-build-where-query-with-grouped-ors

[jhartman86]

@lincanli @weidongyao Solving this was a necessity for me, so had to build something. Posting back here in case this helps anyone else out in the future.

This gist has code for programmatically building a nested WHERE clause with any number of nested AND/OR (...) groups, returning a struct that contains the generated SQL, and a slice of values - so its usable for passing to gorm.db.Where(). It's not the prettiest thing ever, but it does work, and it's really flexible.

Example:

where := build(constraints{
  {col: `alpha`, val: 1},
    {
      operator: "OR",
      groupOR: constraints{
        {col: `bravo`, val: 2},
        {
          operator: "AND",
          groupOR: constraints{
            {col: `charlie`, val: 3},
            {col: `delta`, val: `game on`},
          },
       },
    },
  },
})

// where.Query = (alpha = ? OR (bravo = ? AND (charlie = ? OR delta = ?)))
// where.Values = []interface{1, 2, 3, `game on`}

// Usage with GORM:
var t thing
db.Where(where.Query, where.Values...).Find(&t)
// Executes: SELECT * FROM `things` WHERE (alpha = 1 OR (bravo = 2 AND (charlie = 3 OR delta = 'game on')))

cc: @jinzhu : would something like this, such that WHERE statements can be composed by passing in a specific recursive type, be open for consideration to a PR?

[dandee]

I've also hit a wall with this issue: I've got complex query with a lot of joins and AND conditions (.Where(...).Where(...).Where(...) and so on) with one sub-condition with a bunch or ORs. Something similar to:

SELECT * from xyz WHERE a = 'c1' AND b = 'c2' AND (d = 'c3' OR e = 'c4');

// Current Gorm approach with the following code:
q := db.Table("xyz").Where("a = ?", "c1").Where("b = ?", "c2").Or("d = ?", "c3").Or("e = ?", "c4")

// would produce query like this:
// SELECT * from xyz WHERE a = 'c1' AND b = 'c2' OR d = 'c3' OR e = 'c4';

// ...which is quite useless in my case.

what I would like to see would be something similar to this approach:

orQuery := db.Or("d = ?", "c3").Or("e = ?", "c4")
q := db.Table("xyz").Where("a = ?", "c1").Where("b = ?", "c2").Where(orQuery)
// which would produce the following SQL code:
// SELECT * from xyz WHERE a = 'c1' AND b = 'c2' AND (d = 'c3' OR e = 'c4');

// this approach won't work:
q := db.Table("xyz").Where("a = ?", "c1").Where("b = ?", "c2").Where(orQuery.QueryExpr())
// because it'll produce 'SELECT * FROM "" WHERE ' at the begining of subquery which derail the execution.

I've fell back to "manual" query generation to go around this limitation which feels slightly insecure to me (especially with the handling of right-hand parameters passed in externally.)

I think that the simplest approach would be exposing a Scope method to return rendered SQL string with just a part of SQL after "WHERE" clause, something following this pattern:

orQuery := db.Or("d = ?", "c3").Or("e = ?", "c4")

// use the theoretical Condition() method: 
q := db.Table("xyz").Where("a = ?", "c1").Where("b = ?", "c2").Where(orQuery.Condition())

// to produce:
// `SELECT * from xyz WHERE a = 'c1' AND b = 'c2' AND (d = 'c3' OR e = 'c4');`

// or more maybe even more universal approach:
q := db.Table("xyz").Where("a = ?", "c1").Where("b = ?", "c2").Where("("+orQuery.Condition()+")")

[liepumartins]

Also looking for such feature, grouping where conditions is a must for ORM, if not a deal-breaker.
This approach works well: https://laravel.com/docs/5.8/queries#parameter-grouping

[jugglingjsons]

Had similar issued today ended up doing :
Db.Model(&model{}).Select("select string").Where("exists(?)", tx.SubQuery()).Group("type")
which is far from perfect but does the job

[github-actions[bot]]

This issue will be automatically closed because it is marked as GORM V1 issue, we have released the public testing GORM V2 release and its documents https://v2.gorm.io/docs/ already, the testing release has been used in some production services for a while, and going to release the final version in following weeks, we are still actively collecting feedback before it, please open a new issue for any suggestion or problem, thank you

Also check out https://github.com/go-gorm/gorm/wiki/GORM-V2-Release-Note-Draft for how to use the public testing version and its changelog