Subqueries for each result or inner join with php

Question

I want to display in users search results also their photos. Not only avatar photo but 3 photos. Photos are in extra table user_photos. If I would get single row per user the answer would be clear - inner join. But I will get multirows for each user.

First method I can use is join:

SELECT * FROM users INNER JOIN photos ON photos.user_id=users.user_id

In this case I need some extra php code to merge results with same user_id. Something like this:

foreach($results as $result){
  if(isset($user[$result['user_id']]['age'])){
    $user[$result['user_id']]['photos'][] = $result['photo'];
    continue;
  }      
  $user[$result['user_id']]['age'] = $result['age'];
  $user[$result['user_id']]['photos'][] = $result['photo'];
  //...
}

Then in view I need first first level loop for users and then for each user second level loop for $user[$result['user_id']]['photos'] array.

or I can use for each result subquery. None of options seems elegant, so I am wondering if there is any other way. If not, probably first option is a way to go, correct?

Answer 1

The first option is the way to go, it is much more efficient than the second option, despite the fact that you are retrieving the user data more than once (it is returned for each matching image).

The second option will be extremely inefficient as it gives the database much more work to do, as well as increasing the traffic between your script and the DB very heavily.

There is actually a 3rd option that I have previously shown can be implemented in situations like this, and that is to convert the photo information into a scalar value representing a vector that can be decoded in PHP using GROUP BY and GROUP_CONCAT() (I have used JSON for this in the past) but I can't say I would recommend it, although I will illustrate it if you would like me to.

Answer 2

You want to use GROUP_CONCAT, so you will get something like this:

select u.*, group_concat(photos.name SEPARATOR ' & ') as photonames
from users u
     join photos on photos.user_id=users.user_id
group by a.user_id;

Then you can simply use explode() to unpack the array.

Answer 3

For your original question, the first option is usually the way to go as it cuts down on the number of queries being sent to the database unnecessarily.

However, I prefer Dows answer, which is almost there I think. The one change I would make would be to make the join a LEFT JOIN so that users who have no photos are still returned in the query result, so on updating his query to:

select u.*, group_concat(photos.name SEPARATOR ' & ') as name
from users u
     join photos on photos.user_id=u.user_id
group by u.user_id;

Would return a result along the lines of:

+-------------------------------------+
|user_id   |photo                     |
+----------+--------------------------+
|1         |photo1 & photo2 & photo7  |
+----------+--------------------------+
|2         |                      NULL|
+----------+--------------------------+
|3         |photo3 & photo5           |
+-------------------------------------+