MySql duplicated values in a join using GROUP_CONC

问题:

Let's say I have this schema :

 _________                               _________
| Table 1 |                             | Table 2 |
|---------|          _______    ________|---------|
| id_a    |___      | link1 |   |       | id_b    |
| name    |   |     |-------|   |       | info    |
|_________|   |_____| id_a  |___|       | data    |
     |              | id_b  |           |_________|
     |              |_______|
     |
     |               _______
     |______________| link2 |_______
                    |-------|       |
                    | id_a  |       |   _________
                    | id_c  |       |__| Table 3 |
                    |_______|          |---------|
                                       | id_c    |
                                       | email   |
                                       |_________|

With these informations in the database :

Table 1

 _____________
| id_a | name |
|------|------|
|   1  | foo  |
|______|______|

Table 2

 _____________________
| id_b | info | data  |
|------|------|-------|
|   1  | bar  | corge |
|   2  | kux  | corge |
|   3  | fred | quux  |
|______|______|_______|

Table 3

 _________________
| id_c | email    |
|------|----------|
|   1  | [e-mail]  |
|   2  | [e-mail]  |
|   3  | [e-mail]  |
|   4  | [e-mail]  |
|   5  | [e-mail]  |
|______|__________|

link1

 _____________
| id_a | id_b |
|------|------|
|   1  |   1  |
|   1  |   2  |
|   1  |   3  |
|______|______|

link2

 _____________
| id_a | id_c |
|------|------|
|   1  |   1  |
|   1  |   2  |
|   1  |   3  |
|   1  |   4  |
|   1  |   5  |
|______|______|

When I try to retrieve the datas that I want, I use this syntax:

SELECT t1.*,
       GROUP_CONCAT(DISTINCT t2.info SEPARATOR  '|') AS info,
       GROUP_CONCAT(DISTINCT t2.data SEPARATOR  '|') AS data,
       GROUP_CONCAT(DISTINCT t3.email SEPARATOR '|') AS email
FROM table1 AS t1
LEFT JOIN link1 AS l1
ON l1.id_a = t1.id_a
LEFT JOIN table2 AS t2
ON t2.id_b = l1.id_b
LEFT JOIN link2 AS l2
ON l2.id_a = t1.id_a
LEFT JOIN table3 AS t3
ON t3.id_c = l2.id_c
WHERE t1.id_a = ?
GROUP BY t1.id_a

The problem is that when I have two or more same values in a column (example with "Table 2" / "data") the result dosn't give the same number of concatenated values :

array(
    'id_a'  => '1',
    'info'  => 'bar|kux|fred',
    'data'  => 'corge|quux',
    'email' => '[e-mail]|[e-mail]|[e-mail]|[e-mail]|[e-mail]'
)

And as I need the concatenated values to be exactly the same number to use them later in my code as a single output (for example Bar Corge, Kux Corge, Fred Quux), I would like to know if it is possible to get them as I'm expecting directely in the request ?

回答1:

So I answer myself. What I was looking for is CONCAT_WS. My request become then

SELECT t1.*,
       GROUP_CONCAT(DISTINCT CONCAT_WS(
            ' ', t2.info, t2.data
       ) SEPARATOR  ', ') AS info,
       GROUP_CONCAT(DISTINCT t3.email SEPARATOR '|') AS email
FROM table1 AS t1
LEFT JOIN link1 AS l1
ON l1.id_a = t1.id_a
LEFT JOIN table2 AS t2
ON t2.id_b = l1.id_b
LEFT JOIN link2 AS l2
ON l2.id_a = t1.id_a
LEFT JOIN table3 AS t3
ON t3.id_c = l2.id_c
WHERE t1.id_a = ?
GROUP BY t1.id_a