How to generate a proper join query that brings me data from two tables?

Question

How to generate a proper join query that brings me data from two tables?

Navigation

#1 by (0 votes)

2

I am presenting inconveniences to show the data I require. I currently have two tables that are related as follows:

Peliculas    imagenes_peliculas
-id     <--> -pelicula_id
-nombre      -urlimg
-descripcion

by means of id I have that relationship the fact is that at the moment of generating the query with leftjoin that is the one that gives me the closest result even so it is not what I want.

The controller

public function ShowAllPeliculas(){
  $list_peliculas = DB::table('peliculas')
      ->leftJoin('imagenes_peliculas', 'peliculas.id', '=', 'imagenes_peliculas.pelicula_id')
      ->get();

  dd($list_peliculas);
  // return view('opciones.reporte')->with(['listado'=>$list_peliculas]);
}

This shows me something like this

At the moment I only have 50 records, but this query generates duplicate data, that is, a movie that has 2 or more images, that same number of images is repeated, the case of the record that I have broken down in the attached image.

the ideal and what would unravel since I have been trying for 4 hours is that the result is something like this

 +"id": 28
  +"codigo": "25550"
  +"nombre": "Expanded reciprocal algorithm"
  +"descripcion": "Et nostrum dolores eligendi. Assumenda maiores recusandae facere quae et doloribus totam omnis. Et doloremque est et consequuntur sunt quod dicta. Et in ut offi ▶"
  +"created_at": "2018-04-12 06:32:48"
  +"updated_at": "2018-04-12 06:32:48"
  +"pelicula_id": 5
  +"urlimg": array:3 [▼
     0 => "1523514768_20728326_1629879290375693_7772596140945660817_n.jpg"
     1 => "1523514768_20728326_162985660817_n.jpg"
     2 => "1523514768_6140945660817_n.jpg"
  ]

}

I appreciate your attention, I hope you can help me.

laravel eloquent laravel-5

asked by Eric Js Martinez 12.04.2018 в 22:56

source

1 answer

Security deploy API Laravel convert query to CakePHP 3.5 [closed]

score 0 · Accepted Answer

If you have the one-to-many relationship created with Eloquent, it would be enough to do this:

$list_peliculas = Pelicula::with('imagenes')->get();

and then you access the images after iterating the movie list: $pelicula->imagenes

The relationship from the movie model would be something like this:

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;

class Pelicula extends Model
{
    public function imagenes()
    {
        return $this->hasMany('App\Imagen');
    }
}

and in Image it would be:

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;

class Imagen extends Model
{
    public function pelicula()
    {
        return $this->belongsTo('App\Pelicula');
    }
}

Review the relationship documentation: link