4 tables - relationship - query

@webrobert Thx! Can you help a bit more please? How do I get the active coffees? The green_beans table does not have 'active' field in it. I tried this:

$origins = Origin::whereHas('greenBeans')
	->with('countries','greenBeans.coffees', fn($q) => $q->where('coffees.active', 1))
	->get();

Gives me an error:

mb_strpos() expects parameter 1 to be string, object given

@SigalZ, add the condition in the whereHas like my snippet. (I corrected a typo on the query() bit I do that for sugar

@webrobert I cannot add it on the whereHas, the field is in the coffees table not in the green_beans table.

@SigalZ

ahh.. you want the coffees. I think you can still do

	->whereHas('greenBeans.coffees', fn($q) => $q->where('active', 1))

or

   ->whereRelation('greenBeans.coffees', 'active', 1)

so

$origins = Origin::query()
    ->whereRelation('greenBeans.coffees', 'active', 1)
	->with('countries','greenBeans.coffees')
	->get();

@webrobert That didn't work well. Now I get the countries and the beans under the origins. So I can't tell which country the bean belongs to. So each country under an origin gets all the beans and coffees under it.

For example: Bean: Barbarosa Gold belongs to Brazil but I see it under all the countries.

So I see this:

Origin: South America
Country: Brazil
Bean: Barbosa Gold
Coffee: Brazil
Country: Colombia
Bean: Barbosa Gold
Coffee: Brazil
Country: Peru// (Peru doesn't even have active coffees under it)
Bean: Barbosa Gold
Coffee: Brazil

So I changed the query to:

$origins = Origin::whereHas('countries.greenBeans.coffees', fn($q) => $q->where('coffees.active', 1))
	                ->with('countries.greenBeans.coffees')
	                ->get();

but now I get countries that don't have active coffees in them.

Arggggg

@SigalZ

Just active. You are already at the coffees…

Origin::whereHas('countries.greenBeans.coffees', fn($q) => $q->where('active', 1))

@webrobert Thx, but it doesn't matter, it doesn't change anything. I still get Peru that has no active coffees under it. That's the sql query that it produces:

select * from `origins` where exists (select * from `countries` where `origins`.`id` = `countries`.`origin_id` and exists (select * from `green_beans` where `countries`.`id` = `green_beans`.`country_id` and exists (select * from `coffees` where `green_beans`.`id` = `coffees`.`green_bean_id` and `active` = 1) and `green_beans`.`deleted_at` is null) and `countries`.`deleted_at` is null) and `origins`.`deleted_at` is null

@SigalZ, timeout..

Does this return the correct coffees?

$origins = Origin::query()
    ->whereRelation('greenBeans.coffees', 'active', 1)
	->with('greenBeans.coffees')
	->get();

@webrobert Nope. Still getting coffees with active = 0.

The sql query:

select * from `origins` 
where exists (select * from `green_beans` inner join `countries` on `countries`.`id` = `green_beans`.`country_id` 
where `origins`.`id` = `countries`.`origin_id` 
and exists (select * from `coffees` where `green_beans`.`id` = `coffees`.`green_bean_id` and `active` = 1)
and `green_beans`.`deleted_at` is null and `countries`.`deleted_at` is null) and `origins`.`deleted_at` is null

I don't understand why it ignores the part and 'active' = 1. I guess that's why I have to use join and not exists in the sql query.

I guess I will need to do it in a few steps.

Thank you so much for trying to help

@SigalZ So I tried this query

select * from `origins` where exists (select * from `countries` where `origins`.`id` = `countries`.`origin_id` and exists (select * from `green_beans` where `countries`.`id` = `green_beans`.`country_id` and exists (select * from `coffees` where `green_beans`.`id` = `coffees`.`green_bean_id` and `active` = 1) and `green_beans`.`deleted_at` is null) and `countries`.`deleted_at` is null) and `origins`.`deleted_at` is null

And got 3 rows. No Peru?

https://i.imgur.com/odHCLwU.png

Tested on MariaDB 10.3.27

Are you in reality talking about relationships (countries)? If so, you need to have the same whereHas() query for them

If so then

$origins = Origin::query()
	->whereHas('greenBeans', fn($q) => $q->where('active', 1))
	->with(['countries' => function ($query) {
            $query->whereHas('greenBeans', fn($q) => $q->where('active', 1)) //fixes countries
      }, 'greenBeans' =>  function ($query) {
            $query->where('active', 1)->with('coffees'); //fixes green beans
      })
	->get();

@Sinnbeck Thx, The raw query came from one of the Eloquent queries I tried to run in a controller.

When you run it straight as an sql query, then yes, you get only the origins, but I need the countries and green beans and active coffees to be displayed, so If you run it in a controller with an eloquent query you get Peru, and Peru has no active coffees in it.

The relationship query you gave won't work either, green_beans do not have 'active' field. I need active coffees not green beans.

@SigalZ Ok got it. I tried setting up the project and this is what I cam up with. Hope its correct.

    $origins = \App\Models\Origin::query()
        ->whereHas('greenBeans.coffees', fn($q) => $q->where('active', 1))
        ->with(['countries' => function ($query) {
            $query->whereHas('greenBeans.coffees', fn($q) => $q->where('active', 1))
            ->with(['greenBeans' =>  function ($query) {
                $query->whereHas('coffees',  fn($q) => $q->where('active', 1))
                    ->with(['coffees' =>  fn($q) => $q->where('active', 1)]);
            }]);
        }])
        ->get();

Hope it works as you expect :)

@Sinnbeck OMG!!!!!!! You solved it!!!! I now pronounce you the King of Laravel Relationships.

I'm on vacation, having fun, but now, you made my day!!!

I can't thank you enough.

Next time you're in South Africa, coffee is on me :)

@SigalZ Hehe happy to help and thank you so much for the title :) Have a great vacation

@furqanDev Yes, I read about hasManyThrough, and you can see in my post that I use it in the Origin model. But after that, I don't know what to do.

I'll have a look at the package you linked.

Thank you very much.

I might have found the solution:

 $o = Origin::with(['greenBeans.beans.products' => fn($query) => $query->whereNull('deleted_at')])
 ->whereHas('greenBeans.beans.products', fn ($query) => 
 $query->whereNull('deleted_at')
 )
 ->get();

This seems to work, I'm just not sure it's the best way to do it.