CodeIgniter Forums
Codeigniter 4 Query Builder Getting all match records from second table - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: CodeIgniter 4 (https://forum.codeigniter.com/forumdisplay.php?fid=28)
+--- Forum: CodeIgniter 4 Support (https://forum.codeigniter.com/forumdisplay.php?fid=30)
+--- Thread: Codeigniter 4 Query Builder Getting all match records from second table (/showthread.php?tid=91423)

Codeigniter 4 Query Builder Getting all match records from second table - shonirits - 08-07-2024

I want to improve my sql query, can you please check and let me know how i can do this within one query? right know i am using two different function but i want to do this with one query.
I am getting conversation details included verifying is user blocked or not or part of this conversation or not and also getting list of all participants of conversation. right now with two different query and functions its working fine and giving me result which i need. but i want to do this with one function with one query.
FIRST FUNCTION/ QUERY:
PHP Code:
function get_user_chat_info($chat_id, $user_id){

    $builder = $this->db->table('chats cht')
  ->select('cht.chat_id, cht.room_id, cht.user_id, cht.recipient_id, cht.admin_id, cht.started_at, cht.total_participants, cht.total_messages, cht.last_message_at, COUNT(eu.participant_id) as joined, COUNT(blk.block_id) as blocked')
  ->join('chat_participants eu', 'eu.room_id=cht.room_id AND eu.user_id='.$user_id, 'LEFT')
  ->join('blocks blk', 'blk.chat_id=cht.chat_id AND blk.user_id='.$user_id, 'LEFT')
  ->having('joined > 0 OR COUNT(CASE WHEN cht.user_id = '.$user_id.' OR cht.recipient_id = '.$user_id.' THEN 1 END) >= 1')
  ->where('cht.chat_id', $chat_id)
  ->groupBy('cht.chat_id');
  $query = $builder->get();
  $db_data = $query->getRowArray();
  if($db_data){    
    $select_participant = 'participant_id, room_id, chat_id, user_id, participant_at';
      $where_participant = array('chat_id' => $chat_id);  
      $db_data['participants'] = $this->get_all_chat_participants($where_participant, $select_participant);
  }
  return $db_data;

  } 


SECOND FUNCTION/QUERY:
PHP Code:
function get_all_chat_participants($where, $select=''){

    $builder = $this->db->table('chat_participants');
    if($select){
    $builder->select($select);
    }
    $query = $builder->where($where)
                    ->get();
    $db_data = $query->getResultArray();
    return $db_data;

  } 


RESULT:
[Image: Screenshot-2024-08-08-at-01-56-22.png]

I want same result with one query without using get_all_chat_participants function and same query use in first function.