lundi 1 juin 2020

Use whereHas and query pivot table at the same time is possible in laravel?

I have 2 entities: Beneficiaries and Initiatives, those related in a pivot table beneficiary_initiative. I want all the beneficiaries related to a specific "region". But i have following error:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'beneficiaries.region' in 'where clause' (SQL: select count(*) as aggregate from `initiatives` where `state` = 1 and exists (select * from `beneficiaries` inner join `beneficiary_initiative` on `beneficiaries`.`id` = `beneficiary_initiative`.`beneficiary_id` where `initiatives`.`id` = `beneficiary_initiative`.`initiative_id` and `beneficiaries`.`region` = 120000))

Laravel version: 5.4

My database structure:

beneficiaries

  • id
  • name

initiatives

  • id
  • name

beneficiary_initiative

  • beneficiary_id
  • initiative_id
  • region
  • province

My models:

// Beneficiary.php
public function initiatives()
{
  return $this->belongsToMany(Initiative::class)->withPivot('region', 'province');
}
// Initiative.php

public function beneficiaries()
{
  return $this->belongsToMany(Beneficiary::class)->withPivot('region', 'province');
}

public static function search ($region)
{
  $query = Initiative::select(
    'initiatives.id',
    'initiatives.name'
  )->with('program')
   ->with('beneficiaries')
   ->with('contacts');

  $query->when($region !== null, function ($q) use ($region) {
      return $q->whereHas('beneficiaries', function ($q1) use ($region) {
        $q1->where('beneficiary_initiative.region', '=', $region);
      });
  });

  $initiatives = $query->get();
  return $initiatives;
}


via Chebli Mohamed

Aucun commentaire:

Enregistrer un commentaire