CODEWITHSUNDEEP
mysqlcodeigniter
Piyush
Piyush

Reputation: 4007

How to use "MAX" in codeigniter where?

There is $this->db_selec_max(); for select max element from row set in select but I want to use max in where. How to use "MAX" in codeigniter where? I want to convert following query in to codeigniter form

My query

SELECT rq_id, rq_plant_4sale_code, rq_serial_number, rq_quantity_requested, rq_requester_farm_id, 
         rq_fulfiller_farm_id, rqpri_description, rqrem_remark,rqrem_remark_datetime, rq_created_datetime, 
         plsal_name_botanical, plsal_name_english, pot_code, rqpri_description 
         FROM (reqn_requisitions)
         LEFT JOIN reqn_requisition_priorities ON(rq_priority_rank=rqpri_rank)
         LEFT JOIN reqn_requisition_remarks ON(rq_id=rqrem_reqn_id)
         LEFT JOIN tukai_plants_4sale ON(rq_plant_4sale_code=plsal_id)
         LEFT JOIN tukai_pots ON(plsal_pot_id=pot_id)
         WHERE rqrem_remark_datetime IN(SELECT MAX(rqrem_remark_datetime) AS dt FROM reqn_requisition_remarks         GROUP BY rqrem_reqn_id ) AND rq_challan_id=0'

I am trying like this

$this->db->select("rq_id, rq_plant_4sale_code, rq_serial_number, rq_quantity_requested, rq_requester_farm_id, 
     rq_fulfiller_farm_id, rqpri_description, rqrem_remark,rqrem_remark_datetime, rq_created_datetime, 
     plsal_name_botanical, plsal_name_english, pot_code, rqpri_description ");

               $this->db->join('tukai_plants_4sale', 
               'tukai_plants_4sale.plsal_id = reqn_requisitions.rq_plant_4sale_code','left');
               $this->db->join('tukai_pots', 
               'tukai_pots.pot_id = tukai_plants_4sale.plsal_pot_id','left');
               $this->db->join('reqn_requisition_remarks', 
               'reqn_requisition_remarks.rqrem_reqn_id = reqn_requisitions.rq_id','left');


$this->db->order_by("rq_id","desc");

Upvotes: 2

Views: 227

Answers (1)

Parag Tyagi
Parag Tyagi

Reputation: 8960

For complex where clause conditions you can use,

$this->db->where('<where condition here>', NULL, FALSE);

From docs:

$this->db->where() accepts an optional third parameter. If you set it to FALSE, CodeIgniter will not try to protect your field or table names with backticks.


Try below code:

/* Replace table_name with appropriate table names */

$this->db->select("table_name.rq_id, table_name.rq_plant_4sale_code, table_name.rq_serial_number, table_name.rq_quantity_requested, table_name.rq_requester_farm_id, 
 table_name.rq_fulfiller_farm_id, table_name.rqpri_description, rqrem_remark,table_name.rqrem_remark_datetime, table_name.rq_created_datetime, 
 table_name.plsal_name_botanical, table_name.plsal_name_english, table_name.pot_code, table_name.rqpri_description ");

$this->db->from('reqn_requisitions');

$this->db->join('reqn_requisition_priorities','reqn_requisitions.rq_priority_rank = reqn_requisition_priorities.rqpri_rank','left');
$this->db->join('reqn_requisition_remarks','reqn_requisition_remarks.rqrem_reqn_id = reqn_requisitions.rq_id','left');
$this->db->join('tukai_plants_4sale','tukai_plants_4sale.plsal_id = reqn_requisitions.rq_plant_4sale_code','left');
$this->db->join('tukai_pots','tukai_pots.pot_id = tukai_plants_4sale.plsal_pot_id','left');

$this->db->where('reqn_requisition_remarks.rqrem_remark_datetime IN(SELECT MAX(rqrem_remark_datetime) AS dt FROM reqn_requisition_remarks GROUP BY rqrem_reqn_id', NULL, FALSE);

$this->db->where('table_name.rq_challan_id', 0);

$this->db->order_by("table_name.rq_id","desc");

$query = $this->db->get();

return $query->result();


An alternative:

If your query (anyhow) cannot be written using CI's Active Record, you can always use a simple method:

$this->db->query('<your SQL query here>');

For example:

$query = $this->db->query('SELECT rq_id, rq_plant_4sale_code, rq_serial_number,rq_quantity_requested, rq_requester_farm_id,rq_fulfiller_farm_id, rqpri_description,rqrem_remark,rqrem_remark_datetime, rq_created_datetime, 
     plsal_name_botanical, plsal_name_english, pot_code, rqpri_description 
     FROM (reqn_requisitions)
     LEFT JOIN reqn_requisition_priorities ON(rq_priority_rank=rqpri_rank)
     LEFT JOIN reqn_requisition_remarks ON(rq_id=rqrem_reqn_id)
     LEFT JOIN tukai_plants_4sale ON(rq_plant_4sale_code=plsal_id)
     LEFT JOIN tukai_pots ON(plsal_pot_id=pot_id)
     WHERE rqrem_remark_datetime IN(SELECT MAX(rqrem_remark_datetime) AS dt FROM reqn_requisition_remarks GROUP BY rqrem_reqn_id ) AND rq_challan_id=0');

return $query->result();

Upvotes: 1

Related Questions