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.
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.
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;
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;