Welcome Guest, Not a member yet? Register   Sign In
Parenthesis in ActiveRecord queries ?
#1

[eluser]oll[/eluser]
Hello,

I have a videos table with fields like 'title', 'owner', 'description'.
I have a little search engine that does a simple research ($search string) amongst these fields.

Code:
$fields = array('title','owner','description');
foreach ($fields as $field) {
  $this->db->or_like($field,$search);
}

It works.

But now, I need to secure my site. Each video belongs to one group (the table contains a 'group' field). An authenticated user belongs to several group ($groups array). And I want the search engine to display only the videos if he belongs to the group.
So, I simply added a IN statement :

Code:
$fields = array('title','owner','description');
foreach ($fields as $field) {
  $this->db->or_like($field,$search);
}
$this->db->where_in('group',$groups);

A query looks like :

SELECT * FROM (`videos`) WHERE `groupe` IN ('public','linux','admin') AND `title` LIKE '%bird%' OR `owner` LIKE '%bird%' OR `description` LIKE '%bird%'

But since AND has higher precedence than OR, it's exactly the same as :

SELECT * FROM videos WHERE ( groupe IN (public,linux,admin) AND title LIKE bird ) OR owner LIKE bird OR description LIKE bird

And I want of course :

SELECT * FROM videos WHERE groupe IN (public,linux,admin) AND ( title LIKE bird OR owner LIKE bird OR description LIKE bird )


Is there an elegant way to have this result using CI active record queries ? I just need to add a couple of parenthesis.
Or, at least, is there a way I can get the generated query, instead of running it ? For instance, something like :
Code:
$part1 = $this->db->display_or_like($field,$search);
$part2 = $this->db->display_where_in('group',$groups);

So that I can do something like :
Code:
$query ="(".$part1.")".$part2;

Thank you.
#2

[eluser]brookerrj[/eluser]
I would like to perform the the same kind of query in active record:

(A AND B) OR (C AND D)

Is there an elegant way?
#3

[eluser]m4rw3r[/eluser]
I've made a library for this, called IgnitedQuery.
It can handle parentheses and also subqueries, and is also somewhat backwards compatible with CI's AR (dunno how much, but a lot).

Example:
Code:
// this is all PHP 4 (chaining can be used with PHP 5, use end() to end a subquery/parenthesis)
$q = new IgnitedQuery();

$q->select('whatever');

$sub =& $q->where(); // this starts a subquery
$sub->where('b', 4);
$sub->where('c', 4);

$sub2 =& $q->or_where(); // another with an OR prepended
$sub2->where('b <', 1);
$sub2->where('c >', 5);

$res = $q->get('entries');

(It is a part of IgnitedRecord, but also possible to use as a stand alone lib)
#4

[eluser]oll[/eluser]
This is exactly what I needed.
Thanks !
#5

[eluser]mikedfunk[/eluser]
I just found that when you need to put a portion of your where clause in parentheses, you can jump out of active record JUST for that portion and continue on with your other WHEREs and LIKEs after that.

Code:
// set prefixes
$pgs = $this->db->dbprefix('pages');
$uls = $this->db->dbprefix('user_levels');

$where = "(" . $pgs . ".title LIKE '%".$filter."%'
OR ". $pgs . ".url LIKE '%".$filter."%'
OR ". $uls . ".title LIKE '%".$filter."%')";
$this->db->where($where);

// continue on...
$this->db->where('pages.id !=', 0);

// etc. etc.

Hopefully that's useful to someone.
#6

[eluser]atlanta[/eluser]
[quote author="mikedfunk" date="1279077168"]I just found that when you need to put a portion of your where clause in parentheses, you can jump out of active record JUST for that portion and continue on with your other WHEREs and LIKEs after that.

Code:
// set prefixes
$pgs = $this->db->dbprefix('pages');
$uls = $this->db->dbprefix('user_levels');

$where = "(" . $pgs . ".title LIKE '%".$filter."%'
OR ". $pgs . ".url LIKE '%".$filter."%'
OR ". $uls . ".title LIKE '%".$filter."%')";
$this->db->where($where);

// continue on...
$this->db->where('pages.id !=', 0);

// etc. etc.

Hopefully that's useful to someone.[/quote]

Thanks my man that was very useful for me I was about to pull my hair out. much appreciated.




Theme © iAndrew 2016 - Forum software by © MyBB