Welcome Guest, Not a member yet? Register   Sign In
Adding parenthesis to db queries in CI?
#1

[eluser]Ninjabear[/eluser]
I need to be able to do a db query like this in active record. I need the parenthesis otherwise it will ignore the AND u_id=383 and treat it as OR I suppose:

Code:
SELECT *, year(e.date_start) as start_year, year(e.date_end) as end_year,
month(e.date_start) as start_month, month(e.date_end) as end_month,
day(e.date_start) as start_day, day(e.date_end) as end_day
FROM (`calendar` e)
WHERE (`type_project` = 0 OR `type_todo` = 1)
AND u_id = 383;

The current code is like this:

Code:
$this->db->select("*,
   year(e.date_start) as start_year, year(e.date_end) as end_year,
   month(e.date_start) as start_month, month(e.date_end) as end_month,
   day(e.date_start) as start_day, day(e.date_end) as end_day",
   false);
  
   $this->db->from('calendar e');

   //Limit / offset
   if(isset($options['limit']) && isset($options['offset']))
     $this->db->limit($options['limit'],$options['offset']);
   else if(isset($options['limit']))
    $this->db->limit($options['limit']);  
  
   if(isset($options['sortBy']) && isset($options['sortDirection']))
     $this->db->order_by($options['sortBy'],$options['sortDirection']);
  
   if(isset($options['p_id']))
     $this->db->where('e.p_id',$options['p_id']);

   if(isset($options['t_id']))
     $this->db->where('e.t_id',$options['t_id']);
    
   if(isset($options['e_id']))
     $this->db->where('e.e_id',$options['e_id']);    
    
   //Filters
   if(isset($options['type_project']))
   {
     $this->db->where('type_project',$options['type_project']);

    if(isset($options['type_todo']))
    {
    $this->db->or_where('type_todo',$options['type_todo']);
    }
   }
   elseif( isset($options['type_todo']) )
   {
    $this->db->where('type_todo',$options['type_todo']);
   }
  
   //Show this only for non-admins. Admins can see items for all users.
   if( $options['user_type'] != 'admin' ):
            echo 'user type is not admin';
     if( isset( $options['u_id'] ) ):
             $this->db->where('u_id',$options['u_id'],FALSE);
     endif;
   endif;
  
   $query = $this->db->get();//query

P.s. I've considered doing the method chaining approach but I can't add if statements to each portion if I do that.
#2

[eluser]apodner[/eluser]
Here is an Active Record Extension to do this.


https://github.com/AndrewPodner/ci_ar_where_paren
It should get the job done




Theme © iAndrew 2016 - Forum software by © MyBB