MYSQL and PHP: SELECT subquery without result returns array key with empty value

Question

I have a database with games, articles (reviews + previews) and videos (video reviews + video previews). I have a game with id 233 and I want to find one review, one preview, one video review and video preview, if these are present.

To do this, I'm using the following query:

SELECT (SELECT article_url
        FROM   articles
        WHERE  articles_game_id = 233
               AND artikel_sort = 'review'
               AND article_online = 1
        ORDER  BY article_datetime DESC
        LIMIT  1) AS review,

       (SELECT article_url
        FROM   articles
        WHERE  articles_game_id = 233
               AND artikel_sort = 'preview'
               AND article_online = 1
        ORDER  BY article_datetime DESC
        LIMIT  1) AS preview,

       (SELECT trailer_url
        FROM   trailers
        WHERE  trailer_game_id = 233
               AND trailer_sort = 'video review'
               AND trailer_online = 1
        ORDER  BY trailer_datetime DESC
        LIMIT  1) AS videoreview,

       (SELECT trailer_url
        FROM   trailers
        WHERE  trailer_game_id = 233
               AND trailer_sort = 'video preview'
               AND trailer_online = 1
        ORDER  BY trailer_datetime DESC
        LIMIT  1) AS videopreview
FROM   articles

However, this query has two problems:

It queries the database five times, which does not seem very efficient to me.
If there is no review (for example), the resulting PHP array does have a key/value pair with the key 'review', but its value is empty. However, I would like this key/value pair to be totally absent, so I can simply loop over the array and print each key/value pair as a link.

Does anybody know an efficient way to solve this problem? Any help is greatly appreciated!

left join will not result correct records because of ORDER BY trailer_datetime DESC LIMIT 1 — Furqan Aziz
– Furqan Aziz, Commented Dec 21, 2015 at 11:04
Are you sure that you have an efficiency problem here? Databases are very good at doing queries, and will generally optimise stuff like this behind the scenes, if it's called a lot (and if it's not called a lot, does it matter how quick it is?). Do you have evidence that your application is actually spending a lot of time on this query? — DaveyDaveDave
– DaveyDaveDave, Commented Dec 21, 2015 at 11:51

Shadow · Accepted Answer · 2015-12-21 13:19:16Z

2

Unfortunately, this seems to be a task, where you cannot get away using separate queries. However, I would use union to combine the results of the queries, rather than joins, or subqueries in the select list, or separate queries.

With union there is only 1 roundtrip between the client and the database
If a query in the union does not return any rows, then it will not be in the resultset (see requirements in OP). Pls note, that the type field's value will tell you the type of the url returned.

Sample code:

SELECT article_url, 'review' as type
        FROM   articles
        WHERE  articles_game_id = 233
               AND artikel_sort = 'review'
               AND article_online = 1
        ORDER  BY article_datetime DESC
        LIMIT  1
UNION ALL
SELECT article_url, 'preview'
        FROM   articles
        WHERE  articles_game_id = 233
               AND artikel_sort = 'preview'
               AND article_online = 1
        ORDER  BY article_datetime DESC
        LIMIT  1
UNION ALL
SELECT trailer_url, 'video review'
        FROM   trailers
        WHERE  trailer_game_id = 233
               AND trailer_sort = 'video review'
               AND trailer_online = 1
        ORDER  BY trailer_datetime DESC
        LIMIT  1
UNION ALL
SELECT trailer_url, 'video preview'
        FROM   trailers
        WHERE  trailer_game_id = 233
               AND trailer_sort = 'video preview'
               AND trailer_online = 1
        ORDER  BY trailer_datetime DESC
        LIMIT  1

An alternative solution could be to use a subquery to get the max date (or id, if the article / trailer ids are auto_increment fields). However, you would need 2 subqueries (1 or article, 1 trailers), then 2 queries in which the subqueries are joined back to article / trailer tables, and a 5th one top combine them into 1 query, so I will not describe this approach.

answered Dec 21, 2015 at 13:19

Shadow

34.5k10 gold badges67 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

TheLeonKing Over a year ago

The only downside here is that instead of performing a PDO fetch (which would result in a single array looking like Array('review' => 'review_url', 'preview' => 'preview_url'), I have to perform a fetchAll, which results in a nested array looking like

Array([0] => Array([url] => 'review_url', [type] => 'review'), [1] => Array([url] => 'preview_url', [type] => 'preview') etc.

Is there a way to remain the original array structure?

Shadow Over a year ago

Not unless you revert back to your original query. With joins the problem would be which should be the 1st one of the 4.

Collectives™ on Stack Overflow

MYSQL and PHP: SELECT subquery without result returns array key with empty value

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related