How to group query data based on external data?

Question

PostgreSQL

Good day.
There is a table with a list of user IDs (there is nothing except for the ID, the table is binding). When sampling, these IDs are issued. But, one user can have several IDs (this is how the system works). The table does not reflect (and cannot be reflected) that such and such an ID has aliases, so I can only pass all the links between the IDs at the query building stage.

Actually, the goal: you need to get the value aggregated by field from the table based on the transferred scalar values.

Roughly speaking, there are users 123, 456, 789 in the table. How to make a query so that by explicitly passing the values ​​123 and 789 in the query to get an aggregation by field, for example, users.count for these two rows? Something like GROUP_BY (users.id = 123 OR users.id = 789).

I would be grateful for a tip on a solution. Unfortunately, it is impossible to solve it by other means, the system is very crutch - we row as best we can.

Answer 1

It's hard to come up with a good solution without seeing the query, but let's say you can construct an intermediate table using values. Those. something like this:

SELECT ...
  FROM table1 JOIN table2 ...
  JOIN (
    VALUES (123, ARRAY[123, 789]),
           (345, ARRAY[345])
  ) t (id, aliases) ON (table1.userid = ANY(t.aliases))
 ...

Working (sort of) example:

SELECT t.id, string_agg(tt.data, ',')
  FROM 
  (
    VALUES (123, 'a'),
           (123, 'aa'),
           (789, 'b'),
           (345, 'c'),
           (345, 'd')
  ) tt (userid, data)
  JOIN
  (
    VALUES (123, ARRAY[123, 789]),
           (345, ARRAY[345])
  ) t (id, aliases) ON (tt.userid = ANY(t.aliases))
 GROUP by t.id

Answer 2

But, one user can have several IDs (this is how the system works).

So this is not a user id. Make a real user id and specify it in your aliases. Then group by real user id. Or make a lookup table and group through it.

Something like GROUP_BY (users.id = 123 OR users.id = 789).

This is hardcode. If now you solve the current problem in this way, then there will be big problems due to this approach.