Welcome Guest, Not a member yet? Register   Sign In
Active Record: how do I combine two or_like with an AND between them?
#1

[eluser]sparbier[/eluser]
Hi all,
I need to run a query that takes inputs from two arrays (from post data, don't know beforehand how many elements are given), and looks for the elements of these arrays in two separate fields in my database. So this is what I did:
Code:
if (isset($_POST['region'])) {
        foreach ($_POST['region'] as $r)  {
            // put the searched id between commas before searching for it in the string, in order not to e.g. find a 4 contained in 42 but only ,4,
            $searchstring = ','. $r .',';
            $this->db->or_like('regions', $searchstring);
        }
    }
    if (isset($_POST['instrument'])) {  
        foreach ($_POST['instrument'] as $i)  {
            $searchstring = ','. $i .',';
            $this->db->or_like('instruments', $searchstring);
        }
    }
The issue now is, that while I do want the LIKE statements within each of the two criteria be linked with OR, the two criteria must be linked with AND.

Is there any way to achieve this with Active Records?
#2

[eluser]m4rw3r[/eluser]
You mean that your SQL should look something like this?
Code:
WHERE (regions LIKE '%a%' OR regions LIKE '%b%') AND (instruments LIKE '%c%' OR instruments LIKE '%d%')
I'm afraid that it is not possible to do that with CI's AR.

But I have developed a SQL-builder which is capable to do this: IgnitedQuery.
It is an improved version of CI's AR (a rewrite) which also is capable of handling subqueries and nested wheres (which you want in this case).
I don't have much information available because I need to rewrite IgnitedRecord's manual (which includes IgnitedQuery's, because IgnitedRecord uses IgntedQuery to generate SQL).

Your example would look like this with IQ:
Code:
// $query can be treated just like CI's AR
// (but the escaping problems of 1.7 aren't in IQ, although it may escape somewhat different)
$query = new IgniedQuery();

if (isset($_POST['region'])) {
    // create an instance for the nested wheres
    $q = new IgnitedQuery();

    foreach ($_POST['region'] as $r)  {
        $searchstring = ','. $r .',';
        $q->or_like('regions', $searchstring);
    }

    // assign it to the WHERE part of the query
    $query->where($q);
}
if (isset($_POST['instrument'])){
    $q2 = new IgnitedQuery();

    foreach ($_POST['instrument'] as $i)  {
        $searchstring = ','. $i .',';
        $q2->or_like('instruments', $searchstring);
    }

    $query->where($q2);
}

$result = $query->get();
IgnitedQuery lib file in IgnitedRecord trac (download link on the bottom)
#3

[eluser]sparbier[/eluser]
thanks m4 for your help! seems like your lib is a really worthwile extension to CI, maybe they'll take that as an example to integrate something likewise in coming CI versions :-)

Meanwhile, as I had to deliver this weekend, I opted to builde the query string directly and then use query() rather than the active records stuff. May get me into trouble if I ever change to a db (currently I'm on mysql) with different syntax, but currently it works fine.

thx again!
#4

[eluser]m4rw3r[/eluser]
Of course you can do it yourself (I have nothing against hardcoded SQL), but just remember to escape properly Wink




Theme © iAndrew 2016 - Forum software by © MyBB