Treating Query Many-to-Many

Question

I got a question about Many-to-Many relationship and I hope I’m not asking a repeated question, but I haven’t found the solution to my doubt in any corner.

I happen to have a table of images, a table of papers and a table of relationship that makes a many-to-many relationship between the first two.

Each job can have several images related to it. So I did a query as follows:

select jobs.*, f1.file_name, f2.file_name 
from jobs 
    left JOIN photos_per_job p ON p.job_id = jobs.id
    left JOIN fotos f1 on p.photo_id = f1.id 
    inner join fotos f2 on jobs.foto_capa = f2.id

And I got the result below:

My problem right now is organizing that information in the view. If you notice well the work that has three photos repeats in the result three times, but when I go to show the work to the user I will show one job at a time and each job will have its series of photos.

Is there any way to organize the result in a way that facilitates my loop in the view or to organize it better I need to work the loop itself?

Answer 1

You can implement this logic on your own PHP, change your select so:

SELECT jobs.*, f1.file_name, f2.file_name AS nome_foto_capa 
from jobs 
    LEFT JOIN photos_per_job p ON p.job_id = jobs.id
    LEFT JOIN fotos f1 on p.photo_id = f1.id 
    INNER JOIN fotos f2 on jobs.foto_capa = f2.id

Use this PHP code to handle the database output:

$trabalhos = array();
/* LAÇO QUE PERCORRE OS RESULTADOS RETORNADO PELO MYSQL (PODE SUBSTIRUIR PELO QUE VOCE UTILIZA)*/
foreach($results as $row){
    if(!isset($trabalhos[$row['id']])){
        $trabalhos[$row['id']] = array(
            'id' => $row['id'], 
            'titulo' => $row['titulo'], 
            'foto_capa' => $row['foto_capa'], 
            'descricao' => $row['descricao'], 
            'bg_color' => $row['bg_color'], 
            'font_color' => $row['font_color'], 
            'time' => $row['time'], 
            'nome_foto_capa' => $row['nome_foto_capa']
        );
    }
    $trabalhos[$row['id']]['fotos'][] = $row['file_name'];  
}

---

Or Voce can group the name of the photos using the GRUOUP_CONCAT, so the name of the photos will appear concatenated only in a field separated by comma, then and only give one explodes by the comma. But depending on the amount of photos do not recommend using this approach, because GROUP_CONCAT has a character limit.

SELECT jobs.*, GROUP_CONCAT(f1.file_name) AS fotos, f2.file_name AS nome_foto_capa 
from jobs 
    LEFT JOIN photos_per_job p ON p.job_id = jobs.id
    LEFT JOIN fotos f1 on p.photo_id = f1.id 
    INNER JOIN fotos f2 on jobs.foto_capa = f2.id
GROUP BY jobs.id

Answer 2

You have to group it.

Do something like this:

select jobs.*, f1.file_name, f2.file_name 
from jobs j 
    left JOIN photos_per_job p ON p.job_id = j.id
    left JOIN fotos f1 on p.photo_id = f1.id 
    inner join fotos f2 on j.foto_capa = f2.id
 group by j.id, j.titulo, j.foto_capa, j.descricao, j.bg_color,
   j.font_color, j.time, f2.file_name