MySQL加入3个表和格式

Question

I am using codeigniter and I am trying to call 3 different tables and join them together into one array. This is what I have:

$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();

The output I would like is the following:

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
                )  

)

My question is how do I make what I have work, right now there are two problems (that I have found) First it only outputs 1 result, I need it to output 40 and second I cant so_user.id = so_rating.idUser because I already group_concat(so_rating.idUser)Any Ideas? I am not even sure if this is the right way of doing it as I am still a php newbie.

EDIT:

Alcohol

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

Rating

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

User

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

can you provide some table schematics to work with? i also don't see any GROUP BY in your query. i assume you want to GROUP BY so_alcohol.id? if you want to group only by so_alcohol.id you will have to use an aggregate function on the remaining columns like max(so_alcohol.name) as name. further reading about max()

your second problem is really none. you can join on any available column even if you don't decide to have it in the result. example:

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

if you are thinking about splitting the value of firstName into single names afterwards, don't. have mysql produce one user per row and do the grouping in PHP while outputting.

Edit1: Example on GROUP BY

TABLE product

id   name
1    Product1
2    Product2

TABLE rating

rating_id   product_id
1           1
2           1
3           2

Join

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

outputs

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

adding a 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

will put all tuples that have the same value in the column id into one group:

id   name        rating_count
1    Product1    2
2    Product2    1

you are not allowed to select columns in your result, which you do not group by or use aggregate functions on. but mysql will let this slip and provide you with a reasonable result anyway.

hope that mini-tutorial works. if not i suggest to get to know SQL better before you continue to use codeigniter. if you don't understand what codeigniter does behind the curtains, you will not be able to control your output.