Elliot Sabitov Elliot Sabitov - 8 months ago 26
SQL Question

Condition on the relational model Laravel 5

Hello and thanks in advance for your time and help. I have 3 very simple tables. A user table with a user_id, a games table with a game_id as well as some other fields (scheduled date/time) and a GamesAttendee table that just has user_id and game_id field. I am trying to select all games that user is connected to and only return ones that are scheduled for the future/past.

What I ended up going with is:

$cur = GamesAttendee::where('user_id',$user_id)->pluck('game_id')->all();
$cur = Game::whereIn('id', $cur)->where('scheduled','>=',$now)->get();

But I feel like there has to be a more efficient way of doing this. I have looked around and tried various things like eager loading and just messing with my models and nothing seems to work. I feel like this a very simple and essential case that is extremely common and I am wondering how this is actually supposed to be done in laravel.

I have tried:

$cur = Game::with(['attendees'=>function($q) use ($user_id){
return $q->where('user_id',$user_id);

But that was not what I wanted. I am basically trying to do:

SELECT * FROM GameAttendees
JOIN `games` on games.id = GameAttendees.game_id
WHERE GameAttendees.user_id = 'x' AND games.scheduled >= '2016/05/01' ;

I quickly jotted that mysql code so just ignore any mistakes. Any ideas?

Thank you.


Resolved by adding the following into my user model:

public function future_games()
$now = gmdate('Y-m-d H:i:s',strtotime('+4 hours'));
return $this->belongsToMany('App\Game','games_attendees')->where('scheduled','>=',$now);

then in my controller I was able to do:

$future_games = User::with('future_games')->get();


First define many-to-many relation in your Game and User models:

class Game extends Model {
  public function users() {
    return $this->belongsToMany(User::class, 'GameAttendees');

class User extends Model {
  public function games() {
    return $this->belongsToMany(Game::class, 'GameAttendees');

With that in place you should be able to get all games given user is attending with:

$games = $user->games;

If you want to add some additional conditions, do the following:

$futureGames = $user->games()->where('scheduled','>=',$now)->get();

Or just create another relation in your User model:

class User extends Model {
  public function futureGames() {
    return $this->belongsToMany(Game::class, 'GameAttendees')->where('scheduled','>=',$now);

and access them by:

$futureGames = $user->futureGames;