Search from comma separated values in column

Question

I have a table with 2 columns - id and pets.

Pets column contain abbreviated pet names separated by , [comma] as shown below

+----+-------------+
| id | pets        |
+----+-------------+
| 1  | CAT,DOG     |
+----+-------------+
| 2  | CAT,DOG,TIG |
+----+-------------+
| 3  | ZEB,MOU     |
+----+-------------+

Now I want to list all id's where pets = CAT, similarly all id's where pets = DOG etc

My initial try was to RUN the following SQL for each and every PET (CAT, DOG, etc)

select id
from "favpets"
where pets like '%CAT%'

The limitation of this simple solution is that the actual table and no. of pets are not as simple as mentioned above.

No. of such pets are more than 200. Therefore, 200 sql's have to be executed in order to list all id's corresponding to the pets

Is there any good alternative solution ? I'm using doctrine, so does doctrine provide any good implementation ?

Answer 1

With this query you will obtain all id, all pets, ordered by pet:

SELECT id, unnest(string_to_array(pets, ',')) AS mypet
FROM favpets
ORDER BY mypet;

Using it as subquery it will became easy to group and count:

SELECT mypet, COUNT(*) FROM
(
    SELECT id, unnest(string_to_array(pets, ',')) AS mypet
    FROM favpets
    ORDER BY mypet
) AS a
GROUP BY mypet;