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