NadeemAkhter's avatar

fetch data on multiple tables.

I have 2 tables one user information and other is images of that user, one user may have more than one images . I want to apply pagination query on data.i apply simple select query but they return one user with one image only dont return the arrray of images

0 likes
6 replies
RamjithAp's avatar

Use laravel relationships https://laravel.com/docs/5.5/eloquent-relationships#one-to-many

First, in your User model do

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;

class User extends Model
{
   
    public function images()
    {
        return $this->hasMany('App\Images'); //make sure your images table has user_id column
    }
}

And then in your image table model

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;

class Images extends Model
{
 
    public function user()
    {
        return $this->belongsTo('App\User');
    }
}

Now in your controller

$data = App\Post::user(1)->with('images');
return view('view_name',["data"=>$data]);

You view

@foreach($data->images as $image)
     <img src="{{ $image->url }}">
@endforeach 
NadeemAkhter's avatar

#RamjithAp thanks for your precious time, i want this through database because many other joins also apply on this table my query is like this

public function getAllCarsByCity($id)
    {
      return $data=DB::table('vehicles')
      ->where([['cities.id',$id],['images.size','s'],['vehicles.vehicle_type_id',\App\VehicleType::Car],['status','approved'],['vehicles.isDeleted','false']])
       
      ->join('manufactures','manufactures.id' , '=' , 'vehicles.manufacture_id')
      ->join('brands','brands.id','=','manufactures.brand_id')
      ->join('models','models.id' , '=' , 'vehicles.model_id')
     
      ->join('model_versions','model_versions.id','=','vehicles.model_version_id')
      ->join('cities','cities.id' , '=' , 'vehicles.city_id')
      ->join('images','images.post_id' , '=' , 'vehicles.id')
      ->join('model_years','model_years.id','=','vehicles.model_year_id')
      ->join('engine_types','engine_types.id','=','vehicles.engine_type_id')
      ->join('transmissions','transmissions.id','=','vehicles.transmission_id')
      
          

      ->select('vehicles.*','engine_types.title as engine_type','transmissions.title as transmission','brands.title as manufacture','models.title as model','model_versions.title as model_version','cities.title as city','images.url as url','model_years.year as year')
      ->orderby('vehicles.featured','desc')
      ->paginate(35);
      

    }
Snapey's avatar

Your joins will create multiple rows, one per image, so if I have a vehicle and I have three images of it then my data will be repeated 3 times, once for each image.

Consider using eloquent, its a lot more flexible for things like this.

RamjithAp's avatar

If you decided to use join queries only, here is the solution.

public function getAllCarsByCity($id)
    {
       $data = DB::table('vehicles')
      ->where([['cities.id',$id],['images.size','s'],['vehicles.vehicle_type_id',\App\VehicleType::Car],['status','approved'],['vehicles.isDeleted','false']])
      ->join('manufactures','manufactures.id' , '=' , 'vehicles.manufacture_id')
      ->join('brands','brands.id','=','manufactures.brand_id')
      ->join('models','models.id' , '=' , 'vehicles.model_id')
      ->join('model_versions','model_versions.id','=','vehicles.model_version_id')
      ->join('cities','cities.id' , '=' , 'vehicles.city_id')
      ->join('model_years','model_years.id','=','vehicles.model_year_id')
      ->join('engine_types','engine_types.id','=','vehicles.engine_type_id')
      ->join('transmissions','transmissions.id','=','vehicles.transmission_id')
      ->select('vehicles.*','engine_types.title as engine_type','transmissions.title as transmission','brands.title as manufacture','models.title as model','model_versions.title as model_version','cities.title as city','model_years.year as year')
      ->orderby('vehicles.featured','desc')
      ->get();

      $i = 0;
      $result = $data->toArray();
      foreach($result as $data){
          $images = '';
          $images = DB::table('images')->where('post_id',$data->id)->get();
          $images = $images->toArray();
          $images = ["url"=>$images]
          $result[$i] = array_merge($data,$images);
          $i++:
      }

      return $result;
    }
Snapey's avatar

@RamjithAp first line is a return so your code won't run

oh, and results would be a paginator not raw data

RamjithAp's avatar

@Snapey I just show him how to achieve with join queries, codes are not tested and accurate just copy pasted from above so.

Please or to participate in this conversation.