CODEWITHSUNDEEP
phplaravellaravel-6
Norbert Jurga
Norbert Jurga

Reputation: 214

How to filter data in laravel by relationship of parent object

I've got 3 models: Client, Organization nad Industry. I want to filter and display clients, that are assigned to any organization in choosen Industry. Relations:

Client N-N Organization N-N Industry

One client can have multiple organizations and one organization can have multiple industries. I use form with GET method.

My controller:

class ClientListsController extends Controller
{
  public function index()
  {
      $clients = new Client;
      $queries = array();
      $columns = ['statuses'];

      foreach ($columns as $column) {
        if (request()->has($column)) {

          if ($column == 'industries') {
            // filter by industries
          }

          if ($column == 'statuses' && request($column) == 1 ) {
            $clients = optional($clients->has('organizations'))->paginate(100, ['*'], 'client_page');
          }else if($column == 'statuses' && request($column) == null ){
            $clients = Client::paginate(100, ['*'], 'client_page');
          }
          else{
            $clients = $clients->whereDoesntHave('organizations')->paginate(100, ['*'], 'client_page');
          }

          $queries[$column] = request($column);

        }else{
          $clients = Client::paginate(100, ['*'], 'client_page');
        }
      }

      $organizations = Organization::paginate(100, ['*'], 'org_page');
      return view('clientLists.index')->with(['clients' => $clients, 'organizations' => $organizations]);
  }
}

I've tried multiple things, such as looping through current result with organizations, but that returned true or false whe using has(). I've also tried using $clients = Client::all() but my point is to paginate it further so I've worked with Client::where('id', '*') so I didn't get collection, but that caused, that statuses filter does not work.

I'm working with Laravel 6. Do you have any idea how to make universal filtering with pagination? Thanks a lot!

EDIT relevant relationship in details (f.e. pivot table for Client - Organization is client_organization) with foreign keys defined. 

App\Client
    public function Organizations()
    {
      return $this->belongsToMany('App\Organization')->withTimestamps();
    }
App\Organization
  public function Clients()
  {
    return $this->belongsToMany('App\Client')->withTimestamps();
  }
  public function Industries()
  {
    return $this->belongsToMany('App\Industry')->withTimestamps();
  }
App\Industry
  public function Organizations()
  {
    return $this->belongsToMany('App\Organization')->withTimestamps();
  }

EDIT 2: I'm trying to achieve my goal through SQL query, but I'm stuck with JOINS with many-to-many relationships. 

$clients = DB::table('clients')
->leftJoin('client_organization', 'client_organization.client_id', '=', 'clients.id')->leftJoin('industry_organization', 'industry_organization.organization_id', '=', 'organizations.id')
->whereIn('industry_organization.industry_id', request($column))
->paginate(100, ['*'], 'client_page');
}

select
  count(*) as aggregate
from
  `clients`
  left join `client_organization` on `client_organization`.`client_id` = `clients`.`id`
  left join `industry_organization` on `industry_organization`.`organization_id` = `organizations`.`id`
where
  `industry_organization`.`industry_id` in (2)`

This query returns Column not found: 1054 Unknown column 'organizations.id' in 'on clause'

Upvotes: 1

Views: 1704

Answers (2)

Norbert Jurga
Norbert Jurga

Reputation: 214

I managed to do it this way:

      if (request()->industries[0] != "") {
        $clients = $clients->with('organizations')->whereHas('organizations', function($query){
          $query->with('industries')->whereHas('industries', function($query2){
            $query2->whereIn('industry_id', request()->industries);
          });
        });
      }

Thanks for your help boys :)

Upvotes: 1

party-ring
party-ring

Reputation: 1871

So, if you have the $industry you can find all of the Organisations within it using your relationships:

$organisations = $industry->organisations();

Then, you want to get all clients for each organisation:

$clients = [];

foreach ($organisations as $organisation) {
    $clients[$organisation] = $orgination->clients()->toArray();
}

Or, alternatively, to give it a go all in one query:

$industries = request($column);

$clients = DB::table('clients')
    ->leftJoin('client_organization', 'client_organization.client_id', '=', 'clients.id')
    ->leftJoin('organizations', 'organizations.id', '=', 'client_organization.id')
    ->leftJoin('industry_organization', 'industry_organization.organization_id', '=', 'organizations.id')
    ->whereIn('organisations.industry_id', $industries)
    ->paginate(10);

Try this line by line to check it out and debug it easily (try ->get() after the first leftJoin and check that it works; if that's okay, try it after the second.

Upvotes: 1

Related Questions