gorm用OR查询

Question

I'm stuck in generating a query which is dynamically created in the run-time.

I want to create a having query with OR in the middle so e.g.

SELECT name FROM `user_group`  WHERE ((group_key = 'age' AND group_value = '20')) 

OR ((group_key = 'division' AND group_value = 'accounting')) 
OR ((group_key = 'age' AND group_value = '22')) 
OR ((group_key = 'division' AND group_value = 'kitchen'))

GROUP_BY name
HAVING 
((SUM(group_key = 'age' AND group_value = '20') > 0) 
AND 
(SUM(group_key = 'division' AND group_value = 'accounting') > 0))

OR 

((SUM(group_key = 'age' AND group_value = '22') > 0) 
AND 
(SUM(group_key = 'division' AND group_value = 'kitchen') > 0))

Note that the OR inside having statement is what I'm asking.

I got this with gorm currently:

SELECT name FROM `user_group`  WHERE ((group_key = 'age' AND group_value = '20')) 

OR ((group_key = 'division' AND group_value = 'accounting')) 
OR ((group_key = 'age' AND group_value = '22')) 
OR ((group_key = 'division' AND group_value = 'kitchen'))

GROUP_BY name
HAVING 
((SUM(group_key = 'age' AND group_value = '20') > 0) 
AND 
(SUM(group_key = 'division' AND group_value = 'accounting') > 0))

AND 

((SUM(group_key = 'age' AND group_value = '22') > 0) 
AND 
(SUM(group_key = 'division' AND group_value = 'kitchen') > 0))

Note the AND in the having statement

This is the query generation:

for _, condition := range resp.Allow.Conditions {
    for key, val := range condition {
        if len(key) <= 0 || len(val) <= 0 {
            continue
        }
        groupQuery = groupQuery.Or("(group_key = ? AND group_value = ?)", key, val)
        groupQuery = groupQuery.Having("SUM(group_key = ? AND group_value = ?) > 0", key, val)
    }
}
groupQuery = groupQuery.Group('name')

Is there any method to do this in gorm? I've looked at the documentation and my best bet that it has to be a raw sql query. I don't prefer it but if it's the only way than it's ok.

NB: I'm using mysql as the dialect

Answer 1

The output of the line:

    groupQuery = groupQuery.Having("SUM(group_key = ? AND group_value = ?) > 0", key, val)

is the block

((SUM(group_key = 'age' AND group_value = '20') > 0) 
AND 
(SUM(group_key = 'division' AND group_value = 'accounting') > 0))

AND 

((SUM(group_key = 'age' AND group_value = '22') > 0) 
AND 
(SUM(group_key = 'division' AND group_value = 'kitchen') > 0))

Which is correct. Look at all the opening and closing brackets per line:

````(COND1) AND (COND2) AND (COND3) AND (COND4)```

TO get a single or in the middle of the having statement as you request:

((SUM(group_key = 'age' AND group_value = '20') > 0) 
AND 
(SUM(group_key = 'division' AND group_value = 'accounting') > 0))

AND 

((SUM(group_key = 'age' AND group_value = '22') > 0) 
AND 
(SUM(group_key = 'division' AND group_value = 'kitchen') > 0))

which would be:

(COND1) AND (COND2) OR (COND3) AND COND(4)

would lead to a less expected result.

More logical would be to have:

(COND1) OR (COND2) OR (COND3) OR COND(4)

Or:

((COND1) AND (COND2)) OR ((COND3) AND COND(4))

This last version (which seems to be your target), can not be generated in a loop as stated, and would require a specific approach.

It looks like you are pretty much relegated to just raw SQL for this.