CODEWITHSUNDEEP
codeigniteractiverecordwhere-clause
Mehmed
Mehmed

Reputation: 3040

Codeigniter - How to get values as array to use in next select with where_not_in

I have three tables; user, car and user_x_car. user_x_car holds users who own car; user_id and car_id are stored. I want to get users who don't own a car as follows:

$car_owner = $this->db->select()->from('user_x_car')->get()->result();

for ($i = 0; $i < count($car_owners); $i++)
    $car_owner_id[$i] = $car_owner[$i]->user_id;

$non_car_owner = $this->db->select()->from('user')->where_not_in('id', $car_owner_id)->get()->result();

I get what I want, however, is there any way to bypass the for loop in the middle which creates and array of id's selected in the first select. Is there any way to get array of selected user_ids directly?

Upvotes: 3

Views: 2784

Answers (3)

Imran Qamer
Imran Qamer

Reputation: 2265

function get_unread_notifications_ids()
    {
        //get unread notifications ids
        $this->db->select('GROUP_CONCAT(fknotification_id) as alll');
        $this->db->from("te_notification_status_tbl");
        $this->db->where('read_status',0);

        $ids=$this->db->get()->row();
        return $idss=str_replace(",","','",$ids->alll);
    }

and second function like this:

function get_unviewed_photos_events(){
        $idss = $this->get_unread_notifications_ids();

        $this->db->select('img.*',False);
        $this->db->from("te_notifications_tbl notif");
        $this->db->join('te_images_tbl img','img.id=notif.reference_id','LEFT OUTER');

        $this->db->where("notif.id IN('".$idss."')");
        $rslt = $this->db->get()->result_array();
        return $rslt;
    }

Upvotes: 1

ABorty
ABorty

Reputation: 2522

you can do it by two queries like

first one get all ids from user_x_car table

$temp1=array();
$temp=$this->db->distinct()->select('user_id')->get('user_x_car')->result_array();

then from user table fetch those users who have no cars

foreach($temp as $each)
{
    array_push($temp1,$each['user_id']);
}
$rs=$this->db->where_not_in('id',$temp1)->get('user');
if($rs->num_rows()>0)
{
    $data=$rs->result_array();
    print_r($data);die;
}

$data will print all users who have no car. Please let me know if you face any problem.

Upvotes: 1

John
John

Reputation: 584

Query

$non_car_owner = $this->db->query('SELECT user.*
FROM user LEFT JOIN user_x_car ON user_x_car.id=user.id
WHERE table2.id IS NULL')->result();

Here users who are not on the table user_x_car

foreach($non_car_owner as $user){
   echo $user->user_id;
}

Upvotes: 0

Related Questions