+- 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: using or_where() on same field (/showthread.php?tid=30655)
Pages:
1
2
using or_where() on same field - El Forum - 05-21-2010
[eluser]blorriman[/eluser]
I am trying to to a where(), or_where() on the same field (groups_members.resign_date). I want to find members who have either not resigned (field is NULL), or who resigned after the start_date.
Code:
$this->db->where('groups_members.join_date <=', $end_date);
$this->db->where('groups_members.resign_date >=', $start_date);
$this->db->or_where('groups_members.resign_date', NULL);
$this->db->where('groups_members.group_id', $this->session->userdata('group_id'));
$this->db->join('groups_members', 'groups_members.member_id = members.user_id');
$this->db->order_by('last_name', 'asc');
$query = $this->db->get('members');It's not working and I can't seem to find an answer - anyone have a suggestion?
using or_where() on same field - El Forum - 05-21-2010
[eluser]WanWizard[/eluser]
After the query, put
Code:
echo $this->db->last_query();The issue is probably that you need to add brackets when mixing AND's and OR's in a WHERE clause. Which Active Record can't do.
using or_where() on same field - El Forum - 05-21-2010
[eluser]blorriman[/eluser]
I'm not sure why, but by putting the or_where() before the where() seems to work.
Code:
$this->db->where('groups_members.group_id', $this->session->userdata('group_id'));
$this->db->where('groups_members.join_date <=', $end_date);
$this->db->or_where('groups_members.resign_date', NULL);
$this->db->where('groups_members.resign_date >=', $start_date);
$this->db->join('groups_members', 'groups_members.member_id = members.user_id');
$this->db->order_by('last_name', 'asc');
$query = $this->db->get('members');using or_where() on same field - El Forum - 05-21-2010
[eluser]WanWizard[/eluser]
It works, but probably not the way you intend, as the query is still assembled without brackets.
So you query becomes something like "WHERE A = 1 AND B = 2 AND C = 3 OR D = 4". Which means that as long as D = 4 the row is selected, all the other fields are ignored.
Probably not what you intended.
using or_where() on same field - El Forum - 05-21-2010
[eluser]blorriman[/eluser]
Well, this is what I'm getting, and it seems to work for members who have not resigned (resign_date IS NULL) as well as for members who resigned after the start_date.
Code:
SELECT * FROM (`members`)
JOIN `groups_members` ON `groups_members`.`member_id` = `members`.`user_id`
WHERE `groups_members`.`group_id` = '81'
AND `groups_members`.`join_date` <= '2010-05-08'
OR `groups_members`.`resign_date` IS NULL
AND `groups_members`.`resign_date` >= '2010-04-28'
ORDER BY `last_name` ascusing or_where() on same field - El Forum - 05-21-2010
[eluser]WanWizard[/eluser]
Exactly.
Which would return all records in which group_members.resign_date IS NULL, regardless of the other values.
using or_where() on same field - El Forum - 05-21-2010
[eluser]blorriman[/eluser]
You're right, so are you saying this can't be done in Active Record Class?
using or_where() on same field - El Forum - 05-21-2010
[eluser]WanWizard[/eluser]
Correct.
Depending on the type of query some tricks work, but you loose automatic escaping and table prefixing, so imho not worth the trouble.
If you dare to modify CI's DB_active_rec.php (I have it extended, but default CI doesn't support that), you can replace the where() and or_where() method by these:
Code:
// --------------------------------------------------------------------
/**
* Where
*
* Our version allows you to add open and close brackets to the where clause
*
* Generates the WHERE portion of the query. Separates
* multiple calls with AND
*
* @access public
* @param mixed
* @param mixed
* @param mixed
* @return object
*/
function where($key, $value = NULL, $escape = TRUE, $bracket = NULL)
{
if ( strtolower($bracket) == 'open' )
{
return $this->_where($key, $value, 'AND (', $escape);
}
elseif ( strtolower($bracket) == 'close' )
{
$result = $this->_where($key, $value, 'AND ', $escape);
end($this->ar_where);
$this->ar_where[key($this->ar_where)] .= ')';
return $result;
}
else
{
return $this->_where($key, $value, 'AND ', $escape);
}
}
// --------------------------------------------------------------------
/**
* OR Where
*
* Our version allows you to add open and close brackets to the where clause
*
* Generates the WHERE portion of the query. Separates
* multiple calls with OR
*
* @access public
* @param mixed
* @param mixed
* @param mixed
* @return object
*/
function or_where($key, $value = NULL, $escape = TRUE, $bracket = NULL)
{
if ( strtolower($bracket) == 'open' )
{
return $this->_where($key, $value, 'OR (', $escape);
}
elseif ( strtolower($bracket) == 'close' )
{
$result = $this->_where($key, $value, 'OR ', $escape);
end($this->ar_where);
$this->ar_where[key($this->ar_where)] .= ')';
return $result;
}
else
{
return $this->_where($key, $value, 'OR ', $escape);
}
}using or_where() on same field - El Forum - 05-21-2010
[eluser]blorriman[/eluser]
Thanks WanWizard,
I'll take a look at this.
using or_where() on same field - El Forum - 05-21-2010
[eluser]WanWizard[/eluser]
Note that this doesn't work on the first ->where() of your query.
To fix that I have to find a bit more time, as that involves a lot more changes, which means a better solution than this hack must be found.