Using Query Scope but with different query model - Laravel

Question

User can Register for Multiple Tournaments and Each Tournament has one Score record for each user.

I have kept user_id in Score table to keep the records unique.

I have this Query :

$tournaments = DB::table('tournaments')
        ->join('scores','tournaments.id','=','scores.tournament_id')
        ->select('tournaments.*','scores.score','scores.tees')
        ->where('t_date','<',Carbon::today())
        ->where('scores.user_id',$request->user()->id)
        ->get();

I wanted to avoid joins and also use Query Scope for re-use of where clause '(t_date < Carbon::today())'

So, this is the query I have come up with:

//This is in Tournament Model
public function scopeUpcoming($query)
{
        return $query->where('t_date','>',Carbon::today());
}

$query = Score::with('tournaments')
         ->upcoming()
         ->where('user_id',$request->user()->id)
         ->get();

But scopeUpcoming() uses $query and there is no 't_date' in Score table, so I need to somehow access tournaments table and query on it. vice-versa I can't go with Tournament::with('scores') as there is no 'user_id' in Tournament's table so I cant get for a specific user.

I have lots of Joins in my controllers and I find joins and raw queries are kind of hacks and I am making least use of Eloquent models. I want to learn MVC framework the way its designed to use. I am trying to avoid single/multiple joins and want to use eloquent relationships more effectively. — Murlidhar Fichadia
– Murlidhar Fichadia, Commented Jul 23, 2016 at 14:16

Rubens Mariuzzo · Accepted Answer · 2016-07-23 14:32:31Z

0

You said that you can't go with

Tournament::with('scores') there is no user_id in Tournament's table so I cant get for a specific user.

Actually, you can still use with method and you can filter the items using a closure:

Tournament::with(['scores' => function($query) use ($user_id) {
    $query->where('user_id', $user_id);
});

Also you said:

I need to somehow access tournaments table and query on it

You can use the same bit of code to modify your current query chain

$query = Score::with(['tournaments' => function($query) {
             $query->with('t_date, /**/); // Do your stuff here.
         ])
         ->upcoming()
         ->where('user_id',$request->user()->id)
         ->get();

Hope this light up your case.

edited Jul 23, 2016 at 14:32

answered Jul 23, 2016 at 14:23

Rubens Mariuzzo

29.4k28 gold badges124 silver badges149 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Murlidhar Fichadia Over a year ago

I am really confused now. What goes as query Scope and what goes in controller? Where do I add upcomingScope()? Can you please explain or show what bit goes where? I tried so many alternatives

Murlidhar Fichadia Over a year ago

This is what I got : [{"id":1,"tournament_id":2,"user_id":2,"score":45,"tees":"championship","tournaments":null}] tournaments result seems to be empty whereas there is tournament_id : 2, it says null? whereas its not. $query = Score::with(['tournaments' => function($query) use ($user_id) { $query->where('t_date','<',Carbon::today()); } ]) ->where('user_id',$request->user()->id) ->get();

Rubens Mariuzzo Over a year ago

Wait, wait, the “scope function” is something you put on your models. In the controller you can put all your logic (in your case the query).

Collectives™ on Stack Overflow

Using Query Scope but with different query model - Laravel

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related