一个获得嵌套结果的MySQL查询？

Question

我正在开发一个PHP / MySQL分类搜索引擎，人们可以输入一些关键字，结果页面应该：

1)列出发布与这些关键词匹配的列表的用户

和

2)在这些用户名下面，列出与这些关键词匹配的列表

我只想用一个MySQL查询来实现这一点，但我不确定这是否可能。

实际上，我可以通过以下查询获得用户列表：

SELECT u.id, u.firstname 
FROM user u 
WHERE 
u.id 
IN (SELECT user_id FROM product WHERE make LIKE '%Porsche%')

现在，我想知道如何在同一个查询中获得产品列表。如果在一个查询中不可能实现，那么最优化的方法是什么？

Answer 1

就像这样：

SELECT u.id, u.firstname, p.* 
  FROM user u 
  INNER JOIN product p 
  ON u.id=p.user_id
WHERE p.make LIKE '%Porsche%';

Answer 2

我会用“反向”来做这个工作：

1. 获取所有产品匹配标题与匹配的用户id和名字。
2. 按用户在PHP中排序结果

有时候，用什么方式做事更快，而不是对MySQL要求太高。MySQL在简单查询上更快，而且只要逻辑正确，PHP就可以很好地对数据进行排序。

下面是一个例子：

1-获取数据(MySQL)：

SELECT p.*, u.id AS user_id, u.firstname AS user_firstname  
FROM `products` AS p 
INNER JOIN `user` AS u ON u.id = p.user_id 
WHERE p.make LIKE '%Porsche%' 
ORDER BY p.make, u.id

我不知道products表中列的完整列表，但是它应该为每一行生成如下内容：

id, make, user_id, user_firstname

使用PHP对结果进行2排序(假设您使用PDO，我强烈建议使用PDO)：

// Data will contain the sorted results
$data = array();
// This variable will be used to be sure results owning by a certain user are children of this user
$curent_index = null;
while($row = $query->fetch(PDO::FETCH_ASSOC)) {
    // Assign index to row user id (if different from current)
    if($current_index != $row['user_id']) {
        $current_index = $row['user_ic'];
    }

    // Create entry matching the user (if doesn't exist)
    if(!array_key_exists($current_index, $data)) {
        $data[$current_index] = array(
            'firstname' =>  $row['firstname'],
            'products'  =>  array()
        );
    }

    // Add product in data user array

    $data[$current_id]['products'][$row['id']] = array(
        'make'  =>  $row['make'],
        // whatever other fields product have
    );

}

这应该会产生像这样的结果数组：

1 =>    array(
    'firstname' =>  'Toto',
    'products'  =>  array(
        1   =>  array(
            'make'  =>  'My Porsche'
        ),
        2   =>  array(
            'make'  =>  'Porsche, an history of red cars'
        ),
        //...
    )
),
3   =>  array(
    'firstname' =>  'Titi',
    'products'  =>  array(
        7   =>  array(
            'make'  =>  'Porsche or Ferrari?'
        ),
        10  =>  array(
            'make'  =>  'Why Porsches are so expensive?'
        ),
        // ...
    )
)   

Answer 3

查询应该是

SELECT u.id, u.firstname, p.* 
  FROM user u 
  INNER JOIN product p 
  ON u.id=p.user_id
WHERE p.make LIKE '%Porsche%' LIMIT 10;

这将限制前10个结果。在你的帖子里，你没有问过任何关于限制的问题。