每个结果的子查询或内部连接与 php 循环或...? [英] Subqueries for each result or inner join with php loop or ...?

问题描述

我想在用户搜索结果中显示他们的照片.不仅是头像照片，还有3张照片.照片在额外的表 user_photos 中.如果我每个用户都获得单行，答案将很明确 - 内连接.但我会为每个用户获得多行.

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

在这种情况下，我需要一些额外的 php 代码来合并具有相同 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'];
  //...
}

然后在视图中，我需要为用户提供第一级循环，然后为每个用户提供 $user[$result['user_id']]['photos'] 数组的第二级循环.

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?

推荐答案

您想使用 GROUP_CONCAT，所以你会得到这样的:

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;

然后你可以简单地使用 explode() 来解压数组.

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

这篇关于每个结果的子查询或内部连接与 php 循环或...?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！