Extract different number of rows from multiple tables

arrayjoin;PHPquery

I have a quick question. I'm looking for the concept behind this, not necessarily a ready-to-use code, but if it's not too complex, I don't mind a quick solution. I have an app that manages accounts. These accounts are airports, for example. For each airport, I'd like to show the associated runways (could be 1, could be 10) and the associated contacts (could be 1, could be 10). The accounts are stored in the airports table, contacts are in the contacts table and runways are in the runways table. Structure is as follows:

airports (very fictional data)
+----+--------------+--------------+--------------+-------------+-------------+
| id | airport_name | airport_iata | airport_icao | airport_lat | airport_lon |
+----+--------------+--------------+--------------+-------------+-------------+
|  1 | Schiphol     | SCH          | EHSC         | 123"456'    | 456"789'    |
|  2 | Heathrow     | HEA          | EHHE         | 123"456'    | 456"789'    |
|  3 | John Kennedy | JOH          | EAJK         | 123"456'    | 456"789'    |
+----+--------------+--------------+--------------+-------------+-------------+

contacts (very fictional data)
+----+------------+------------+-----------+------------------+--------------+
| id | airport_id | first_name | last_name |  email_address   | phone_number |
+----+------------+------------+-----------+------------------+--------------+
|  1 |          1 | John       | Ruppert   | john@airport.com |    123456789 |
|  2 |          1 | Mark       | Griffin   | mark@airport.com |    123456789 |
|  3 |          2 | Lia        | Isaac     | lia@gh.nl        |    123456789 |
+----+------------+------------+-----------+------------------+--------------+

runways (very fictional data)
+----+------------+------------+--------+----------+----------------+
| id | airport_id | designator | length | surface  | landing_system |
+----+------------+------------+--------+----------+----------------+
|  1 |          1 | 28R        | 4544m  | tarmac   | ILS (CAT-1)    |
|  2 |          1 | 12L        | 3288m  | concrete | ILS(CAT-3)     |
|  3 |          1 | 14R        | 2644m  | tarmac   | VASI           |
+----+------------+------------+--------+----------+----------------+

What I'm looking for is a query (or multiple queries) that will give me one row with one airport to start with. I'm on a page like airports.php?id=1, so I want to see details of Schiphol. This airport has 2 contacts, so I also want to be able to loop both contacts using a foreach (PHP) and list the contacts. I also want to be able to show 3 runways for this airport and access each column using a foreach.

I tried this:

SELECT airports.*, contacts.*, runways.*
FROM airports
LEFT JOIN contacts ON airports.id = contacts.airport_id
LEFT JOIN runways ON airports.id = runways.airport_id
WHERE airports.id='$id';

My first problem is that it somehow returns double the maximum the number of rows in any table. For example, runways has most rows (3) for airport with id 1, hence I'm getting 6 rows (6 times the airport, 3 times 2 contacts and 2 times 3 runways). Second problem is that by using join (any form), I'm getting a table with 6 rows (correct number of rows wouldn't help too much either) and I can't loop through the results, as each row will contain all airport details, all contact details, all runways.

Can I get an array with 1 element for airport, an array with 2 elements for contacts and an array with 3 elements for runways without using multiple statements? I've been told JOINS is the way to go, but at the moment, it doesn't feel that way. What am I doing wrong?

Thanks for your time!

Best Answer

Your JOIN is behaving perfectly normally.

If you have one airports row, and no matching contacts or runways, you would get something like this:

 Airport  | Contact | Runway
----------+---------+--------
 Schiphol |   NULL  |  NULL

If you had one airport, one contact, and one runway, you'd get:

 Airport  | Contact | Runway
----------+---------+--------
 Schiphol | Ruppert |  28R

However, when you've got multiple matching contacts and runways, and there's no connection between a contact and a runway, then each contact will get paired with each runway, as you've seen:

 Airport  | Contact | Runway
----------+---------+--------
 Schiphol | Ruppert |  28R
 Schiphol | Ruppert |  12L
 Schiphol | Ruppert |  14R
 Schiphol | Griffin |  28R
 Schiphol | Griffin |  12L
 Schiphol | Griffin |  14R

This is called a cross-product. You're getting double the rows you think you want because you're crossing 2 rows with 3 rows (2 * 3 = 6). If you had 7 contacts and 5 runways, you'd be getting 35 rows.

What your best solution is will depend on what you're DBMS is capable of.

Worst case scenario, run three queries: one with the airport results, one with the contacts results, and one with the runways results. Pull all three results sets into PHP. If you're bringing back results for more than one airport, you'd loop through the airports results, and (inside that loop) loop through the other two result sets to pull out the contacts and runways for the current airport.

Many DBMSes provide functions that would let you combine the rows for each airport's contacts or runways into XML or JSON. In that case, you'd have one main query, but you'd have two sub-queries within it to generate the two objects for the other tables. It's be something like (pseudo-code only!):

SELECT airport_name, airport_iata, airport_icao
      ,(SELECT <XML/JSON func>(first_name, last_name, email_address, phone_number)
          FROM contacts
         WHERE airport_id = airports.id
       ) as contacts_obj
      ,(SELECT <XML/JSON func>(designator, length, surface, landing_system)
          FROM runways
         WHERE airport_id = airports.id
       ) as runways_obj
  FROM airports
 WHERE airports.id = 1;

Again, pseudo-code only; you'll need to figure out the details of the function for your DBMS.

Barring an explicit XML or JSON function, most DBMSes have the ability to create string aggregates. This would work much like the function above, except more manually; you'd probably build a string for each row (first_name + '|' + last_name + '|' + ...), and use the aggregate function to combine the multiple contacts into a single string (with a different separator, of course).

Exclude users without emails

Assuming we only want users that actually have emails. Users without emails are ignored. The reason I went with this assumption at first is that all your queries do that already:

LEFT JOIN emails on users.id = emails.user_id
WHERE emails.email LIKE 'a' || '%%'

By adding a WHERE condition on emails.email you effectively convert your LEFT JOIN to a plain [INNER] JOIN and exclude users without emails. Detailed explanation:

Query with LEFT JOIN not returning rows for count of 0

2nd query rewritten

Your 2nd query does not work as advertised, results are not "descending by number of emails". You have to nest the result of count() in another CTE or subquery and run dense_rank() on it. You cannot nest window functions in the same query level.

SELECT u.name, e2.*
FROM  (
   SELECT *, dense_rank() OVER (ORDER BY n_emails, users.id) AS rnk
   FROM  (
      SELECT user_id, id AS e_id, e_mail
           , count(*) OVER (PARTITION BY user_id) AS n_emails          
      FROM   emails
      WHERE  email LIKE 'a' || '%'  -- one % is enough
      ) e1
   ) e2
JOIN   users u ON u.id = e2.user_id
WHERE  rnk < 3
ORDER  BY rnk;

This should be fastest if the predicate is selective enough (selects only a small fraction of all emails). Two window functions with rows sorted differently have their price, too.

A major point is to run the subquery on emails only - which is possible if the preliminary assumption holds.

3rd query improved

If, on the other hand, the predicate WHERE e.email LIKE 'a' || '%' is not very selective, your 3rd query is probably faster, even if it reads from the table twice - but the second time only desired rows. Also improved:

SELECT e.user_id, u.name,
       e.id AS e_id, e.e_mail, sq.n_emails
FROM  (
   SELECT user_id, count(*) AS n_emails
   FROM   emails
   WHERE  email LIKE 'a' || '%'
   GROUP  BY user_id
   ORDER  BY count(*) DESC, user_id  -- break ties
   LIMIT  2  OFFSET 0
   ) sq
JOIN   emails e USING (user_id)
JOIN   users  u ON u.id = e.user_id
WHERE  e.email LIKE 'a' || '%'
ORDER  BY sq.n_emails DESC;

Include users without emails

You can either include the users table in the inner query again, similar to what you had before. But you have to pull the filter on email into the join condition!

SELECT u.name, e2.*
FROM  (
   SELECT *, dense_rank() OVER (ORDER BY n_emails, users.id) AS rnk
   FROM  (
      SELECT u.id AS user_id, u.name, e.id AS e_id
           , count(e.user_id) OVER (PARTITION BY u.id) AS n_emails          
      FROM   users u
      LEFT   JOIN emails e ON e.user_id = u.id
                          AND e.email LIKE 'a' || '%'  -- !!!
      ) e1
   ) e2
WHERE  rnk < 3
ORDER  BY rnk;

Which will be a bit more expensive.

Since you retrieve users with the most emails first, users without emails will rarely be in the result. To optimize performance, you could use a UNION ALL with LIMIT:

(  -- parentheses required
SELECT u.name, e2.user_id, e2.e_id, e2.e_mail, e2.n_emails
FROM  (
   SELECT *, dense_rank() OVER (ORDER BY n_emails, users.id) AS rnk
   FROM  (
      SELECT user_id, id AS e_id, e_mail
           , count(*) OVER (PARTITION BY user_id) AS n_emails          
      FROM   emails
      WHERE  email LIKE 'a' || '%'  -- one % is enough
      ) e1
   ) e2
JOIN   users u ON u.id = e2.user_id
WHERE  rnk < 3      -- adapt to paging!
ORDER  BY rnk
)
UNION ALL
(    
SELECT u.name, u.user_id, NULL AS e_id, NULL AS e_mail, 0 AS n_emails  
FROM   users       u
LEFT   JOIN emails e ON e.user_id = u.id
                    AND e.email LIKE 'a' || '%'
WHERE  e.e.user_id IS NULL
)
OFFSET 0      -- adapt to paging!
LIMIT  2      -- adapt to paging!

Detailed explanation:

Optimize a query on two big tables

`MATERIALIZED VIEW`

I would consider materializing the result for two reasons:

Subsequent queries are much faster.
You don't have to operate on a moving target. You speak of paging, and if users get new emails between pages, your whole sort order may be moot.

Build a MV from the 2nd query without LIMIT (REFRESH MATERIALIZED VIEW), then return the first page etc. It's a matter of policy, when you refresh the MV again.

Postgresql – Aggregate rows of type array

Order of source rows irrelevant, source is array column

Your updated requirements are much simpler for two reasons:

We don't need to establish any order among source rows.
And the array already establishes order among array elements.

Simple table:

CREATE TABLE vals (j int[]);

Simple query:

SELECT ARRAY(SELECT elem FROM vals, unnest(j) elem) AS arr;

Order of source rows relevant, source are integer columns

Not as simple. Based on this table:

CREATE TABLE vals (i int, j int);

There are various ways to preserve order. I think this should be among the fastest - with an ARRAY constructor:

SELECT ARRAY(
   SELECT t.elem
   FROM  (SELECT i, j FROM vals ORDER BY i, j) v
        , LATERAL (VALUES (v.i), (v.j)) t(elem)
   ) AS arr;

My first draft had ORDER BY i, j after the LATERAL join, which would not guarantee the order of columns. I pulled the ORDER BY into a subselect to produce the correct order now. This works, but it's not documented. To be absolutely sure, you have to add an explicit ORDER BY later:

SELECT ARRAY (
   SELECT elem
   FROM   vals v, LATERAL (VALUES (1, v.i), (2, v.j)) t(ordr, elem)
   ORDER  BY v.i, v.j, ordr
   ) AS arr;

About guaranteed order:

PostgreSQL unnest() with element number

About the VALUES expression in a LATERAL join:

SELECT DISTINCT on multiple columns

Another way with - this time with array_agg():

SELECT array_agg(elem) AS arr
FROM  (
   SELECT i, j, 1 AS ordr, i AS elem FROM vals
   UNION ALL
   SELECT i, j, 2 AS ordr, j AS elem FROM vals
   ORDER BY i, j, ordr
   ) sub;

The ARRAY constructor is faster, array_agg() is easier to integrate with multiple result columns.

SQL Fiddle.

The query in your test case would actually work in Postgres 9.5 (currently beta, to be released in the course of 2015). Quoting the release notes for pg 9.5:

Allow array_agg() and ARRAY() to take arrays as inputs (Ali Akbar, Tom Lane)

However, you get a 2-dimenstional array instead, not your desired result. You can easily implement the same (but slower) aggregate function in older versions:

Is there something like a zip() function in PostgreSQL that combines two arrays?

Best Answer

Related Solutions

Postgresql – Sort by number of related rows in referencing table