+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: Query problem in Active Record Class (/showthread.php?tid=43986)
Query problem in Active Record Class - El Forum - 07-29-2011
[eluser]Unknown[/eluser]
Hi,
How can I make separation in query parts???
I like to have this :
SELECT * FROM (`boxes`) WHERE `color` IN ('1')
AND ( `name` LIKE '%white%' OR `short` LIKE '%white%')
With Active Record Class I can't separate the likes from the where parameter, I could do this with Igniter:
SELECT * FROM (`boxes`) WHERE `color` IN ('1') AND `name` LIKE '%white%' OR `short` LIKE '%white%'
Is there any solution for this?
Thanks,
Chris
Query problem in Active Record Class - El Forum - 07-29-2011
[eluser]danmontgomery[/eluser]
Code:
$this->db->where_in('color', array(1))->where('(`name` LIKE "%white%" OR `short` LIKE "%white%")', NULL, FALSE)->get('boxes');Query problem in Active Record Class - El Forum - 08-05-2011
[eluser]jvicab[/eluser]
if you like to have this solved without building the query yourself (like me), add this function in a system\database\DB_DB_active_rec.php:
function like_brackets()
{
// add a opening bracket
reset($this->ar_like);
$key = key($this->ar_like);
$this->ar_like[$key] = '(' . $this->ar_like[$key];
// add a closing bracket
end($this->ar_like);
$key = key($this->ar_like);
$this->ar_like[$key] .= ')';
// update the AR cache clauses as well
if ($this->ar_caching === TRUE)
$this->ar_cache_like[$key] = $this->ar_like[$key];
return $this;
}
and call it inside your model after the last like (or or_like) statement. Here is an example:
$this->db->from('tbl_work_listings,tbl_work_features');
$this->db->where(array('tbl_work_listings.PropertyID' => 'tbl_work_features.PropertyID'), null, false);
$this->db->like('FeatureDescription', 'ocean');
$this->db->or_like('FeatureDescription', 'waterfront');
$this->db->like_brackets();
It will create a query like this:
SELECT * FROM (`tbl_work_listings`, `tbl_work_features`)
WHERE tbl_work_listings.PropertyID = tbl_work_features.PropertyID
AND ( `FeatureDescription` LIKE '%ocean%' OR `FeatureDescription` LIKE '%waterfront%')
note: I have the same implemented by where.
Query problem in Active Record Class - El Forum - 08-05-2011
[eluser]Aken[/eluser]
No one says you have to use active record, either. You can just write your queries as you prefer and use $this->db->query();