CODEWITHSUNDEEP
phplaravelpostgresqllaravel-8
Satheesh Kumar
Satheesh Kumar

Reputation: 51

PostgreSQL comma separated column join in PHP Laravel

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

enter image description here

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

FIND_IN_SET() will not work 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;

Upvotes: 0

Views: 348

Answers (2)

Frank Heikens
Frank Heikens

Reputation: 127222

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.

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521804

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.

Upvotes: 1

Related Questions