Eloquent hasMany with foreign key on joint table

Question

Assume this:

class List extends Model
{
    public function items(){
        return $this->hasMany(Items::class, 'c.class_id', 'class_id')
            ->rightjoin('items_classes as c', 'c.items_id', '=', 'items.id');
    }
}

The problem is that Eloquent prepends items to foreign key field and the final query is:

SELECT * FROM items
RIGHT JOIN items_classes as c ON c.items_id = items.id
// here it is
WHERE items.c.class_id = 10

Even using DB::raw('c.class_id') didn't solve the problem.

What is your query building code looking like? How do you define your query? Also, is it good practice to also do a right join in a relationship method? — Fjarlaegur, Feb 20 '19 at 09:03
I agree with @Fjarlaegur. It isn't a good practice to do a right join on relationship methods. Instead, make a new method, reuse this method there and attach a right join there. Also, where is the `where` condition written that you showed? — nice_dev, Feb 20 '19 at 09:07
@Fjarlaegur I really have no idea what do you mean by "What is your query building code looking like?". The query is generated by this relationship function. — Omid, Feb 20 '19 at 09:19
@vivek_23 Would you give me a link to any documentation that purposes such a solution? I need an example. — Omid, Feb 20 '19 at 09:20
@Omid For methods that define relationships, there shouldn't be any additional stuff attached to it. This would make it execute every time whenever `items()` is called even when sometimes it wouldn't be needed.This would make it tightly coupled. Instead make a new method and attach this method to it with the right join. Also, if the query is generated by this method itself, then there is some `boot()` method executing, attaching this `where` condition every time it's executed. Can you show us that method? — nice_dev, Feb 20 '19 at 09:32
@vivek_23 I have posted all the things I want to do. There is no additional method to show you. Also I'm not going to add any extra where clause to the relationship query. This `where` condition is the default behavior of Eloquent builder and wanted to change it. However, I found out this is not going to be solved and the Laravel core needs some changes. https://github.com/laravel/ideas/issues/1528 — Omid, Feb 20 '19 at 09:42
@Omid `This where condition is the default behavior of Eloquent builder` That is never the case. Laravel only defines relationships and enables `joins` accordingly. Have you checked if there is any `boot()` method in any of your models' definitions? See [here](https://laravel.com/docs/5.7/eloquent#global-scopes) for more details. — nice_dev, Feb 20 '19 at 12:08

score 1 · Answer 1 · answered Feb 20 '19 at 11:30

If you notice the signature of hasMany relation method :

return $this->hasMany(Model::class, 'foreign_key', 'local_key');

Which means when Laravel will make the query, it will consider second argument foreign_key as a column of table defined in Model::class.

To simplify in your case :

return $this->hasMany(Items::class, 'c.class_id', 'class_id')->...

Leaving the rightjoin aside for a moment, Laravel is considering c.class_id as a foreign key of Item::class table which is indeed items table.

So the resultant query is :

SELECT * FROM items WHERE items.c.class_id = 10

Then when you add the right join, laravel just adds into the main query and makes it :

SELECT * FROM items
RIGHT JOIN items_classes as c ON c.items_id = items.id
WHERE items.c.class_id = 10

Laravel will not refer items_classes in the relation because you are relating List Model to Item::class and not ItemClass::class.

I am not sure about the data you need but see if you can use with like below :

class List extends Model
{
    public function items(){
        return $this->hasMany(Items::class, 'c.class_id', 'class_id');
    }

}


List::with(['items', function($q){

    return $q->->rightjoin('items_classes as c', 'c.items_id', '=', 'items.id');
}])->get();

Hope this gives you an idea how you can update your relationships to get desired query. If you add your table structure and data you want, I can update the answer with relationships for you.

Thanks for spending time on my issue. However, I found this is not going to solve anyway but with overriding hasMany method. https://github.com/laravel/ideas/issues/1528 — Omid, Feb 25 '19 at 07:29

Eloquent hasMany with foreign key on joint table

1 Answers1