CodeIgniter SELECT query to find rows with qualifying sets of column values in a JOINed table

Question

I have this SQL query and It looks confusing enough for me to think how I will convert this to a Codeigniter Active Record query.

I considered looping through it getting by line like this:

for ( $i=1 ; $i<=5 ; $i++) {
    ${'line'.$i} = $this->staffrequisition_model->getRequestsToProcess($i, $staffInfo->position_depth, $staffInfo->department_group);
}
$data = array_merge($line1, $line2, $line3, $line4, $line5);

Using this method in my model

public function getRequestsToProcess($i, $depth, $departmentGroup)
{
    $this->db->select('*, ns_request_info.idx as request_idx, ns_staff_info.idx as staff_idx');    
    $this->db->from('ns_request_info');
    $this->db->join('ns_staff_info', 'ns_request_info.requested_by_idx = ns_staff_info.member_idx');
    $this->db->where('line'.$i.'', $depth);
    $this->db->where('line'.$i.'_action', 'PENDING');
    if ($departmentGroup != 'HIGHER TIER') {
        $this->db->where('ns_staff_info.department_group', $departmentGroup);
    }
    $this->db->where('status', 'PENDING');
    $this->db->or_where('status', 'ONGOING');
    $this->db->where('line' . $i . '', $depth);
    $this->db->where('line' . $i . '_action', 'PENDING');
    if ($departmentGroup != 'HIGHER TIER') {
       $this->db->where('ns_staff_info.department_group', $departmentGroup);
    }
    $this->db->order_by('idx', 'desc');
    $query = $this->db->get();
    return $query->result_array();
}

But I realized that doing array merge for all of them would destroy the sorting of the id's of the result.

I have here my SQL query and I have no idea on how to transform this into a Codeigniter Active Record query.

SELECT *, ns_request_info.idx as request_idx, ns_staff_info.idx as staff_idx 

FROM `ns_request_info` 
INNER JOIN `ns_staff_info` 
    ON ns_request_info.requested_by_idx = ns_staff_info.member_idx 
WHERE
    (
        (ns_request_info.line1=1 AND ns_request_info.line1_action='PENDING')
        OR (ns_request_info.line2=1 AND ns_request_info.line2_action='PENDING')
        OR (ns_request_info.line3=1 AND ns_request_info.line3_action='PENDING')
        OR (ns_request_info.line4=1 AND ns_request_info.line4_action='PENDING')
        OR (ns_request_info.line5=1 AND ns_request_info.line5_action='PENDING')
    )
    AND (ns_request_info.status='PENDING' OR ns_request_info.status='ONGOING') 
ORDER BY ns_request_info.idx DESC

zahorniak · Accepted Answer · 2018-08-01 08:12:16Z

1

Try something like this:

    $search_val = 1;
    $action = "PENDING";
    $this->db->where("nri.line1 = $search_val and nri.line1_action = '{$this->db->escape_str($action)}'")
    ->or_where("nri.line2 = $search_val and nri.line2_action = '{$this->db->escape_str($action)}'")
    ->or_where("nri.line3 = $search_val and nri.line3_action = '{$this->db->escape_str($action)}'")
    ->or_where("nri.line4 = $search_val and nri.line4_action = '{$this->db->escape_str($action)}'")
    ->or_where("nri.line5 = $search_val and nri.line5_action = '{$this->db->escape_str($action)}'")
    ->join('ns_staff_info nsi', 'nsi.member_idx = nri.requested_by_idx')
    ->get('ns_request_info nri')->result_array();

answered Aug 1, 2018 at 8:12

zahorniak

1355 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Azis · Accepted Answer · 2018-08-01 08:49:19Z

public function getRequestsToProcess($depth,$departmentGroup)
    {
        $this->db->select('*, ns_request_info.idx as request_idx, ns_staff_info.idx as staff_idx');    
        $this->db->from('ns_request_info');
        $this->db->join('ns_staff_info', 'ns_request_info.requested_by_idx = ns_staff_info.member_idx');
        $this->db->where("((ns_request_info.line1=".$depth." AND ns_request_info.line1_action='PENDING') OR (ns_request_info.line2=".$depth." AND ns_request_info.line2_action='PENDING') OR (ns_request_info.line3=".$depth." AND ns_request_info.line3_action='PENDING') OR (ns_request_info.line4=".$depth." AND ns_request_info.line4_action='PENDING') OR (ns_request_info.line5=".$depth." AND ns_request_info.line5_action='PENDING'))");
        $this->db->where("(ns_request_info.status='PENDING' OR ns_request_info.status='ONGOING')");
        if($departmentGroup!='HIGHER TIER'){ $this->db->where('ns_staff_info.department_group', $departmentGroup); }
        $this->db->order_by('ns_request_info.idx', 'desc');
        $query = $this->db->get();
        return $query->result();
    }

mickmackusa · Accepted Answer · 2024-09-26 12:51:03Z

You can avoid the parenthetically grouped pairs of conditions by using Row Constructor syntax in a WHERE IN statement. In this implementation, the values to be matched as written first and the pairs of columns are written inside of the IN() portion.

Code:

if ($departmentGroup != 'HIGHER TIER') {
    $this->db->where('staff.department_group', $departmentGroup);
}
return $this->db
    ->select('*, req.idx request_idx, staff.idx staff_idx')
    ->join('ns_staff_info staff', 'req.requested_by_idx = staff.member_idx')
    ->where_in(
        sprintf('(%d, %s)', $depth, $this->db->escape('PENDING')),
        array_map(
            fn($i) => "(req.line{$i}, req.line{$i}_action)",
            range(1, 5)
        ),
        false
    )
    ->where_in('req.status', ['PENDING', 'ONGOING'])
    ->order_by('req.idx', 'DESC')
    ->get('ns_request_info req')
    ->result();

Rendered SQL:

SELECT *, `req`.`idx` `request_idx`, `staff`.`idx` `staff_idx`
FROM `ns_request_info` `req`
JOIN `ns_staff_info` `staff` ON `req`.`requested_by_idx` = `staff`.`member_idx`
WHERE (1, 'PENDING') IN((req.line1, req.line1_action), (req.line2, req.line2_action), (req.line3, req.line3_action), (req.line4, req.line4_action), (req.line5, req.line5_action))
AND `req`.`status` IN('PENDING', 'ONGOING')
ORDER BY `req`.`idx` DESC

I must say, though, that it is bad practice to be making iterated trips to the database as your script is designed. My ultimate advice would be avoid the loop and make just one trip to the database. If I understand the intention, this would involve adjusting the WHERE and ORDER BY clauses.

Collectives™ on Stack Overflow

CodeIgniter SELECT query to find rows with qualifying sets of column values in a JOINed table

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related