Morphed resource_id column is null on generated sql

[jfoliveira]

Hi, brilliant package! Congrats and thanks for sharing it!

I need help on using it with a morphed relationship. Note on the generated SQL query below that the query is correct but the model instance id is not being used, using null instead.

SQL clause most relevant part is:

where `items`.`resource_id` is null

Replacing is null for my service id and running the query in the database it returns the correct owner, as I expected:

where `items`.`resource_id` = 123

So there is no issue on the query generated by this package, but it seems I'm not using it properly to retrieve so it could set the resource_id based on the current model instance id field value.

My relation definition:

class Service extends Model
{
    // ...
    public function owners()
    {
        return $this->hasManyDeep(
            Owner::class,
            [Item::class, ItemOwner::class],
            [
                ['resource_type', 'resource_id'], // foreign key to this model instance on `Item` model
                'item_id', // foreign key to `Item` model on `ItemOwner` model
                'id' // primary key on Owner model
            ],
            [
                'id_renamed_for_debugging', // id not used in the sql query. Is that correct?
                'id', // Local key on `Item` model
                'owner_id' // foreign key on `ItemOwner` model to `Owner` model
            ]
        );
    }
}

Here is how I'm invoking it:

  $service = Service::find($id);
  dd($service->owners()->get());

And here is the generated SQL query when debugging:

select `owners`.*, `items`.`resource_id` as `laravel_through_key` 
from `owners` 
inner join `item_owners` on 
`item_owners`.`owner_id` = `owners`.`id` 

inner join `items` on 
`items`.`id` = `item_owners`.`item_id` 

where `items`.`resource_id` is null
  and `items`.`resource_type` = 'App\\Service'
  and `owners`.`deleted_at` is null

What am I doing wrong?

[staudenmeir]

Can you share the Service model and the result of dd(Service::find($id)->getAttributes());?

[jfoliveira]

Sure.

Dump of model attributes:

dd(Service::find($id)->getAttributes()); 

output:

array:11 [
  "id" => 10
  "name" => "sample service"
  "description" => "test service"
  "created_at" => "2020-04-16 17:10:46"
  "updated_at" => "2020-04-16 17:10:46"
]

The whole code of Service model file:

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;

class Service extends Model
{
    use \Staudenmeir\EloquentHasManyDeep\HasRelationships;

    protected $table = 'services';

    protected $fillable = [
        'name',
        'description',
    ];

    public function owners()
    {
        return $this->hasManyDeep(
            Owner::class,
            [Item::class, ItemOwner::class],
            [
                ['resource_type', 'resource_id'], // foreign key to this model instance on `Item` model
                'item_id', // foreign key to `Item` model on `ItemOwner` model
                'id' // primary key on Owner model
            ],
            [
                'id_not_used_and_not_considered_in_the_sql_query_why?',
                'id', // Local key on `Item` model
                'owner_id'       // foreign key on `ItemOwner` model
            ]
        );
    }
}

What intrigues me is that it's getting the resource_type correctly in the SQL query, just the model instance resource_id is not being applied.

[staudenmeir]

This works for me, but that's the first relationship you tried, right?

public function owners()
{
    return $this->hasManyDeep(
        Owner::class,
        [Item::class, ItemOwner::class],
        [
            ['resource_type', 'resource_id'],
            'item_id',
            'id',
        ],
        [
            'id',
            'id',
            'owner_id',
        ]
    );
}

[jfoliveira]

You nailed it using id where I was using id_renamed_for_debugging. I was expecting it to raise a column not found exception but it was just ignored. Setting it to id or null it worked.

I thought I have tried that before ... Sorry @staudenmeir and thank you very much for your support!

[staudenmeir]

The first local key doesn't appear in the query, it's used to get the binding value from the parent model ($service->id). Eloquent returns null if an attribute doesn't exist.

BTW, you can simplify the relationship (assuming ItemOwner is a Pivot model):

public function owners()
{
    return $this->hasManyDeep(
        Owner::class,
        [Item::class, ItemOwner::class],
        [['resource_type', 'resource_id']]
    );
}