How to group by more than one row value?

Question

I am working with POSTGRESQL and I can't find out how to solve a problem. I have a model called Foobar. Some of its attributes are:

FOOBAR
check_in:datetime
qr_code:string
city_id:integer

In this table there is a lot of redundancy (qr_code is not unique) but that is not my problem right now. What I am trying to get are the foobars that have same qr_code and have been in a well known group of cities, that have checked in at different moments. I got this by querying:

SELECT * FROM foobar AS a
WHERE a.city_id = 1 
AND EXISTS (
    SELECT * FROM foobar AS b
    WHERE a.check_in < b.check_in
    AND a.qr_code = b.qr_code 
    AND b.city_id = 2
    AND EXISTS (
       SELECT * FROM foobar as c
       WHERE b.check_in < c.check_in
       AND c.qr_code = b.qr_code
       AND c.city_id = 3 
       AND EXISTS(...)
    )
)

where '...' represents more queries to get more persons with the same qr_code, different check_in date and those well known cities.

My problem is that I want to group this by qr_code, and I want to show the check_in fields of each qr_code like this:

2015-11-11 14:14:14 => [2015-11-11 14:14:14, 2015-11-11 16:16:16, 2015-11-11 17:18:20] (this for each different qr_code)

where the data at the left is the 'smaller' date for that qr_code, and the right part are all the other dates for that qr_code, including the first one. Is this possible to do with a sql query only? I am asking this because I am actually doing this app with rails, and I know that I can make a different approach with array methods of ruby (a solution with this would be well received too)

`the sent_at fields of each qr_code`..? I though `qr_code` is a column, not a table? As always, a proper table definition would clarify things. — Erwin Brandstetter, Jul 24 '15 at 04:13
Sorry. I dont know why I write sent_at, it is check_in. Edited. — jmmc, Jul 24 '15 at 13:08
[So do you have your answer?](http://meta.stackexchange.com/a/5235/169168) — Erwin Brandstetter, Jul 29 '15 at 02:02

score 0 · Answer 1 · edited May 23 '17 at 11:44

You could solve that with a recursive CTE - if I interpret your question correctly:

Assuming you have a given list of cities that must be visited in order by the same qr_code. Your text doesn't say so, but your query indicates as much.

WITH RECURSIVE
  c AS (SELECT '{1,2,3}'::int[] AS cities)  -- your list of city_id's here

, route AS ( 
   SELECT f.check_in, f.qr_code, 2 AS idx
   FROM   foobar f
   JOIN   c ON f.city_id = c.cities[1] 

   UNION ALL
   SELECT f.check_in, f.qr_code, r.idx + 1
   FROM   route  r
   JOIN   foobar f USING (qr_code)
   JOIN   c ON f.city_id = c.cities[r.idx] 
   WHERE  r.check_in < f.check_in
   )
SELECT qr_code, array_agg(check_in) AS check_in_list
FROM  (
   SELECT *
   FROM   route
   ORDER  BY qr_code, idx  -- or check_in
   ) sub
HAVING count(*) = (SELECT array_length(cities) FROM c);
GROUP  BY 1;

Provide the list as array in the first (non-recursive) CTE c.

In the recursive part start with any rows in the first city and travel along your array until the last element.

In the final SELECT aggregate your check_in column in order. Only return qr_code that have visited all cities of the array.

Similar:

Recursive query used for transitive closure

How to group by more than one row value?

1 Answers1