From: | Alban Hertroys <haramrae(at)gmail(dot)com> |
---|---|
To: | Arup Rakshit <ar(at)zeit(dot)io> |
Cc: | "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com>, Ron <ronljohnsonjr(at)gmail(dot)com>, "pgsql-general(at)lists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Re: Select rows when all all ids of its children records matches |
Date: | 2018-09-12 15:57:30 |
Message-ID: | 0F6A7C43-0ACA-4BA0-8D21-2C5EA24E714B@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
> On 12 Sep 2018, at 17:44, Arup Rakshit <ar(at)zeit(dot)io> wrote:
>
> Can you show me the SQL construction? Do I need to use `WITH`?
An option is to create a bit-wise OR and SUM the grouped results. If the result of these 3 bits is 7, than the post matches all three tags.
select p.id, p.name
from post p
join post_tag pt on (pt.post = p.id)
join tag t on (t.id = pt.tag)
where t.id in (1, 2, 3)
group by
case t.id
when 1 then 1
when 2 then 2
when 3 then 4
else 0
end
having sum(case t.id
when 1 then 1
when 2 then 2
when 3 then 4
else 0
end) = 7;
I used ints here for the bitwise OR, a bitstring would probably be neater.
Another approach is to aggregate the set of matching tags into an array using array_agg(). I think that's what David means. You could then check the length of the array to see if you have all 3 (or 4 or 5 or 9000).
>> On 12-Sep-2018, at 9:13 PM, David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com> wrote:
>>
>> On Wednesday, September 12, 2018, Arup Rakshit <ar(at)zeit(dot)io> wrote:
>> IN is OR, I want the AND logic. Select posts which has tag 1, 2 and 3 ( tag ids )
>>
>> Build arrays and then use the “contains” operator.
>>
>> David J.
>
Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.
From | Date | Subject | |
---|---|---|---|
Next Message | Arup Rakshit | 2018-09-12 16:08:04 | Re: Select rows when all all ids of its children records matches |
Previous Message | Arup Rakshit | 2018-09-12 15:44:27 | Re: Select rows when all all ids of its children records matches |