CODEWITHSUNDEEP
phpmysqlcodeigniteractiverecordcodeigniter-3
gdfgdfg
gdfgdfg

Reputation: 3566

Multiple joins with where, like and or_like CodeIgniter 3 Active Records

I am trying this:

        $location = 'a';
        $this->db->select('l.id, d.guests, l.city, l.country');
        $this->db->from('offers as o');
        $this->db->join('location as l', 'l.id = o.id', 'left');
        $this->db->join('desc as d', 'd.offer_id = o.id', 'left');
        $this->db->where('d.guests', $guests);
        $this->db->where('o.completed', 1);
        $this->db->where('l.country LIKE', $location.'%');
        $this->db->or_where('l.city LIKE', $location.'%');
        $this->db->limit(5);

And I have offer with 3 guests and with country Albania (1 row per table for it). But, if $guests = 2; I have result this 1 row. It is same, if I use like and or_like instead where and or_where. If I comment this line:

$this->db->or_where('l.city LIKE', $location.'%');

all works fine, I have no results if $guests != 3 and 1 row result if $guests = 3.

Generated query with $this-db->last_query() is:

SELECT 'l'.'id', 'd'.'guests', 'l'.'city', 'l'.'country' FROM 'offers' as 'o' LEFT JOIN 'location' as 'l' ON 'l'.'id' = 'o'.'id' LEFT JOIN 'desc' as 'd' ON 'd'.'id' = 'o'.'id' WHERE 'd'.'guests' = 3 AND 'o'.'completed' = 1 AND 'l'.'country' LIKE 'a%' OR 'l'.'city' LIKE 'a%' LIMIT 5

How can I make this query ? Select values where completed = 1, guests = $guests and city or country like $location.

Upvotes: 1

Views: 969

Answers (3)

Arun Sharma
Arun Sharma

Reputation: 13

Try this

$location = 'a';
 $this->db->select('l.id, d.guests, l.city, l.country');
 $this->db->from('offers as o');
 $this->db->join('location as l', 'l.id = o.id', 'left');
 $this->db->join('desc as d', 'd.offer_id = o.id', 'left');
 $this->db->where('d.guests', $guests);
 $this->db->where('o.completed', 1);
 $this->db->like('l.country',$location,'after');
 $this->db->or_like('l.city',$location,'after');
 $this->db->limit(5);

Upvotes: 0

Alexander Popov
Alexander Popov

Reputation: 836

You need to use Query grouping

$location = 'a';
$this->db->select('l.id, d.guests, l.city, l.country');
$this->db->from('offers as o');
$this->db->join('location as l', 'l.id = o.id', 'left');
$this->db->join('desc as d', 'd.offer_id = o.id', 'left');
$this->db->where('d.guests', $guests);
$this->db->where('o.completed', 1);
$this->db->->group_start();
$this->db->where('l.country LIKE', $location.'%');
$this->db->or_where('l.city LIKE', $location.'%');
$this->db->group_end();
$this->db->limit(5);

Also, take a look at the like and or_like methods to avoid code like this 'l.country LIKE'.

Upvotes: 0

parth
parth

Reputation: 1868

try this code 

$location = 'a';
$this->db->select('l.id, d.guests, l.city, l.country');
$this->db->from('offers as o');
$this->db->join('location as l', 'l.id = o.id', 'left');
$this->db->join('desc as d', 'd.offer_id = o.id', 'left');
$this->db->where('d.guests', $guests);
$this->db->where('o.completed', 1);
$this->db->where("(l.country LIKE '".$location."%' or l.city LIKE '".$location."%')");
$this->db->limit(5);

Upvotes: 1

Related Questions