postgres sql 9.3 string_agg function not working on PHP

Question

I have this set of tables:

users table:

id    username
 1      user

groups table:

id    name
 1     g1
 2     g2

users_groups table:

user_id    group_id
   1          1
   1          2

and i want to obtain this result:

id    username    user_groups
 1      user         g1,g2

i found that using string_agg would solve the problem, and it did on postgres console, but when i used the same query on php i got this message

SQLSTATE[HY000]: General error: 1 no such function: string_agg

here's my query:

select users.id, users.username, string_agg(groups.name, ', ')
from users
inner join users_groups on users.id = users_groups.user_id
inner join groups on groups.id = users_groups.group_id
group by users.id

by the way I'm using:

postgresql-9.3
php 5.6.0
apache 2
windows 7
laravel 5.0

First of all, you should `GROUP BY users.id, users.username`. What is the data type of the `groups.name` column? — Patrick, Jul 22 '15 at 01:43
@Patrick i did `group by users.username` nothing changed, and `groups.name` is varchar — yazfield, Jul 22 '15 at 16:26
@Renzo the php code has nothing special, it's just a test using laravel, here it is though: `dd(DB::table('users')->select(DB::raw("users.id, users.username, string_agg(groups.name, ', ')"))->join('users_groups', 'users.id', '=', 'users_groups.user_id')->join('users_groups', 'groups.id', '=', 'users_groups.group_id')->groupBy('users.id', 'users.username')->get());` — yazfield, Jul 22 '15 at 16:49

score 1 · Answer 1 · answered Jul 23 '15 at 00:18

Apart from the missing GROUP BY on users.username there is nothing wrong with the query, so the problem may be on the PHP side. A solution that is likely to work is to turn your query into a view and then call that from PHP:

CREATE VIEW users_with_groups AS
  SELECT users.id, users.username, string_agg(groups.name, ', ') AS groups
  FROM users
  JOIN users_groups ON users.id = users_groups.user_id
  JOIN groups ON groups.id = users_groups.group_id
  GROUP BY users.id, users.username;

Your PHP code now becomes a rather simple DB::table('users_with_groups')->select, instead of the complex statement you have now. (Note that a VIEW in SQL is like a "virtual" table, you can apply all operations on it just like on a regular table.)

This approach is in general a good idea: PostgreSQL has many more options for SELECT statements than what is practically possible with PHP (or any other framework for that matter). And you will have all your complex queries together in one place (the database), making security profiling and maintenance far easier.

Yeah I figured it was in the PHP side, but still don't know what's the problem, i'll keep looking. A view is a good idea to avoid this problem, it slipped my mind, thanks @Patrick — yazfield, Jul 23 '15 at 03:31

postgres sql 9.3 string_agg function not working on PHP

1 Answers1