PostgreSQL comma separated column join in PHP Laravel

Question

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:

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;

Looks like an error in your datamodel, multiple values in a single column (industry_id that is). Optimise your datamodel and your problem is also gone. — Frank Heikens
– Frank Heikens, Commented May 25, 2022 at 10:51
@TimBiegeleisen 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 — Satheesh Kumar
– Satheesh Kumar, Commented May 25, 2022 at 10:59

Tim Biegeleisen · Accepted Answer · 2022-05-25 11:11:21Z

1

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.

edited May 25, 2022 at 11:11

answered May 25, 2022 at 11:08

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Satheesh Kumar Over a year ago

thanks for your help. But this FIND_IN_SET will not works for Postgres

Tim Biegeleisen Over a year ago

@SatheeshKumar Sorry, the reference to FIND_IN_SET was left there by accident, as I began my answer using your query as a starting point. The typo has been removed.

Frank Heikens · Accepted Answer · 2022-05-25 13:04:06Z

0

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.

answered May 25, 2022 at 13:04

Frank Heikens

129k26 gold badges157 silver badges153 bronze badges

Collectives™ on Stack Overflow

PostgreSQL comma separated column join in PHP Laravel

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related