would it be possible in postgres to get a count of sql hits on a select statement

Question

Say I have the following table in Postgres 9.4:

items 
id | name | tag_ids
1 tire -1--2-
2 wheel -1--3-
3 transmisson -3-

I'd like to do something like:

select id, tags from items where tag_ids ilike '%-1-%' or tags ilike '%-3-%';

but if it hit twice (like id 2), return a count of hits. Would this be possible? Or possibly have it come back as two records and then process in application code.

edit

ok, so assuming we had a second table like this:

items_tags
item_id tag_id
1 1
1 2
2 1
2 3
3 3

Is there a way to join them and then group by the number of "hits" - I guess just selected rows and then order in reverse? And give a represntation of the hit tag_ids

select i.name from items inner join items_tags using (id) where items_tags.tag_id in (1,3);

but how would I do a count and order. I'm assuming a group by but this is beyond me.

edit 2

so I can't get this second query to work. It seems like it should but isn't:

select tag_ids, (tag_ids ilike '%-1-%')::int + (tag_ids ilike '%-11-%')::int as hits 
from items order by hits desc; 

saying ERROR: column "hits" does not exist

select tag_ids, (tag_ids ilike '%-1-%')::int + (tag_ids ilike '%-11-%')::int
as hits from items where hits > 0 order by hits desc; 

Answer 1

About the error in "edit 2"

The first statement is valid (your post is confusing things), the error message only applies to the 2nd statement, where you try to reference the output column hits in the WHERE clause and that's not possible. Details:

• Use array expression from subquery in ANY() condition

Solution: repeat the (longish) expression in the WHERE clause or use a subquery or, rather, spell out individual WHERE conditions:

SELECT tag_ids
 , (tag_ids LIKE '%-1-%')::int
 + (tag_ids LIKE '%-11-%')::int AS hits
FROM items
WHERE (tag_ids LIKE '%-1-%' OR
 tag_ids LIKE '%-11-%') -- cannot reference out column in WHERE
ORDER BY hits DESC; -- but you can in ORDER BY

This is faster because Postgres only need to verify a single match to qualify the row, and the use of indexes is possible (a trigram index in this case).

Also: replaced ILIKE with just LIKE - there are no relevant characters involved.

The best solution depends on missing information, but arrays would be more elegant and easier to index. Like you had in your related question:

• Order result by count of common array elements

A normalized design like @ypercube already suggested is probably best.

Answer 2

Yes, you can use this in the select list:

 (case when tags ilike '%-1-%' then 1 else 0 end) 
 + (case when tags ilike '%-3-%' then 1 else 0 end) 
 as hits 

or alternatively (convert the boolean values to integers (TRUE -> 1, FALSE -> 0) and then add:

 (tags ilike '%-1-%')::int + (tags ilike '%-3-%')::int
 as hits 

But the query (and many others) would be much easier if you normalized the design and store the item-tag relationship in a separate, junction table.