PostgreSQL comma separated column join in PHP Laravel-CodePudding

How to write join query for PostgreSQL comma separated column join in PHP Laravel. FIND_IN_SET() will not works as I am using PostgreSQL

I need to join industry table with Company table. While company can have multiple industries they are entered in "industry_id" coloumn and values are separated by comma.

FIND_IN_SET() will not works as I am using PostgreSQL

Mysql Query

$companyData = "SELECT comp.* 
                                        
              FROM companies AS comp 
              LEFT JOIN `industries` as `indus` ON
              find_in_set(indus.id, comp.indusrty_id) >0           
              WHERE comp.id = ".$companyId;

CodePudding user response：

You may use the following trick on Postgres to workaround there being no FIND_IN_SET available:

SELECT comp.*
FROM companies AS comp
INNER JOIN industries AS indus
    ON ',' || comp.indusrty_id || ',' LIKE '%,' || indus.id || ',%'        
WHERE comp.id = ?;

Note that a much better solution here would be to fix your data model and stop storing CSV like this. Create a junction table in which each record stores one industry ID and one company ID.

CodePudding user response：

As a workaround you could also create your own PostgreSQL function find_in_set():

CREATE OR REPLACE FUNCTION FIND_IN_SET(text, text)
RETURNS BOOLEAN
IMMUTABLE
LANGUAGE SQL
AS
$$
    SELECT $1 =ANY(STRING_TO_ARRAY($2,','));
$$;

It doesn't fix the real problem nor the performance issues you're going to face, but it works.