CODEWITHSUNDEEP
codeigniteractiverecordcodeigniter-3codeigniter-2
Shobhit Gupta
Shobhit Gupta

Reputation: 690

Codeigniter Active record And Or combinations in where clause with multiple & nested groups

I have a complex SQL query and I want to implement it through Active Records. This query has several AND / OR clauses grouped together with another criteria. I have gone through various articles where they said that we can use group_start() and group_end() but I am wondering if a group can be started inside another group too? The resulting serial numbers needs to be excluded from the result set to be produced by the outer query. Actually I tried using Join here, but it didn't work. Any working idea regarding joins here will be appreciable too.

As you can see in the query below, I have used double round brackets to represent multiple groups inside a group. The resulting serial numbers needs to be excluded from the results of outer query too. Please tell me what will be its Codeigniter Active Record equivalent code.

select * from table2 WHERE NOT table2.serial IN (select columnname from table where ((col < val and val < col) or (col < val and val < col) or(val=col and val=col)) AND incol=intval AND intcol=intval)

Here, col is the column name, val is a value of DATE type, intval is an Integer value

Upvotes: 0

Views: 384

Answers (1)

Harshwardhan Sharma
Harshwardhan Sharma

Reputation: 261

Try this syntax

$this->db->select("*")->from("table");
    $this->db->group_start();
        $this->db->group_start();
            $this->db->where("val <",'col');
            $this->db->where("val <",'col');
        $this->db->group_end();
        $this->db->or_group_start();
            $this->db->or_where("val <",'col');
            $this->db->where("val <",'col');
        $this->db->group_end();
        $this->db->or_group_start();
            $this->db->or_where("val ",'col');
            $this->db->where("val ",'col');
        $this->db->group_end();
    $this->db->group_end();
    $this->db->where("incol ",'intval');
    $this->db->where("incol ",'intval');
    $this->db->get();
    $last_query = $this->db->last_query();

    $this->db->select('*')->from('table2');
    $this->db->where_not_in('serial',$last_query);
    $this->db->get();
    echo $this->db->last_query();

The query string produced by the above is

SELECT * FROM `table2` WHERE
     `serial` NOT IN(
    SELECT columnname FROM `table` WHERE
    (
        (`val` < 'col' AND `val` < 'col') OR
        (`val` < 'col' AND `val` < 'col') OR
        (`val` = 'col' AND `val` = 'col') 
    ) AND `incol` = 'intval' AND `incol` = 'intval'
);

Upvotes: 2

Related Questions