PostgreSQL - filter results with a LIKE on an aggregate field (string

I created an SQLFiddle scenario here but briefly:

I need to aggregate the contents of a one to many relationship into a long string and then i need to filter those result by comparing the content of that aggregate field against another string ( or rather seeing if the search string appears anywhere in the long aggregate string).

Help?

SELECT 
  Names.UserId, 
  Names.name, 
  string_agg(UserRoles.Rolename, ', ') as Roles
FROM 
  Names, UserRoles
WHERE 
  names.UserId = UserRoles.UserId
/*
** This ↓ dowsn't work
* ERROR: column "roles" does not exist
*/
/*
AND 
  Roles LIKE '%Ma%'
*/

GROUP BY 
  (Names.UserId, Names.name)
ORDER BY 
  Names.UserId;




CREATE TABLE UserRoles
    (UserId int, Rolename varchar(15))
;

CREATE TABLE Names
    (UserId int, Name varchar(15))
;

INSERT INTO UserRoles
    (UserId, Rolename)
VALUES
    (1, 'Manager'),
    (1, 'Event Organiser'),
    (2, 'Supervisor'),
    (2, 'Employee'),
    (2, 'Some otherRole')
;

INSERT INTO Names
    (UserId, Name)
VALUES
    (1, 'Jacob'),
    (2, 'Mary');

CodePudding user response：

You could use a WITH clause.

WITH table_name AS (SELECT 
  Names.UserId, 
  Names.name, 
  string_agg(UserRoles.Rolename, ', ') as Roles
FROM 
  Names, UserRoles
WHERE 
  names.UserId = UserRoles.UserId
GROUP BY 
  (Names.UserId, Names.name)
ORDER BY 
  Names.UserId)
  
SELECT
*
FROM table_name
WHERE Roles LIKE '%Ma%';

CodePudding user response：

Your assertion should appear in a HAVING clause rather than the WHERE clause. Assuming you want to return only users bearing the manager role, you may use:

SELECT 
    n.UserId, 
    n.name, 
    STRING_AGG(ur.Rolename, ', ') AS Roles
FROM Names n
INNER JOIN UserRoles ur
    ON n.UserId = ur.UserId
GROUP BY 
    n.UserId,
    n.name
HAVING
    COUNT(CASE WHEN ur.Rolename = 'Manager' THEN 1 END) > 0
ORDER BY 
    n.UserId;