CODEWITHSUNDEEP
phpmysqllaravellaravel-5
Murlidhar Fichadia
Murlidhar Fichadia

Reputation: 2609

Using Query Scope but with different query model - Laravel

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

Relationship Diagram

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.

Upvotes: 0

Views: 427

Answers (1)

Rubens Mariuzzo
Rubens Mariuzzo

Reputation: 29241

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.

Upvotes: 0

Related Questions