Laravel where in query

Question

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 1

Use DB::raw to run raw queries

$query = DB::select(DB::raw("select movie.title FROM movie where movie.id in (select session.movie_id from session where session.id = $id and date = $date);"));

return $query;

Answer 2

You can make use of laravel's advanced-wheres:

$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();

Answer 3

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);
}

Answer 4

However you use laravel ORM, it will generate more than one query, i would recommend you to just query in session table first and get the list, then use that result in your wherein query in movie model.

Answer 5

$query = "select movie.title from movie where movie.id IN ( SELECT session.movie_id FROM session WHERE session.id ='".$id."' and date = '".$date."' );

$resultSet = DB::select($query);

Hope this helps.

Answer 6

Use Left Join. For example,

$first = DB::session('movie.title')->leftjoin('session','session.movie_id','=','movie.id')->where('session.id', '=', $id)->where('session.date','=','$date')->get();

To check results,

dd(first);

Hope this helps.

Answer 7

$query = "SELECT movie.title
    FROM movie
    WHERE movie.id IN
    (SELECT session.movie_id FROM session WHERE session.id = ? and date = ?)";

$movie = \DB::select($query, [$id, $date]);

Answer 8

Using LEFT JOIN is a good option for optimized code. Otherwise Check the below explanation.

The following line of code will generate the query to fetch the movie id's. Using "toSql()" method will return the query instead of result.

$movie_id_qry = DB::table('session')->select('session.movie_id')->whereRaw('session.id = '. $id .' && date = '. $date)->toSql();

This query can be included to fetch the movie title likewise:

$movie_title = DB::table('movies')->where('movie.id', '=', $movie_id_qry)->pluck('movie.title');

For result : echo $movie_title;

The "pluck()" method will return only the title column if match found. To retrieve the entire row use the "get()" method instead of "pluck()".

As the "toSql()" method will only generate the query, the combined DB fetching only happens in the second line of code.

Hope this is helpful.

Answer 9

DB::table('movies')
  ->select(
    movies.title, 
    DB::raw(
      '(select session.movie_id from session where session.id = id AND date = date') as movie_id'
    )
  )->where('movies.id','movie_id')get();