Laravel query with multiple tables

0
votes

I need to get all "Prestation" datas where status is "Disponible" and get the "first_name" and "last_name" of the Items User concerned.

I have three tables, the first is "Prestations" :

  • _id
  • name
  • price
  • created_at
  • updated_at

The second is "Items" where i only need to test the status :

  • _id
  • quantity
  • status <<<<---- "Disponible"
  • prestation_id
  • user_id

The last table is "Users" where i need the first_name and last_name:

  • _id
  • first_name
  • last_name

Prestation.php model : 

public function item()
 {
     return $this->hasMany('App\Models\Item');
 }

Item.php model :

public function prestation()
{
    return $this->belongsTo('App\Models\Prestation');
}

User.php model

public function items()
{
    return $this->hasMany('App\Models\Item');
}

I read the Laravel documentation and tried something like :

/**
//  * @param Request $request
//  * @return mixed
//  */
 public function prestationsListAvailables(Request $request, $conciergerie_select_id = null) 
 {
     $available = 'Disponible';

     $prestations = Prestation::with('item')
         ->whereHas('item', function($query) use ($available) {
             $query->whereIn('status', $available);
         })
         ->get();

     return $prestations;
 }

But at this step, nothing works. I tried simply this too and it's work :

/**
 * @param Request $request
 * @return mixed
 */
public function prestationsListAvailables(Request $request, $conciergerie_select_id = null) 
{
    $prestations = Prestation::with([
    ])
    ->get();

    return $prestations;
}

Here i get all of "Prestations" but i need to test if the status is disponible and get the first_name and last_name of the user concerned. Thank you for your help.

2
laravelrequestquery-builderlaravel-query-builder

2 Answers

0
votes

Can you try this ?

/**
//  * @param Request $request
//  * @return mixed
//  */
 public function prestationsListAvailables(Request $request, $conciergerie_select_id = null) 
 {
     $available = 'Disponible';

     $prestations = Prestation::whereHas('item', function($query) use ($available) {
             $query->where('status', $available);
         })
         ->join('items', 'items.prestation_id', '=', 'prestations.id')
         ->join('users', 'users.id', '=', 'items.user_id')
         ->get([
             'prestations.*',
             'users.firstname AS firstname',
             'users.lastname AS lastname'
         ]);

     return $prestations;
 }
0
votes

I would create a belongsToMany relation between User and Item:

Presentation

public function users()
{
    return $this->belongsToMany('App\Models\User', 'items')->withPivot(['status','quantity']);
}

Then you can query the relation with:

Presentation::with(['users' => function ($query) use ($available) {
    $query->where('status', $available); // you are filtering the pivot table
}])->get();