PostgreSQL - in array function

Question

I have returned records in the form:

id, group_name, {user_groupid1, user_groupid2, ...,  user_groupidn}

The query returns all groups in a system plus I want to return in the same result set whether a user belongs to a group or not.

First I tried to use a subquery in the select statement to set the third column to a boolean value and it worked like charm but the big problem is that I use Java+Hibernate and Hibernate won't work with subqueries in select statements if you want to pass the result to a constructor (and that's exactly what I want). So I though of using maybe an SQL function where there are 2 parameters, the first is an ID (long), the second is an array or a set of IDs and I'd like to know whether the ID is contained in the set or not. In the example above I used a function called array_agg, so it concatenates the given IDs to an array but it's not necessarily the form the 2nd param has to be. It just a set of Ids.

Before that I came to the idea to solve this in SQL, I returned the IDs as a String array above then I processed it in Java (splitting, parsing) and I don't really like that, so that's why I need another solution.

Any help is appreciated!

cheers,

b

3 tables are involved: user (id, name), group (id, name), usergroupcontact (id, user_id, group_id, status). in a simplified way, otherwise all of them are too big and other attributes are irrelevant in this case. — Balázs Németh
– Balázs Németh, Commented Jan 16, 2011 at 12:35
select g.id, g.name, g.id in (select ginner.id from group ginner join usergroupcontact ugc join user u where u.id=:userID) from group g where g.name like '%:name%' - that was the pseudo code of my original query. — Balázs Németh
– Balázs Németh, Commented Jan 16, 2011 at 12:38
Just a side note: it's not a good idea to use reserved words as table names (user, group). But I still don't understand what you are up to. If you have a list of all groups and all user_ids that belong to that group, what exactly is it you want to get out of that? I suggest you post some sample data and the expected result from that. Btw: that extra id in usergroupcontact probably doesn't make sense as (user_id, group_id) is a perfect PK unless a user can have multiples status in a group — user330315
– user330315, Commented Jan 16, 2011 at 12:51
the table names are different, this is just a simplified example. also, the connector table has much more function than a simple join table, that's why it has ID. it's much more complicated but i didn't want to post full tables, just the relevant parts :) — Balázs Németh
– Balázs Németh, Commented Jan 16, 2011 at 13:12

score 1 · Accepted Answer · 2011-01-16 13:04:27Z

1

That second comment made me understand what you want ;)

You are on the right track:

SELECT g.id,
       g.name,
       (SELECT COUNT(*)
        FROM usergroupcontact ug2
        WHERE ug2.group_id = g.id
        AND   ug2.user_id = :user_id) as user_is_member
FROM group_ g
WHERE g.name LIKE '%:name%';

This will give you 0 in the column user_is_member if the user is not part of that group or 1 if that user is part of the group.

(Note I used group_ as the table name to avoid the use a reserved words)

Edit
If you want to get a list of all users and groups and check if a user is a member of a group, then the following might be want you want:

SELECT u.id AS user_id,
       u.name AS user_name,
       g.id AS group_id,
       g.name AS group_name,
       CASE
         WHEN ug.id IS NULL THEN 'not member'
         ELSE 'member'
       END AS is_member
FROM user_ u 
  CROSS JOIN group_ g 
  LEFT JOIN usergroupcontact ug ON ug.user_id = u.id AND ug.group_id = g.id;

edited Jan 16, 2011 at 13:04

answered Jan 16, 2011 at 12:58

user330315

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

3 Comments

Balázs Németh Over a year ago

thanks for the answer, i'm going to try out soon. but i'm gonna have to translate sql to hql, that makes always things complicated :s

user330315 Over a year ago

Can't Hibernate run regular SQL queries?

Balázs Németh Over a year ago

yes, I guess it's possible, never tried though. but usually you use named queries to return entities in your application then you start to return DTOs with more complex queries, and in the end you use regular SQLs and you don't understand anymore why to use Hibernate and not plain SQL everywhere :) I want to keep it simple and readable. but your first answer is good enough and works well. thanks!

Collectives™ on Stack Overflow

PostgreSQL - in array function

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related