Inserting a Subquery in a Join using Query Builder

Hello, i need to do this query where i get the courses that a specific student doesn't have:

Code:

SELECT students_courses.*, courses.*
FROM courses
LEFT JOIN (
    SELECT student_id, course_id
    FROM students_courses
    WHERE student_id = 296
) students_courses ON courses.id = students_courses.course_id
WHERE students_courses.student_id IS NULL

I'm trying to do it in query builder but I can't get it to work. Does anyone know to do it or maybe in another way?

PHP Code:

$builder = $this->model->db->table('students_courses');
        
$subQuery = $builder->select('student_id, course_id')
                  ->where('student_id', $studentId)
                  ->getCompiledSelect();

$query = $this->model->select('students_courses.*, courses.*')
                    ->join('(' . $subQuery . ')' . 'students_courses', 'courses.id = students_courses.course_id', 'left')
                    ->where('students_courses.student_id', null)
                    ->get(); 


I'm not a guru but recently I have faced a similar problem

Are you using the development mode and checking the query that is is produced by your code?

do you have errors?

how did you instantiate the model class? I mean, it looks to me that you have "too many" arrows in

Code:

$builder = $this->model->db->table

Hope the following method could help or being of inspiration

note that $this-> in the following method, implicitly refers to the 'ticket' table which is the one that has the focus in this Model, in fact at top the model is

PHP Code:

class TicketObjModel extends Model
{
    protected $DBGroup          = 'default';
    protected $table            = 'ticket';
    protected $primaryKey      = 'id';
    protected $useAutoIncrement = true;
    protected $insertID        = 0;
    protected $returnType      = 'object'; 


this is the method later in the same model ( see also https://forum.codeigniter.com/showthread.php?tid=87747 )

PHP Code:

    public function GetTicketList()
    {

        $data = [
            'fetched' =>  $this
            ->select('ticket.date, ticket.subject, ticket.id, ticket.ticket_id, ticket.dept_id, ticket.name, 
                      ticket.lastActivity, ticket.note, 
                      dept.name as dept, post.user_id as utente, post.date as datamess, 
                      post.message as messaggio')
            ->join('dept', 'dept.id = ticket.dept_id')     // this is needed to get the dept name
            ->join('post', 'post.ticket_id = ticket.id ')  // this loads all the posts for the given ticket
            ->where('post.date = (
                SELECT MAX(post.date) FROM post            
                WHERE post.ticket_id = ticket.id
            )')                                            // this subquery was needed to avoid loading all the 
                                                           // posts and instead load only the newest one
            ->orderBy('ticket.lastActivity', 'DESC')->paginate(5,'group1'),
            'pager' => $this->pager,
        ];

        return $data;
    } 


I'm not a guru but recently I have faced a similar problem

Are you using the development mode and checking the query that is is produced by your code?

do you have errors?

how did you instantiate the model class? I mean, it looks to me that you have "too many" arrows in

Code:

$builder = $this->model->db->table

Hope the following method could help or being of inspiration

note that $this-> in the following method, implicitly refers to the 'ticket' table which is the one that has the focus in this Model, in fact at top the model is

PHP Code:

class TicketObjModel extends Model
{
    protected $DBGroup          = 'default';
    protected $table            = 'ticket';
    protected $primaryKey      = 'id';
    protected $useAutoIncrement = true;
    protected $insertID        = 0;
    protected $returnType      = 'object'; 


this is the method later in the same model ( see also https://forum.codeigniter.com/showthread.php?tid=87747 )

PHP Code:

    public function GetTicketList()
    {

        $data = [
            'fetched' =>  $this
            ->select('ticket.date, ticket.subject, ticket.id, ticket.ticket_id, ticket.dept_id, ticket.name, 
                      ticket.lastActivity, ticket.note, 
                      dept.name as dept, post.user_id as utente, post.date as datamess, 
                      post.message as messaggio')
            ->join('dept', 'dept.id = ticket.dept_id')     // this is needed to get the dept name
            ->join('post', 'post.ticket_id = ticket.id ')  // this loads all the posts for the given ticket
            ->where('post.date = (
                SELECT MAX(post.date) FROM post            
                WHERE post.ticket_id = ticket.id
            )')                                            // this subquery was needed to avoid loading all the 
                                                           // posts and instead load only the newest one
            ->orderBy('ticket.lastActivity', 'DESC')->paginate(5,'group1'),
            'pager' => $this->pager,
        ];

        return $data;
    } 


Thank you for your reply. But I already got it to work yesterday by doing this:

PHP Code:

        $coursesModel = model('App\Models\Courses');

        $subQuery = $coursesModel->db->table('students_courses')
                                ->select('student_id, course_id')
                                ->where('student_id', $studentId)
                                ->getCompiledSelect();

        $query = $coursesModel->select('courses.*')
                        ->join('(' . $subQuery . ') students_courses', 'courses.id = students_courses.course_id', 'left')
                        ->where('students_courses.student_id', null);