CODEWITHSUNDEEP
sqlarrayspostgresql
Lee Eather
Lee Eather

Reputation: 355

My SELECT clause inside my postgres ARRAY() function seems to not be building the correct array syntax

I am building a array from a select clause in my WHERE condition

SELECT m.* 
FROM users AS u 
INNER JOIN microposts m ON u.id=m.user_id 
INNER JOIN taggings t ON m.id=t.taggable_id 
INNER JOIN tags t2 ON t2.id=t.tag_id 
WHERE ARRAY(SELECT name FROM tags)::text[] @> ARRAY['hello']

The problem part is WHERE ARRAY(SELECT name FROM tags)::text[]

This line works ok if I compare it to a single value eg ARRAY['hello']

But if it's more than one value and my ARRAY(SELECT name FROM tags)::text[] should be building the same array as ARRAY['hello', 'gday'] it is not working.

Is my ARRAY(SELECT name FROM tags)::text[] statement building the correct array? if not how come?

For clarity here is my data structure

Microposts

╔════════════════╤════════════════════╤═══════════╗
║ id             │ user_id            │ content   ║
╠════════════════╪════════════════════╪═══════════╣
║ 1              │ 2                  │ "hi"      ║
╟────────────────┼────────────────────┼───────────╢
║ 2              │ 2                  │ "gday"    ║
╟────────────────┼────────────────────┼───────────╢
║ 3              │ 1                  │ "yo"      ║
╟────────────────┼────────────────────┼───────────╢
║ 4              │ 1                  │ "mate"    ║
╚════════════════╧════════════════════╧═══════════╝

Taggings. NOTE: taggable_id is the microposts.id

╔════════════════╤════════════════════╤═══════════╗
║ id             │ taggable_id        │ tag_id    ║
╠════════════════╪════════════════════╪═══════════╣
║ 1              │ 1                  │ 1         ║
╟────────────────┼────────────────────┼───────────╢
║ 2              │ 1                  │ 2         ║
╟────────────────┼────────────────────┼───────────╢
║ 3              │ 1                  │ 3         ║
╟────────────────┼────────────────────┼───────────╢
║ 4              │ 2                  │ 3         ║
╚════════════════╧════════════════════╧═══════════╝

Tags table

╔════════════════╤════════════╗
║ id             │ name       ║
╠════════════════╪════════════╣
║ 1              │  "sport"   ║
╟────────────────┼────────────╢
║ 2              │  "cats"    ║
╟────────────────┼────────────╢
║ 3              │  "bird"    ║
╟────────────────┼────────────╢
║ 4              │  "diving"  ║
╚════════════════╧════════════╝

EDITTTT: Ok example

User input is ARRAY['sport', 'cats', 'bird']

So based on our tables this correlates to give us taggings.tag_id 1, 2, 3 and each has a taggings.taggable_id of 1 which is a match to a micropost. So this is exactly what I am inputting.

Upvotes: 0

Views: 45

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271151

If you are looking for microposts that have a particular collection of tags, then you need aggregation. With arrays you could use:

SELECT m.* 
FROM microposts m JOIN
     taggings tt
     ON m.id = tt.taggable_id JOIN
     tags t
     ON t.id = tt.tag_id 
GROUP BY m.id   -- allowed assuming id is either the primary key or unique
HAVING ARRAY_AGG(t.name ORDER BY t.name) @> ARRAY['hello'] ;

Upvotes: 2

Related Questions