CodeIgniter ActiveRecord question
Suppose I have a tasks table which contains tasks created by several people and where each task can be assigned to any other user, with columns like these: status - A string value, can be Open, In Progress or Closed created_by - int value, stores the id of the user who created the task assigned_to - int value, stores the id of the user assigned to a task And I want to see only open tasks created by or assigned to me, i.e. the query: SELECT * FROM tasks WHERE status = 'Open' AND (created_by = 3 OR assigned_to = 3) How can I do this using CodeIgniter ActiveRecord? -- Regards, Peter Karunyu -------------------
use $this->db->query(' your sql') should allow you to do anything On Thu, Apr 4, 2013 at 2:06 AM, Peter Karunyu <pkarunyu@gmail.com> wrote:
Suppose I have a tasks table which contains tasks created by several people and where each task can be assigned to any other user, with columns like these:
status - A string value, can be Open, In Progress or Closed created_by - int value, stores the id of the user who created the task assigned_to - int value, stores the id of the user assigned to a task
And I want to see only open tasks created by or assigned to me, i.e. the query:
SELECT * FROM tasks WHERE status = 'Open' AND (created_by = 3 OR assigned_to = 3)
How can I do this using CodeIgniter ActiveRecord?
-- Regards, Peter Karunyu -------------------
_______________________________________________ skunkworks mailing list skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
-- Ian Vuyayi Madege Software engineer As a programmer a computer will always do what you command it to do.
@Ian, so there is no way of using the constructs *$this->db->where()* or*$this->db->or_where() * in some magical way to achieve the above? On Thu, Apr 4, 2013 at 3:30 PM, Ian Madege <imadege1990@gmail.com> wrote:
use $this->db->query(' your sql')
should allow you to do anything
On Thu, Apr 4, 2013 at 2:06 AM, Peter Karunyu <pkarunyu@gmail.com> wrote:
Suppose I have a tasks table which contains tasks created by several people and where each task can be assigned to any other user, with columns like these:
status - A string value, can be Open, In Progress or Closed created_by - int value, stores the id of the user who created the task assigned_to - int value, stores the id of the user assigned to a task
And I want to see only open tasks created by or assigned to me, i.e. the query:
SELECT * FROM tasks WHERE status = 'Open' AND (created_by = 3 OR assigned_to = 3)
How can I do this using CodeIgniter ActiveRecord?
-- Regards, Peter Karunyu -------------------
_______________________________________________ skunkworks mailing list skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
-- Ian Vuyayi Madege Software engineer As a programmer a computer will always do what you command it to do.
_______________________________________________ skunkworks mailing list skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
-- Regards, Peter Karunyu -------------------
$rs = $this->db->where("status = 'Open' AND (created_by = 3 OR assigned_to = 3)")->get('tasks')->result(); On Thu, Apr 4, 2013 at 12:44 AM, Peter Karunyu <pkarunyu@gmail.com> wrote:
@Ian, so there is no way of using the constructs *$this->db->where()* or*$this->db->or_where() * in some magical way to achieve the above?
On Thu, Apr 4, 2013 at 3:30 PM, Ian Madege <imadege1990@gmail.com> wrote:
use $this->db->query(' your sql')
should allow you to do anything
On Thu, Apr 4, 2013 at 2:06 AM, Peter Karunyu <pkarunyu@gmail.com> wrote:
Suppose I have a tasks table which contains tasks created by several people and where each task can be assigned to any other user, with columns like these:
status - A string value, can be Open, In Progress or Closed created_by - int value, stores the id of the user who created the task assigned_to - int value, stores the id of the user assigned to a task
And I want to see only open tasks created by or assigned to me, i.e. the query:
SELECT * FROM tasks WHERE status = 'Open' AND (created_by = 3 OR assigned_to = 3)
How can I do this using CodeIgniter ActiveRecord?
-- Regards, Peter Karunyu -------------------
_______________________________________________ skunkworks mailing list skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
-- Ian Vuyayi Madege Software engineer As a programmer a computer will always do what you command it to do.
_______________________________________________ skunkworks mailing list skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
-- Regards, Peter Karunyu -------------------
_______________________________________________ skunkworks mailing list skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
you could also try as below $rs = $this->db->or_where( array('created_by'=>3, 'assigned_to'=>3))->where(array('status'=>'open'))->get('tasks')->result(); On Thu, Apr 4, 2013 at 2:33 AM, Nd'wex Common <flexycat@gmail.com> wrote:
$rs = $this->db->where("status = 'Open' AND (created_by = 3 OR assigned_to = 3)")->get('tasks')->result();
On Thu, Apr 4, 2013 at 12:44 AM, Peter Karunyu <pkarunyu@gmail.com> wrote:
@Ian, so there is no way of using the constructs *$this->db->where()* or*$this->db->or_where() * in some magical way to achieve the above?
On Thu, Apr 4, 2013 at 3:30 PM, Ian Madege <imadege1990@gmail.com> wrote:
use $this->db->query(' your sql')
should allow you to do anything
On Thu, Apr 4, 2013 at 2:06 AM, Peter Karunyu <pkarunyu@gmail.com>wrote:
Suppose I have a tasks table which contains tasks created by several people and where each task can be assigned to any other user, with columns like these:
status - A string value, can be Open, In Progress or Closed created_by - int value, stores the id of the user who created the task assigned_to - int value, stores the id of the user assigned to a task
And I want to see only open tasks created by or assigned to me, i.e. the query:
SELECT * FROM tasks WHERE status = 'Open' AND (created_by = 3 OR assigned_to = 3)
How can I do this using CodeIgniter ActiveRecord?
-- Regards, Peter Karunyu -------------------
_______________________________________________ skunkworks mailing list skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
-- Ian Vuyayi Madege Software engineer As a programmer a computer will always do what you command it to do.
_______________________________________________ skunkworks mailing list skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
-- Regards, Peter Karunyu -------------------
_______________________________________________ skunkworks mailing list skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
@Nd'wex, $rs = $this->db->where("status = 'Open' AND (created_by = 1 OR assigned_to = 1)")->get('tasks')->result(); This will give the desired query, but I forgot to mention one more condition, that I want to blindly pass in the values to go into $this->db->where and $this->db->or_where as an array, for example in the model: function get_tasks($where = array(), $or_where = array()) { if (is_array($where) && count($where) > 0) $this->db->where($where); } if (is_array($or_where) && count($or_where) > 0) $this->db->or_where($or_where); } } And then AR should know to put the brackets around the OR clauses Otherwise, if I end up with a query like: SELECT * FROM (`tasks`) WHERE `created_by` = 1 OR `assigned_to` = 1 AND `status` = 'Open'; Wont the OR clause (OR `assigned_to` = 1) have precedent and thus disregard (AND `status` = 'Open')? On Thu, Apr 4, 2013 at 5:35 PM, Nd'wex Common <flexycat@gmail.com> wrote:
you could also try as below
$rs = $this->db->or_where( array('created_by'=>3, 'assigned_to'=>3))->where(array('status'=>'open')) ->get('tasks')->result();
On Thu, Apr 4, 2013 at 2:33 AM, Nd'wex Common <flexycat@gmail.com> wrote:
$rs = $this->db->where("status = 'Open' AND (created_by = 3 OR assigned_to = 3)")->get('tasks')->result();
On Thu, Apr 4, 2013 at 12:44 AM, Peter Karunyu <pkarunyu@gmail.com>wrote:
@Ian, so there is no way of using the constructs *$this->db->where()* or * $this->db->or_where()* in some magical way to achieve the above?
On Thu, Apr 4, 2013 at 3:30 PM, Ian Madege <imadege1990@gmail.com>wrote:
use $this->db->query(' your sql')
should allow you to do anything
On Thu, Apr 4, 2013 at 2:06 AM, Peter Karunyu <pkarunyu@gmail.com>wrote:
Suppose I have a tasks table which contains tasks created by several people and where each task can be assigned to any other user, with columns like these:
status - A string value, can be Open, In Progress or Closed created_by - int value, stores the id of the user who created the task assigned_to - int value, stores the id of the user assigned to a task
And I want to see only open tasks created by or assigned to me, i.e. the query:
SELECT * FROM tasks WHERE status = 'Open' AND (created_by = 3 OR assigned_to = 3)
How can I do this using CodeIgniter ActiveRecord?
-- Regards, Peter Karunyu -------------------
_______________________________________________ skunkworks mailing list skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
-- Ian Vuyayi Madege Software engineer As a programmer a computer will always do what you command it to do.
_______________________________________________ skunkworks mailing list skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
-- Regards, Peter Karunyu -------------------
_______________________________________________ skunkworks mailing list skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
_______________________________________________ skunkworks mailing list skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
-- Regards, Peter Karunyu -------------------
function get_tasks($where = array(), $or_where = array()) { if (is_array($where) && count($where) > 0) $this->db->where($where); } if (is_array($or_where) && count($or_where) > 0) //overkill procedure $tmp = array(); foreach($or_where as $key => $val) { $tmp[] = $key."=".$val; } $or_where ="(" . implode(" or ", $tmp).")"; $this->db->where($or_where); unset($tmp); //discard tmp array } } hope this helps On Fri, Apr 5, 2013 at 1:37 PM, Peter Karunyu <pkarunyu@gmail.com> wrote:
@Nd'wex, $rs = $this->db->where("status = 'Open' AND (created_by = 1 OR assigned_to = 1)")->get('tasks')->result();
This will give the desired query, but I forgot to mention one more condition, that I want to blindly pass in the values to go into $this->db->where and $this->db->or_where as an array, for example in the model:
function get_tasks($where = array(), $or_where = array()) {
if (is_array($where) && count($where) > 0) $this->db->where($where); }
if (is_array($or_where) && count($or_where) > 0) $this->db->or_where($or_where); }
}
And then AR should know to put the brackets around the OR clauses
Otherwise, if I end up with a query like: SELECT * FROM (`tasks`) WHERE `created_by` = 1 OR `assigned_to` = 1 AND `status` = 'Open';
Wont the OR clause (OR `assigned_to` = 1) have precedent and thus disregard (AND `status` = 'Open')?
On Thu, Apr 4, 2013 at 5:35 PM, Nd'wex Common <flexycat@gmail.com> wrote:
you could also try as below
$rs = $this->db->or_where( array('created_by'=>3, 'assigned_to'=>3))->where(array('status'=>'open')) ->get('tasks')->result();
On Thu, Apr 4, 2013 at 2:33 AM, Nd'wex Common <flexycat@gmail.com> wrote:
$rs = $this->db->where("status = 'Open' AND (created_by = 3 OR assigned_to = 3)")->get('tasks')->result();
On Thu, Apr 4, 2013 at 12:44 AM, Peter Karunyu <pkarunyu@gmail.com>wrote:
@Ian, so there is no way of using the constructs *$this->db->where()*or * $this->db->or_where()* in some magical way to achieve the above?
On Thu, Apr 4, 2013 at 3:30 PM, Ian Madege <imadege1990@gmail.com>wrote:
use $this->db->query(' your sql')
should allow you to do anything
On Thu, Apr 4, 2013 at 2:06 AM, Peter Karunyu <pkarunyu@gmail.com>wrote:
Suppose I have a tasks table which contains tasks created by several people and where each task can be assigned to any other user, with columns like these:
status - A string value, can be Open, In Progress or Closed created_by - int value, stores the id of the user who created the task assigned_to - int value, stores the id of the user assigned to a task
And I want to see only open tasks created by or assigned to me, i.e. the query:
SELECT * FROM tasks WHERE status = 'Open' AND (created_by = 3 OR assigned_to = 3)
How can I do this using CodeIgniter ActiveRecord?
-- Regards, Peter Karunyu -------------------
_______________________________________________ skunkworks mailing list skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
-- Ian Vuyayi Madege Software engineer As a programmer a computer will always do what you command it to do.
_______________________________________________ skunkworks mailing list skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
-- Regards, Peter Karunyu -------------------
_______________________________________________ skunkworks mailing list skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
_______________________________________________ skunkworks mailing list skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
-- Regards, Peter Karunyu -------------------
_______________________________________________ skunkworks mailing list skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
Head over here >> http://stackoverflow.com, you will certainly find an accurate answer. On Thu, Apr 4, 2013 at 3:44 PM, Peter Karunyu <pkarunyu@gmail.com> wrote:
@Ian, so there is no way of using the constructs *$this->db->where()* or*$this->db->or_where() * in some magical way to achieve the above?
On Thu, Apr 4, 2013 at 3:30 PM, Ian Madege <imadege1990@gmail.com> wrote:
use $this->db->query(' your sql')
should allow you to do anything
On Thu, Apr 4, 2013 at 2:06 AM, Peter Karunyu <pkarunyu@gmail.com> wrote:
Suppose I have a tasks table which contains tasks created by several people and where each task can be assigned to any other user, with columns like these:
status - A string value, can be Open, In Progress or Closed created_by - int value, stores the id of the user who created the task assigned_to - int value, stores the id of the user assigned to a task
And I want to see only open tasks created by or assigned to me, i.e. the query:
SELECT * FROM tasks WHERE status = 'Open' AND (created_by = 3 OR assigned_to = 3)
How can I do this using CodeIgniter ActiveRecord?
-- Regards, Peter Karunyu -------------------
_______________________________________________ skunkworks mailing list skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
-- Ian Vuyayi Madege Software engineer As a programmer a computer will always do what you command it to do.
_______________________________________________ skunkworks mailing list skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
-- Regards, Peter Karunyu -------------------
_______________________________________________ skunkworks mailing list skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
participants (4)
-
Ian Madege -
Joseph Tintale -
Nd'wex Common -
Peter Karunyu