Many to many relation, IN operator and possibility of improper results

Question

I have a problem with creating optimal SQL query. I have private messages system where user can send single message to many of users or groups of users. Recipients are stored in single text column (don't ask me why is that I wasn't responsible for designing that) like that:

[60,63,103,68]

Additionaly I've added new text column where is placed group which user belongs to so I can have as a groups in database:

[55,11,11,0]

Now I want to get all users (receivers) and their groups. I have table where relation between user and group id. The problem is that single user can belong to multiple groups, for example user 60 can be in group ID 55 and 11. I would like to do it in the most optimal way (there can be 50+ receivers stored in column...) so I can write query like that:

SELECT u.name, u.last_name, g.group_name

                    FROM 
                        user u
                    LEFT JOIN
group g ON u.id = g.user_id
                    WHERE
                        u.id IN (".$users.") and
g.id IN (".$groups.")

Unfortunately group name returned by query might by not proper - connected with the group ID i placed in WHERE. I may create PHP foreach and get user and his group using IDs I have:

foreach($user as $key => $single)
{
$sql = "...
      where u.id = $single AND g.id = $group[$key] ";
}

but I think this is very bad way. Is there any way to get user and specified group in single query?

Is it possible to change the db? And could you paste the colums of both tables here? So we have a better view? — Naruto, Feb 14 '14 at 14:57
It's: users, groups, messages and user to groups relation tables. I will provide columns soon. — webrama.pl, Feb 14 '14 at 15:02
@Dieter can you clarify what you mean by change the db? Do you mean the table structure or move off of Postgres? — Kuberchaun, Feb 14 '14 at 16:25

score 1 · Accepted Answer · edited Jun 20 '20 at 09:12

Since users and groups are only linked by their ordinal positions in the list, you need to make use of that.

The quick and dirty method would be to unnest() in parallel:

SELECT u.name, u.last_name, g.group_name
FROM  (   
   SELECT unnest(string_to_array('3,1,2', ',')::int[])    AS usr_id  -- users
        , unnest(string_to_array('10,11,12', ',')::int[]) AS grp_id  -- groups
  ) sel
JOIN   usr_grp ug USING (usr_id, grp_id)
JOIN   usr u USING (usr_id)
JOIN   grp g USING (grp_id);

Note how I replaced SQL key words like user or group as identifiers.

-> SQLfiddle

This way, elements with the same ordinal positions in the array (converted from a comma-separated list) form a row. Both arrays need to have the same number of elements or the operation will result in a Cartesian product instead. That should be the case here, according to your description. Add code to verify if that condition might be violated.

Cleaner alternatives

While the above works reliably, it is a non-standard Postgres feature of SRF (set returning functions) which is frowned upon by some.

There are cleaner ways to do it. And the upcoming version 9.4 of Postgres will ship a new feature: WITH ORDINALITY, allowing for much cleaner code. This related answer demonstrates both:
PostgreSQL unnest() with element number

Incredible. Looks like it works with PGSQL 9.1 which I'm using. — webrama.pl, Feb 14 '14 at 18:37

Many to many relation, IN operator and possibility of improper results

1 Answers1

Cleaner alternatives

Linked