MySQL join 3表和格式

Question

我正在使用codeigniter，我正在尝试调用3个不同的表，并将它们连接到一个数组中。这就是我所拥有的：

$this->db->select('so_alcohol.id, 
    so_alcohol.name as name, 
    so_alcohol.category as category, 
    so_alcohol.permName as permName,
    so_alcohol.picture as alcoholPicture, 
    group_concat(so_user.firstName) as firstName,
    group_concat(so_user.lastName) as lastName,
    group_concat(so_user.picture) as userPicture,
    group_concat(so_rating.idUser) as idUser,
    group_concat(so_rating.overall) as overall,
    group_concat(so_rating.description) as description');
$this->db->from('alcohol');
$this->db->order_by("id", "desc"); 
$this->db->join('so_rating', 'so_rating.idAlcohol = so_alcohol.id', 'LEFT');
$this->db->join('so_user', 'so_user.id = so_rating.idUser', 'LEFT');
$query = $this->db->get();

我想要的输出如下：

Array
(
    [0] => stdClass Object
            (
                    [id] => 1
                    [name] => Product Name
                    [category] => Product category
                    [permName] => Product-Name
                    [alcoholPicture] => http://image.com/1.jpg
                    [idUser] => 1,2,3
                    [overall] => 100,80,50
                    [description] => Review 1,Awesome review 2, Horrible review 3
                    [firstName] => name 1, name 2, name 3
                    [lastName] => last 1, last 2, last 3
                    [userPicture] => http://userimage.com/1.jpg, http://userimage.com/2.jpg, http://userimage.com/3.jpg
                )
    [0] => stdClass Object
            (
                    [id] => 1
                    [name] => Product Name
                    [category] => Product category
                    [permName] => Product-Name
                    [alcoholPicture] => http://image.com/1.jpg
                    [idUser] => 1,2,3
                    [overall] => 100,80,50
                    [description] => Review 1,Awesome review 2, Horrible review 3
                    [firstName] => name 1, name 2, name 3
                    [lastName] => last 1, last 2, last 3
                    [userPicture] => http://userimage.com/1.jpg, http://userimage.com/2.jpg, http://userimage.com/3.jpg
                )  

)

我的问题是我怎么做我有什么工作，现在有两个问题(我发现)第一，它只输出1个结果，我需要它输出40和第二我不能so_user.id = so_rating.idUser，因为我已经group_concat(so_rating.idUser)任何想法？我甚至不确定这是否是正确的方式，因为我仍然是一个php新手。

编辑：

酒精

id        name           category        permName        picture
1         Product #1     1               Product-1       http://someurl.com/1.jpg
2         Product #2     1               Product-2       http://someurl.com/2.jpg
3         Product #3     1               Product-2       http://someurl.com/3.jpg
4         Product #4     2               Product-2       http://someurl.com/4.jpg

评级

id        idUser        idProduct        overall        description
1         1             2                100            Great Product Review 1
2         2             2                75             Great Product Review 2      
3         1             3                100            Great Product Review 3     
4         2             3                98             Great Product Review 4

用户

id        firstName        lastName        userPicture  
1         first 1          last 1          http://someurlUserPicture.com/1.jpg            
2         first 2          last 2          http://someurlUserPicture.com/2.jpg                 
3         first 3          last 3          http://someurlUserPicture.com/3.jpg                
4         first 4          last 4          http://someurlUserPicture.com/4.jpg

Answer 1

你能提供一些表格原理图来使用吗？我也没有在您的查询中看到任何GROUP BY。我猜你想要GROUP BY so_alcohol.id？如果只想按so_alcohol.id分组，则必须对其余列使用聚合函数，如max(so_alcohol.name) as name。further reading about max()

你的第二个问题实际上是没有的。您可以在任何可用列上连接，即使您不决定将其包含在结果中。示例：

SELECT
    MAX(customer.email) AS email,
    MAX(customer.name) AS name,
    GROUP_CONCAT(address.country) AS countries
FROM
    customer
JOIN
    /* customer.address_id is not in the SELECT-clause, 
       but you can still use the column to join */
    address ON customer.address_id = address.id 
GROUP BY
    customer.id

如果您正在考虑将firstName的值拆分为单个名称，请不要让mysql每行生成一个用户，并在输出时在PHP中进行分组。

Edit1: GROUP BY示例

表格产品

id   name
1    Product1
2    Product2

表评等

rating_id   product_id
1           1
2           1
3           2

加入

SELECT * 
FROM product 
JOIN rating ON product.id = rating.product_id

输出

id   name        rating_id   product_id
1    Product1    1           1
1    Product1    2           1
2    Product2    3           2

添加GROUP BY id-clause

SELECT 
    id,
    MAX(name) AS name,
    COUNT(rating_id) AS rating_count
FROM product 
JOIN rating ON product.id = rating.product_id
GROUP BY id

会将列id中具有相同值的所有元组放入一组：

id   name        rating_count
1    Product1    2
2    Product2    1

不允许选择结果中的列，也不能对其分组或使用聚合函数。但是mysql会让这一点出错，并为你提供一个合理的结果。

希望迷你教程能起作用。如果没有，我建议您在继续使用codeigniter之前更好地了解SQL。如果您不了解codeigniter在幕后做什么，您将无法控制您的输出。