Codeigniter combine where_in and like active record queries

Question

I have the following active record query:

$this->db->select('id, email, first_name, last_name, current_location_state, current_location, avatar, avatar_fb');
          $this->db->from('users');
          $this->db->like('first_name', $search);
          $this->db->or_like('last_name', $search);
          $this->db->or_like("CONCAT(first_name, ' ', last_name)", $search);
          $this->db->or_like('email', $search);
          $this->db->where_in('id', $ids);

This is a function that has an array $ids, which has the ids of my friends. I want to search for friends that match my "like" queries, but only have one of the id's in the $ids variable.

I'm pretty sure i need to combine where_in and all the like statements so its something like (WHERE_IN $ids && Like Statements).

I'm not great at mysql so any help here would be appreciated.

Thanks!

function narrow_connections($search) {

       //First get all this users connections...
       $connections = $this->get_connections($this->session->userdata('user_id'), 0, 0);

       if(empty($connections)) {
          return array();
       }else {

          //Need to get an array of id's
          $ids = array();
          foreach($connections as $con) {
             array_push($ids, $con['id']);
          }

          //Now that we have an array of ID's, find all users that have one of the ids (our connections), AND match a search term to narrow down
          //the results. 

          $this->db->select('id, email, first_name, last_name, current_location_state, current_location, avatar, avatar_fb');
          $this->db->from('users');
          $this->db->like('first_name', $search);
          $this->db->or_like('last_name', $search);
          $this->db->or_like("CONCAT(first_name, ' ', last_name)", $search);
          $this->db->or_like('email', $search);
          $this->db->where_in('id', $ids);
          $query = $this->db->get();
          $data = array();

          foreach ($query->result() as $row) {
          $data[] = array(
            'id' => $row->id,
            'email' => $row->email,
            'first_name' => $row->first_name,
            'last_name' => $row->last_name,
            'current_location_state' => $row->current_location_state,
            'current_location' => $row->current_location,
            'avatar' => $row->avatar,
            'avatar_fb' => $row->avatar_fb,
          );
          }
          return $data;

       }
     }

In codeigniter, it's easier to write such query using old, raw sql rather than `active record` — baldrs, Jan 28 '14 at 19:36

baldrs · Accepted Answer · 2014-01-28T20:17:23.373

Do you want to find all the friends? If there are only one id, then you don't need like part, as you already found your friend. On the other part, if you not sure about id of your friends, and just want to find all friends matching your like criteria, you may remove where_in part.

This will find your friends:

$this->db->select('id, email, first_name, last_name, current_location_state, current_location, avatar, avatar_fb');
$this->db->from('users');
$this->db->like('first_name', $search);
$this->db->or_like('last_name', $search);
$this->db->or_like("CONCAT(first_name, ' ', last_name)", $search);
$this->db->or_like('email', $search);

Considering there's only one id, such query will find only one friend:

$this->db->select('id, email, first_name, last_name, current_location_state, current_location, avatar, avatar_fb');
$this->db->from('users');
$this->db->where_in('id', $ids);

EDIT

Sometimes, with codeigniter, it is better to use a raw query:

$this->db->query('
  SELECT `id`, `email`, `first_name`, `last_name`, `current_location_state`, `current_location`, `avatar`, `avatar_fb`
  FROM `users`
  WHERE (
    `first_name` like ?
    or `last_name` like ? 
    or concat(`first_name`, ' ', `last_name`) like ? 
    or `email` like ?)
  AND `id` in('
    .join(',',
    array_map(function($e) { return (int) $e; }, $ids))
    .')',
  "%$search%", "%$search%", "%$search%", "$search")->result();

Forgive me I should've specified that we're trying to narrow down our friends by name in this function. So we have a connections table with our "friends". So we get our friends ids in $ids. Then we're trying to say, of all the users with our friends ids, narrow down by the search term. I'll post more above. — Daniel White, Jan 28 '14 at 19:46
@DanielWhite and what's the problem, then? Your code seems to be correct. What it's doing wrong? — baldrs, Jan 28 '14 at 19:59
its basically getting the ids of my connections just fine, but the "$this->db->like" statements are over riding the "where_in" and just returning all users that match the like statements. I want to return all users that match the like statements AND are one of my connections. So (where_in $ids AND (like statements)). I just can't figure out how to write the SQL / Active record for it. — Daniel White, Jan 28 '14 at 20:01

score 0 · Answer 2 · answered Mar 18 '16 at 11:48

It's been a while, but I had the same problem and got it solved by simply reorder the filters. This means: you first have to do the 'where' statetemnt (not 'where_in')

This would look like:

$this->db->select('id, email, first_name, last_name, current_location_state, current_location, avatar, avatar_fb');

      $this->db->where('id', $ids);
      $this->db->from('users');
      $this->db->like('first_name', $search);
      $this->db->or_like('last_name', $search);
      $this->db->or_like("CONCAT(first_name, ' ', last_name)", $search);
      $this->db->or_like('email', $search);

Codeigniter combine where_in and like active record queries

2 Answers2