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


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

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', '', '=', 'imagenes_peliculas.pelicula_id')

  // 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.

asked by Eric Js Martinez 12.04.2018 в 22:56

1 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:


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:


namespace App;

use Illuminate\Database\Eloquent\Model;

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

Review the relationship documentation: link

answered by 12.04.2018 / 23:05