Subqueries for each result or inner join with php loop or ...?

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?

What does this segment mean: merge results with same user_id and other fields, excepting photo... — hjpotter92
– hjpotter92, Commented Jul 12, 2012 at 8:15

hellsgate · Accepted Answer · 2012-07-12 08:40:50Z

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           |
+-------------------------------------+

Doa · Accepted Answer · 2012-07-12 08:31:44Z

1

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.

answered Jul 12, 2012 at 8:31

Doa

2,0155 gold badges20 silver badges36 bronze badges

Comments

DaveRandom · Accepted Answer · 2012-07-12 08:34:19Z

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.

Collectives™ on Stack Overflow

Subqueries for each result or inner join with php loop or ...?

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related