Home / Questions / 155397
Accepted
Eric Js Martinez
Asked: 2020-04-13 12:56:40 +0800 CST 2020-04-13 12:56:40 +0800 CST

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

  • 772

I am presenting inconveniences at the moment of displaying the data that I require. I currently have two tables that are related as follows:

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

Through id I have that relationship. The case is that when generating the query with leftjoin, which is the one that gives me the closest result, it is still not what I want.

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 enter image description here

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

the ideal and for what I would unravel since I have been trying to do it 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

1 Answers

  1. Best Answer

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

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

    and then you access the images like this after iterating the list of movies: $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');
    }
}

    Check out the relationships documentation: https://laravel.com/docs/5.6/eloquent-relationships#one-to-many

    • 0