Обсуждение: Select rows when all all ids of its children records matches

Поиск
Список
Период
Сортировка

Select rows when all all ids of its children records matches

От
Arup Rakshit
Дата:
I have a table posts(id, name), posts_tags(post_id, tag_id) and tags (id, name) ... I want to get all posts which has tag id 1, 2 and 3 for example. How should I do this? I tried ALL, but it didn’t work.

Those tag ids comes from UI by users, so I am looking for generic approach.


Thanks,

Arup Rakshit



Re: Select rows when all all ids of its children records matches

От
Ron
Дата:
Maybe this:
select p.id, p.name
from posts p,
     posts_tags pt,
     tags t
where t.id in (1, 2, 3)
  and t.id = pt.tag_id
  and pt.post_id = p.id;


On 09/12/2018 10:23 AM, Arup Rakshit wrote:
I have a table posts(id, name), posts_tags(post_id, tag_id) and tags (id, name) ... I want to get all posts which has tag id 1, 2 and 3 for example. How should I do this? I tried ALL, but it didn’t work.

Those tag ids comes from UI by users, so I am looking for generic approach.


Thanks,

Arup Rakshit




--
Angular momentum makes the world go 'round.

Re: Select rows when all all ids of its children records matches

От
Arup Rakshit
Дата:
IN is OR, I want the AND logic. Select posts which has tag 1, 2 and 3 ( tag ids )


Thanks,

Arup Rakshit



On 12-Sep-2018, at 8:58 PM, Ron <ronljohnsonjr@gmail.com> wrote:

Maybe this:
select p.id, p.name
from posts p,
     posts_tags pt,
     tags t
where t.id in (1, 2, 3)
  and t.id = pt.tag_id
  and pt.post_id = p.id;


On 09/12/2018 10:23 AM, Arup Rakshit wrote:
I have a table posts(id, name), posts_tags(post_id, tag_id) and tags (id, name) ... I want to get all posts which has tag id 1, 2 and 3 for example. How should I do this? I tried ALL, but it didn’t work.

Those tag ids comes from UI by users, so I am looking for generic approach.


Thanks,

Arup Rakshit




--
Angular momentum makes the world go 'round.

Re: Select rows when all all ids of its children records matches

От
"David G. Johnston"
Дата:
On Wednesday, September 12, 2018, Arup Rakshit <ar@zeit.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. 

Re: Select rows when all all ids of its children records matches

От
Arup Rakshit
Дата:
Can you show me the SQL construction? Do I need to use `WITH`?


Thanks,

Arup Rakshit



On 12-Sep-2018, at 9:13 PM, David G. Johnston <david.g.johnston@gmail.com> wrote:

On Wednesday, September 12, 2018, Arup Rakshit <ar@zeit.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. 

Re: Select rows when all all ids of its children records matches

От
Alban Hertroys
Дата:
> On 12 Sep 2018, at 17:44, Arup Rakshit <ar@zeit.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
matchesall 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.g.johnston@gmail.com> wrote:
>>
>> On Wednesday, September 12, 2018, Arup Rakshit <ar@zeit.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.



Re: Select rows when all all ids of its children records matches

От
Arup Rakshit
Дата:
I tried :

WITH posts_tags_cte AS (
        SELECT post_id, array_agg(tag_id) as tags
        FROM posts_tags
        WHERE tag_id in (1, 2)
        GROUP BY post_id
)
SELECT posts.id FROM posts_tags_cte JOIN posts ON posts.id = posts_tags_cte.post_id
WHERE posts_tags_cte.tags @> array[1, 2]::int8[]

But it gives me all the posts.


Thanks,

Arup Rakshit



On 12-Sep-2018, at 9:14 PM, Arup Rakshit <ar@zeit.io> wrote:

Can you show me the SQL construction? Do I need to use `WITH`?


Thanks,

Arup Rakshit



On 12-Sep-2018, at 9:13 PM, David G. Johnston <david.g.johnston@gmail.com> wrote:

On Wednesday, September 12, 2018, Arup Rakshit <ar@zeit.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. 


Re: Select rows when all all ids of its children records matches

От
Rob Sargent
Дата:



On 09/12/2018 10:08 AM, Arup Rakshit wrote:
I tried :

WITH posts_tags_cte AS (
        SELECT post_id, array_agg(tag_id) as tags
        FROM posts_tags
        WHERE tag_id in (1, 2)
        GROUP BY post_id
)
SELECT posts.id FROM posts_tags_cte JOIN posts ON posts.id = posts_tags_cte.post_id
WHERE posts_tags_cte.tags @> array[1, 2]::int8[]

But it gives me all the posts.


Aren't you looking for cte.tags = array[1,2]?

Re: Select rows when all all ids of its children records matches

От
Arup Rakshit
Дата:
Hi Rob,

I figured it out. thanks. It is giving correct data.

Aren't you looking for cte.tags = array[1,2]?

posts_tags_cte has tags column, so I am using it.

Thanks,

Arup Rakshit



On 12-Sep-2018, at 9:47 PM, Rob Sargent <robjsargent@gmail.com> wrote:



On 09/12/2018 10:08 AM, Arup Rakshit wrote:
I tried :

WITH posts_tags_cte AS (
        SELECT post_id, array_agg(tag_id) as tags
        FROM posts_tags
        WHERE tag_id in (1, 2)
        GROUP BY post_id
)
SELECT posts.id FROM posts_tags_cte JOIN posts ON posts.id = posts_tags_cte.post_id
WHERE posts_tags_cte.tags @> array[1, 2]::int8[]

But it gives me all the posts.


Aren't you looking for cte.tags = array[1,2]?