PostgreSQL - in array function

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

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;