CODEWITHSUNDEEP
laravel-4eloquent
user2522307
user2522307

Reputation: 11

Eloquent Subquery

I have a many to many relationship between a student table and a apparatus table (A student performs on many apparatuses and an apparatus has many students). I have a student_results table that has a composite primary key (student_id and apparatus_id) and a 3rd field called results).

I have written a query to find all the apparatuses that a student DOES NOT have a result. The example I give is for student with id = 121. The sub-query is.

SELECT apparatus_id, strapparatus_name FROM apparatuss 
WHERE apparatus_id <> ALL(SELECT apparatus_id FROM student_results 
WHERE student_id =' . 121 . ')';

I would like to write this using Eloquent (Laravel 4.1).

Any help greatly appreciated.

Upvotes: 0

Views: 3934

Answers (1)

Antonio Carlos Ribeiro
Antonio Carlos Ribeiro

Reputation: 87719

Assuming that you already have your Eloquent models Apparatus and StudentResult set, this is a way:

Apparatus::whereNotIn(
    'apparatus_id',
    StudentResult::where('student_id', 121)->lists('apparatus_id')
)->get('apparatus_id', 'strapparatus_name');

Or, if you don't have a model for your student_results table, you can just:

Apparatus::whereNotIn(
    'apparatus_id',
    DB::table('student_results')->where('student_id', 121)->lists('apparatus_id'); 
)->get(array('apparatus_id', 'strapparatus_name'));

Upvotes: 2

Related Questions