SQL query problem with Active Record

[eluser]codeworxx[/eluser]
Hey Guys,

i have a big Problem with CodeIgniters Active Record Class - i'm missing the and_where_in function!

I have the following code:

Code:

$like = array(
            'firstname' => $search,
            'surname' => $search,
            'postcode' => $search,
            'city' => $search,
            'firm' => $search,
            'streetaddress' => $search,
            'streetaddress2' => $search
        );      
        $this->db->or_like($like);        
        if($distance != 0) {                  
            $zips = array('1234','34345','44343');
            if($zips != false) {
                $this->db->where_in('postcode',$zips);
            }            
        }          
        $this->db->where('published','1');        
        $this->db->order_by( 'firm' );
        $result = $this->db->get('users');

which produces an sql query like that:

Code:

SELECT * FROM (`twx_users`) WHERE `postcode` IN ('1234', '34345', '44343') AND `published` = '1' AND `firstname` LIKE '%c%' OR `surname` LIKE '%c%' OR `postcode` LIKE '%c%' OR `city` LIKE '%c%' OR `firm` LIKE '%c%' OR `streetaddress` LIKE '%c%' OR `streetaddress2` LIKE '%c%' ORDER BY `firm`

but i need an sql query like that:

Code:

SELECT * FROM (`twx_users`) WHERE `postcode` IN ('1234', '34345', '44343') AND `published` = '1' AND (`firstname` LIKE '%c%' OR `surname` LIKE '%c%' OR `postcode` LIKE '%c%' OR `city` LIKE '%c%' OR `firm` LIKE '%c%' OR `streetaddress` LIKE '%c%' OR `streetaddress2` LIKE '%c%') ORDER BY `firm`

the signs '(' and ')' before 'firstname' LIKE and after 'streetaddress2' LIKE are important!! how to fix that?

any hints?

Thanks so much.

[eluser]danmontgomery[/eluser]
AR doesn't currently support where-clause grouping, you would have to put that in the query manually:

Code:

->where("(`firstname` LIKE '%c' OR `surname` LIKE '%c%' OR `postcode` LIKE '%c%')", NULL, FALSE);

[eluser]Nick_MyShuitings[/eluser]
I'm working on an extension, there was a post the other day requesting the same thing. I am looking at adding support for parenthesis enclosed Where clauses, the EXISTS with a subquery, and subqueries in general. If it works and its not a total hackjob I'll post feedback on monday.

[eluser]codeworxx[/eluser]
@Lab Technican
Thanks, okay - then i will do a manual query.


@Nick_MyShuitings
Please contact me or write in that post - i need that function more often... it would be great...

Thanks