database query combine possibility

i currently have to following 2 query's:

Code:

SELECT `PersonelID` FROM `vacations` WHERE ( `FromDate` <= '2017-07-10' OR ( `FromDate` BETWEEN '2017-07-10' AND '2017-07-16' ) ) AND ( `ToDate` >= '2017-07-16' OR ( `ToDate` BETWEEN '2017-07-10' AND '2017-07-16' ) )

from which i use the result in the following query (the NOT IN('3') part)

Code:

SELECT `PersonelID`, `FirstName`, `LastName` FROM `personel` WHERE `PersonelID` NOT IN('3') AND `Active` = 1 ORDER BY `FirstName` ASC

but i found that for speed, i can combine these 2 query's as 1 single query like below which works like a charm in phpmyadmin

Code:

SELECT `PersonelID`, `FirstName`, `LastName` FROM `personel` WHERE `PersonelID` NOT IN(SELECT `PersonelID` FROM `vacations` WHERE ( `FromDate` <= '2017-07-01' OR ( `FromDate` BETWEEN '2017-07-01' AND '2017-07-02' ) ) AND ( `ToDate` >= '2017-07-02' OR ( `ToDate` BETWEEN '2017-07-01' AND '2017-07-02' ) )) AND `Active` = 1 ORDER BY `FirstName` ASC

for these 2 seperate query's i have the following code to use the result from the first in the second query:

PHP Code:

     $this->db->select('PersonelID')->from('vacations');
        $this->db->group_start();
        $this->db->where('FromDate <=', $startDate);
        $this->db->or_group_start();
        $this->db->where('FromDate BETWEEN \''.$startDate.'\' AND \''.$endDate.'\'');
        $this->db->group_end();
        $this->db->group_end();
        $this->db->group_start();
        $this->db->where('ToDate >=', $endDate);
        $this->db->or_group_start();
        $this->db->where('ToDate BETWEEN \''.$startDate.'\' AND \''.$endDate.'\'');
        $this->db->group_end();
        $this->db->group_end();
        $vacations = $this->db->get();
        $vacation = $vacations->result_array();
        if (empty($vacation))
        {
            $vac = '';
        }
        else
        {
            foreach($vacation as $vacs)
            {
                $vac[] = $vacs['PersonelID'];
            }
        }
        $this->db->select('PersonelID, FirstName, LastName');
        $this->db->from('personel');
        $this->db->where_not_in('PersonelID', $vac);
        $this->db->where('Active', 1);
        $this->db->order_by('FirstName', 'ASC');
        $personel = $this->db->get(); 


but i can't seem to figure out how to combine these 2 query's in CI using the querybuilder. am i missing something, or is this really impossible using the querybuilder...

Hi,

Since you have already written the sql why not just use it like so:

PHP Code:

$this->db->query('YOUR QUERY HERE'); 


For complex queries it is often easier to write an sql query and use is as above.

In the docs: https://www.codeigniter.com/user_guide/d...ml#queries

Best wishes,

Paul.

PS You can also enable the profiler to view the query that was generated which often shows you where it is going wrong.

PHP Code:

$this->output->enable_profiler(TRUE);