Postgresql have multiple ILIKE Querys in OR-CodePudding

Well hello there,

I have a question regarding SQL Queries with multiple ILIKE Statements in the Query.

I have this Query:

SELECT activitydate, userid, sum(time) as homeofficeTime FROM hours
WHERE remark ILIKE
ANY(ARRAY['%homeoffice%', '%home office%'])
AND hours.activitydate BETWEEN '2022-03-01' AND '2022-09-30'
GROUP BY activitydate, userid
ORDER BY activitydate

which works and gives me an output like this:

2022-03-15  124 90
2022-04-07  82  60
2022-05-19  82  30
2022-05-24  139 120
2022-06-14  94  30
2022-07-01  94  30
...

Then I have another Query like this (basically the same but looking for a diff column in Table):

SELECT activitydate, userid, sum(time) as homeofficeTime FROM hours
WHERE
hours.comment ILIKE
ANY(ARRAY['%homeoffice%', '%home office%']) 
AND hours.activitydate BETWEEN '2022-03-01' AND '2022-12-30'
GROUP BY activitydate, userid
ORDER BY activitydate

The Output:

2022-09-15  8   180
2022-09-22  8   120
2022-09-23  8   110

Also looks fine.

Now I want to have both Querys build in one but when I do it like this:

SELECT activitydate, userid, sum(time) as homeofficeTime FROM hours
WHERE
hours.comment ILIKE
ANY(ARRAY['%homeoffice%', '%home office%']) OR remark
ILIKE
ANY(ARRAY['%homeoffice%', '%home office%'])
AND hours.activitydate BETWEEN '2022-03-01' AND '2022-12-30'
GROUP BY activitydate, userid
ORDER BY activitydate

Then I get more Output that I want it seems to not care about the Date specified in "BETWEEN":

2019-01-07  69  525
2019-03-18  77  900
2019-03-19  77  540
2019-09-04  69  360
2019-10-21  69  480
2020-08-06  102 510
2021-11-06  155 60
2022-02-28  82  30
2022-03-15  124 90
2022-04-07  82  60
2022-05-19  82  30
...

I tried to do it like this:

SELECT activitydate, userid, sum(time) as homeofficeTime FROM hours
WHERE
hours.comment OR remark ILIKE
ANY(ARRAY['%homeoffice%', '%home office%']) 
AND hours.activitydate BETWEEN '2022-03-01' AND '2022-12-30'
GROUP BY activitydate, userid
ORDER BY activitydate

But it only tells me that: "ERROR: argument of OR must be type boolean, not type text LINE 3: hours.comment OR remark ILIKE"

Any advice is appreciated!

CodePudding user response：

Just use right parentheses

SELECT 
    activitydate, userid, sum(time) as homeofficeTime 
FROM hours
WHERE
    ( 
        hours.comment ILIKE ANY(ARRAY['%homeoffice%', '%home office%']) OR 
        remark ILIKE ANY(ARRAY['%homeoffice%', '%home office%'])
    ) AND 
    hours.activitydate BETWEEN '2022-03-01' AND '2022-12-30'
GROUP BY activitydate, userid
ORDER BY activitydate

Also you can simpily the query using REGEXP:

SELECT 
    activitydate, userid, sum(time) as homeofficeTime 
FROM hours
WHERE
    ( 
        hours.comment ~* 'home[\s]?office' OR 
        remark ~* 'home[\s]?office'
    ) AND 
    hours.activitydate BETWEEN '2022-03-01' AND '2022-12-30'
GROUP BY activitydate, userid
ORDER BY activitydate

sqlize