gorm having query with 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

Can you add some newlines in the queries? Reading them while scrolling is not really handy. Also what database dialect are you generating the queries for (they look like mysql, but you know best) — Norbert van Nobelen
I updated my question. Is it better now? That's true, I'm using mysql — kkesley

Norbert van Nobelen Norbert van Nobelen · Accepted Answer · 2018-10-24T04:34:09

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.

gorm having query with OR

1 Answers