Eloquent Sub Query

In Laravel exists whereHasMorph and orWhereHasMorph

Docs: https://laravel.com/docs/7.x/eloquent-relationships#querying-polymorphic-relationships

@michaloravec I saw that but how would you modify the query to use it properly?

$weekIds = $diary->weeks->pluck('id');
        $products = Product::query()
            ->whereHasMorph(
                'productable',
                ['App\Diary', 'App\Week'],
                function (Builder $query, $type) use($diary,$weekIds){
                    if ($type === 'App\Diary') {
                        $query->orWhere('productable_id', $diary->id);
                    }
                    if ($type === 'App\Week') {
                        $query->orWhereIn('productable_id', $weekIds);
                    }
                })->get();

gives me the error Column not found: 1054 Unknown column 'products.productable_type'

Probably like this

$products = Product::whereHasMorph('productable', [
    'App\Diary', 'App\Week'
], function ($query, $type) use ($diary, $weekIds) {
        if ($type === 'App\Diary') {
            $query->where('id', $diary->id);
        }

        if ($type === 'App\Week') {
            $query->orWhereIn('id', $weekIds);
        }
    }
)->get();

In which table do you have productable? Because this will work only if your products table has productable

@michaloravec this is the error that I get for that

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'products.productable_type' in 'where clause' (SQL: select * from `products` where ((`products`.`productable_type` = App\Diary and exists (select * from `diaries` where `products`.`productable_id` = `diaries`.`id` and `id` = 13)) or (`products`.`productable_type` = App\Week and exists (select * from `weeks` where `products`.`productable_id` = `weeks`.`id` or (`id` in (51, 52, 53, 54))))))

it seems to be looking in the products table not the productable table but the end result of the query its getting all the products in the diary, so would you need a subquery ?

It's in another table then productable.productable, but just change first productable for your relationship name.

$products = Product::whereHasMorph('productable.productable', [
    'App\Diary', 'App\Week'
], function ($query, $type) use ($diary, $weekIds) {
        if ($type === 'App\Diary') {
            $query->where('id', $diary->id);
        }

        if ($type === 'App\Week') {
            $query->orWhereIn('id', $weekIds);
        }
    }
)->get();

this is in my Product Model:

public function productable()
    {
        return $this->morphTo('productable');
    }

when I add 'productable.productable' its giving me the error:

BadMethodCallException
Call to undefined method App\Product::productable.productable()

You have in another table (which I still don't know the name) foreign key product_id

So in your Product model

public function relationshipToModelWIthProductable()
{
    return $this->hasMany('App\ModelWithProductable');
}

Then in that table with productable should be

public function productable()
{
    return $this->morphTo();
}

Look in the docs for table structure https://laravel.com/docs/7.x/eloquent-relationships#polymorphic-relationships

Sorry @michaloravec trying to explain everything here

I have a productable table which is more or less a pivot table that will link to the Diary / Week which contains the product_id here is a screenshot of the productable table: https://share.getcloudapp.com/P8ubb5e1

Then I have trying to query all products which are in this is the Product Model, I added this to my products model as per your instructions

public function productable()
    {
        return $this->hasMany(Productables::class,'product_id');
    }

for this part here:

public function productable()
{
    return $this->morphTo();
}

should I create the productable model and add that to it?

In Product model

public function productables() // but probably this change for something better
{
    return $this->hasMany(Productables::class);
}

In Productables model

public function productable()
{
    return $this->morphTo();
}

Then this should work

$products = Product::whereHasMorph('productables.productable', [
    'App\Diary', 'App\Week'
], function ($query, $type) use ($diary, $weekIds) {
        if ($type === 'App\Diary') {
            $query->where('id', $diary->id);
        }

        if ($type === 'App\Week') {
            $query->orWhereIn('id', $weekIds);
        }
    }
)->get();

Ok @michaloravec I got that all setup now the error is :

Call to undefined method App\Product::productables.productable()

but I can run it through tinker like so:

Psy Shell v0.10.4 (PHP 7.4.5 — cli) by Justin Hileman
>>> $p = Product::find(1)
[!] Aliasing 'Product' to 'App\Product' for this Tinker session.
=> App\Product {#4237
     id: 1,
     supplier_id: 1,
     name: "omnis",
     long_desc: "Dicta eveniet suscipit deserunt facere dolore necessitatibus
 provident atque. Maxime qui rerum sint quaerat non est dolore. Ut eos pariat
ur suscipit fuga illo aut dolorem consequatur. Quaerat velit totam aut possim
us.",
     release_date: "2018-04-18",
     active: 1,
     created_at: "2020-08-07 20:20:48",
     updated_at: "2020-08-07 20:20:48",
     media: Illuminate\Database\Eloquent\Collection {#4236
       all: [],
     },
   }
>>> $p->productables->first()->productable
=> App\Diary {#4231
     id: 12,
     user_id: 442,
     name: "Similique et adipisci quia vero et dolorem dolorem.",
     type: "gree",
     grow_medium: "hy",
     published_at: "2020-08-07 20:20:54",
     created_at: "2020-08-07 20:20:54",
     updated_at: "2020-08-07 20:20:54",
   }
>>>

It seems that whereHasMorph doesn't work with nested relationship so try this one

$products = Product::whereHas('productables', function ($query) use ($diary, $weekIds) {
    $query->whereHasMorph('productable', [
        'App\Diary', 'App\Week'
    ], function ($query, $type) use ($diary, $weekIds) {
            if ($type === 'App\Diary') {
                $query->where('id', $diary->id);
            }

            if ($type === 'App\Week') {
                $query->orWhereIn('id', $weekIds);
            }
        }
    );
})->get();

Making headway @michaloravec ok so the results produced don't seem to be correct though that query is producing 44 results for $products however when I run these queries

dump($diary->products); //PRODUCES 1 Result
dump($diary->weeks->pluck('products')->collapse()); //PRODUCES 5 RESULTS

also I tried changing

$query->where('id', $diary->id);
$query->where('productable_id', $diary->id);

but the results still returned 44 so maybe the wherehasmorph filter isn't running at all ?

Same diary or data of the weeks can be used in more products. So I think that query result is correct.

I think its the way its joining up this is the query that is being produced by the one we have done so far

SELECT
	*
FROM
	`products`
WHERE
	EXISTS (
		SELECT
			*
		FROM
			`productables`
		WHERE
			`products`.`id` = `productables`.`product_id`
		AND (
			(
				`productables`.`productable_type` = 'App\Diary'
				AND EXISTS (
					SELECT
						*
					FROM
						`diaries`
					WHERE
						`productables`.`productable_id` = `diaries`.`id`
					AND `id` = 13
				)
			)
			OR (
				`productables`.`productable_type` = 'App\Week'
				AND EXISTS (
					SELECT
						*
					FROM
						`weeks`
					WHERE
						`productables`.`productable_id` = `weeks`.`id`
					OR (`id` IN(51, 52, 53, 54))
				)
			)
		)
	)

But the query I think it needs to be is:

SELECT
	*
FROM
	`products`
WHERE
	EXISTS (
		SELECT
			*
		FROM
			`productables`
		WHERE
			`products`.`id` = `productables`.`product_id`
		AND (
			(
				`productables`.`productable_type` = 'App\Diary'
				AND
				`productables`.`productable_id` = 13
			)
			OR (
				`productables`.`productable_type` = 'App\Week'
				AND		
				`productables`.`productable_id` IN(51,52,53,54)
				
			)
		)
	)

Got it the orWhereIn was causing the issue @michaloravec I switched it to wherein on the id

Ok so this will be probably the final query

$products = Product::whereHas('productables', function ($query) use ($diary, $weekIds) {
    $query->whereHasMorph('productable', [
        'App\Diary', 'App\Week'
    ], function ($query, $type) use ($diary, $weekIds) {
            if ($type === 'App\Diary') {
                $query->where('id', $diary->id);
            }

            if ($type === 'App\Week') {
                $query->whereIn('id', $weekIds);
            }
        }
    );
})->get();

@michaloravec thanks alot for all your help, one other question for you if you don't mind since you kind of understand the table structure

If I have a product now with a specific ID how would you determine the diaries that use this product since its a polymorphic table and the week belongs to a specific diary you would need to figure out a way to do a subquery to attach the App\Week with the Diary_id ?

You filter products by diary and weeks of diary so you already have that diary. Otherwise use eager loading for that

https://laravel.com/docs/7.x/eloquent-relationships#eager-loading

@michaloravec I get that part but say on my product page I want to list all diaries

$product = Product::find(1);
$diaries = Diary::
			//See if this product is used on the diary itself
				//if it is return the diary
			// see if the product is used on the week
				//if it is used on a week find the corresponding diary that has this week and return it

In Diary model

public function productables()
{
    return $this->morphMany('App\Productables', 'productable');
}

public function weeks()
{
    return $this->hasMany('App\Week');
}

Then

$product = Product::find(1);

$diaries = Diary::whereHas('productables.product', function ($query) use ($product) {
    $query->where('id', $product->id);
})->orWhereHas('weeks.productables.product', function ($query) use ($product) {
    $query->where('id', $product->id);
})->get();

@michaloravec ok so

$diaries = Diary::whereHas('productables.productable', function ($query) use ($product) {
            $query->where('id', $product->id);
        })->get();

Since Productables model has productable method and its a morphto it wants me to use a wherehasmorph but then we run in into the problem of the nested relationships again

I think in this case you don't have to use whereHasMorph

@michaloravec we already setup the morphto on the productable model though so using it like above is forcing us to do it that way.

From docs: To query the existence of MorphTo relationships, you may use the whereHasMorph method

But now you work with morphMany so in that case you could work with whereHas

https://laravel.com/docs/7.x/eloquent-relationships#querying-polymorphic-relationships

Just sent you an email @michaloravec

$product = Product::find(88);
        $diaries = Diary::whereHas('productables', function ($query) use ($product) {
            $query->where('product_id', $product->id);
        })->get();
        dd($diaries);

Product 88 is attached to App\Diary and it is also attached to a Week (which belongs to a different diary) https://share.getcloudapp.com/lluYYEPp

The result is pulling the right Diary but since this product is attached to the Week as well need to figure out a way to grab the diary that corresponds to those weeks.

In Week model you also have to have this

public function productables()
{
    return $this->morphMany('App\Productables', 'productable');
}

Product is attached to the week, but week belongs to diary so this should work

$product = Product::find(88);

$diaries = Diary::whereHas('productables', function ($query) use ($product) {
    $query->where('product_id', $product->id);
})->orWhereHas('weeks.productables', function ($query) use ($product) {
    $query->where('product_id', $product->id);
})->get();

@michaloravec thanks that worked perfectly.