How can I simplify my query?

[eluser]cicovy[/eluser]
Hi, I have this query which I want to filter the results for a certain $point_id and other 3 search parameters which I want to be grouped with (). If the $point_id is different from '1', I want to display only records for that $point_id and if the $point_id is equal to '1', I want to display all records.

Code:

function search($point_id,$search)
    {
        $this->db->from('items');
        if ($point_id <> '1') {
            $where = "point_id = '".$point_id."' AND (name LIKE '%".$search."%' OR category LIKE '%".$search."%' OR nr_crt LIKE '%".$search."%')";
            $this->db->where($where);
        } else {
            $where = "WHERE name LIKE '%".$search."%' OR category LIKE '%".$search."%' OR nr_crt LIKE '%".$search."%'";
            $this->db->where($where);
        }
        
        $this->db->order_by("name", "asc");    
        return $this->db->get();
    }

How can I group the

Code:

$where = "point_id = '".$point_id."' AND (name LIKE '%".$search."%' OR category LIKE '%".$search."%' OR nr_crt LIKE '%".$search."%')";

into something like $this->db->... ? So the function will look something like

Code:

function search($point_id,$search)
    {
        $this->db->from('items');
        $this->db->like('name',$search);
        ..........
        ..........
        if ($point_id <> '1') {
            $this->db->where('point_id',$point_id);
        }        
        $this->db->order_by("name", "asc");    
        return $this->db->get();
    }

Thanks!

[eluser]cicovy[/eluser]
As a note, right now, it produces the results I want, but I'm feeling like I'm not using CI's power to achieve this.

Thanks again!

[eluser]Nick_MyShuitings[/eluser]
I did some quick tests, and without some hacking/extending to the Active Record class I don't think you can generate that where clause using the existing functionality.

Specifically the parenthesis around the AND (SEVERAL OR CLAUSES).

Now that you've got me thinking, it would be interesting to be able to set the something like this in order to combine.

Code:

$this->db->where('point_id',$point_id);
$where[] = $this->db->like('name',$search);
$where[] = $this->db->or_like('category', $search);
$where[] = $this->db->or_like('nr_crt', $search);
$this->db->where($where);

Which would produce the CLAUSE AND (NESTED CLAUSES) structure that you have. I'll have to look into that class... would have to be a core hack, which I'm not the biggest fan of, but it would make for some cleaner query construction.

On an aside, if this search is an important feature for you, you should look into FullText Index of MySQL (if you're using MySQL) which will be significantly faster then LIKE statements... especially double wildcard-ed LIKE statements http://dev.mysql.com/doc/refman/4.1/en/f...earch.html

[eluser]cicovy[/eluser]
Thanks for the tip! This search is important so I will think at implementing your suggestion with the FullText Index.

Cheers!

[eluser]smilie[/eluser]

Code:

$this->db->from('items');
        if ($point_id <> '1') {
            $this->db->where('point_id',$point_id);
            $this->db->like('name',$search);
            $this->db->or_like('category',$search);
            $this->db->or_like('nr_crt',$search);
        } else {
            $this->db->like('name',$search);
            $this->db->or_like('category',$search);
            $this->db->or_like('nr_crt',$search);
        }

I thinks something like this should work.
Do a $this->db->last_query(); (or profiling turned on) to see what query you get...

Good luck,
Smilie

[eluser]cicovy[/eluser]
Hi smile, thanks for the suggestion, but it doesn't produce what I need... I hope CI will implement a way to group OR db statements.

Thanks a lot Nick & smile!