Eloquent relationships that deal with 3 tables

I have made all the one to many relationships in 3 models ‘user’, ‘auctionschedule’ and ‘auctionregistrations’ for “hasMany” and “belongsTo”. Here is what they look like https://imgur.com/u7aexVt

This allows for some very powerful (easy to create) queries. For example, this query is great:

It gives me a count of all the auctions the user has scheduled that are after Now()

$count = auth()->user()->auctionschedule()->where('date', '>=', Now())->count();

But how do I get a count of all the auctions the user is registered for that are scheduled for after Now(). Note that the auction date is in the auctionschedule table. So with this query, all 3 tables are involved. I’ve tried everything I can think of such as this but nothing seems to work-example:

$count = auth()->user()->auctionregistrations()->where('auctionschedule.date', '>', Now())->count();

bobbybouwmann

5 years ago

Best Answer

Level 88

You can solve this using a join. Eloquent really can't help you here, because it's not a natural relationship.

Documentation: https://laravel.com/docs/7.x/queries#joins

1 like

scottsuhy

5 years ago

Level 1

Got it. So essentially this:

SELECT auctionschedule.scheduled_for, auctionschedule.auctionname, auctionregistrations.auction_id, auctionregistrations.auction_user_id
            FROM auctionregistrations
            left JOIN auctionschedule ON auctionschedule.id = auctionregistrations.auction_id
            left JOIN users ON users.id = auctionregistrations.auction_user_id
            where auctionschedule.scheduled_for > now()
            order by auctionschedule.scheduled_for

        $myauctionregistrationscount = Auctionregistration::select('id')
            ->leftJoin('auctionschedule', function($join){
                $join->on('auctionschedule.id', '=', 'auctionregistrations.auction_id');
            })
            ->leftJoin('users', function($join){
                $join->on('users.id', '=', 'auctionregistrations.auction_user_id');
            })
            ->where('auctionschedule.scheduled_for', '>=', Now())
            ->where('auctionregistrations.auction_user_id', '=', auth()->user()->id)
            ->count();

scottsuhy

5 years ago

Level 1

actually, i guess its this because I have the ID of the user.

 $myauctionregistrationscount = Auctionregistration::select('id')
            ->leftJoin('auctionschedule', function($join){
                $join->on('auctionschedule.id', '=', 'auctionregistrations.auction_id');
            })
            ->where('auctionschedule.scheduled_for', '>=', Now())
            ->where('auctionregistrations.auction_user_id', '=', auth()->user()->id)
            ->count();

Please or to participate in this conversation.