Laravel where in query

How would I do this with Laravel?

SELECT movie.title
FROM movie
WHERE movie.id IN
(SELECT session.movie_id FROM session WHERE session.id = :id and date = :date)




Answer:




First off you will need a database with movies and sessions tables and two models in Laravel.

Movie Model

    <?php namespace App;

    use Illuminate\Database\Eloquent\Model;

    class Movie extends Model {

      protected $fillable = ['title'];

      public function sessions()
      {
          return $this->hasMany('App\Session');
      }
      }
    ?>

Session Model

    <?php namespace App;

    use Illuminate\Database\Eloquent\Model;

    class Session extends Model {

      public function movies()
      {
          return $this->belongsTo('App\Movie','movie_id');
      }
    ?>

The query in your Controller would have a method like.

public function show($id)
{
    try {
        $movie = Movie::with('sessions')->where('id',$id)->firstOrFail();
    }
    catch(ModelNotFoundException $exception)
    {
        abort(404);
    }
    return view('movies.show')->withMovie($movie);
}




You can try:

$movie = Movie::whereIn('id', function($query) use ($id, $date) {
    $query->select(DB::raw('session.movie_id'))
          ->from('session')
          ->whereRaw('session.id = ? and session.date = ? array($id, $date));

})
->get();

advanced-wheres