Help with RIGHT JOIN conversion?

MySQL query is as follows:

Code:

SELECT RossResearchDB.property.id, RossResearchDB.property.lat, RossResearchDB.property.long, RossResearchDB.property.property_name, RossResearchDB.liked_property.FK_Renter FROM RossResearchDB.liked_property right join RossResearchDB.property
on RossResearchDB.liked_property.FK_Property = RossResearchDB.property.id
and FK_Renter = 2;

I am lost on how to set this up using the CI syntax.

Any help on the way ahead is appreciated!

Ross

I have this figured out so far: (shortened my requested fields until I get this figured out)

Code:

$this->db->select('property.id , property.lat, liked_property.FK_Property');
$this->db->from('property');
$this->db->join('liked_property', 'liked_property.FK_Property = property.id', 'left');
$this->db->where('liked_property.FK_Renter', 2);

except that "where" is not giving me the AND portion...  I get 

SELECT `property`.`id`, `property`.`lat`, `liked_property`.`FK_Property` FROM `property` LEFT JOIN `liked_property` ON `liked_property`.`FK_Property` = `property`.`id` WHERE `liked_property`.`FK_Renter` = 2

@nfoboy,

Try this https://codeigniter.com/user_guide/datab...y-bindings it should do the job for you.

$this->db->select('property.id , property.lat, liked_property.FK_Property');
$this->db->from('property');
$this->db->join('liked_property', 'liked_property.FK_Property = property.id and liked_property.FK_Renter = 2', 'left');
$this->db->where('liked_property.FK_Renter', 2);

@nfoboy,

Try this https://codeigniter.com/user_guide/datab...y-bindings it should do the job for you.

I saw that, but I didn't get that I could use it with the join statement, as vincent78 pointed out.

$this->db->select('property.id , property.lat, liked_property.FK_Property');
$this->db->from('property');
$this->db->join('liked_property', 'liked_property.FK_Property = property.id and liked_property.FK_Renter = 2', 'left');
$this->db->where('liked_property.FK_Renter', 2);

Thanks so much!  I was trying the Query Builder documentation, but just did not catch this particular nuance of chaining...

@nfoboy,

For future reference.

Query binding allows you to write a full sql statement with or without any variables that are needed to complete the query.

$sql = "SELECT p.id, p.lat, l.FK_Property FROM property as p LEFT JOIN liked_property as l ON l.FK_Property = p.id and l.FK_Renter = 2";
$this->db->query($sql);

@nfoboy,

For future reference.

Query binding allows you to write a full sql statement with or without any variables that are needed to complete the query.

$sql = "SELECT p.id, p.lat, l.FK_Property FROM property as p LEFT JOIN liked_property as l ON l.FK_Property = p.id and l.FK_Renter = 2";
$this->db->query($sql);

Thanks... I'm trying to get my CI Judo up to snuff, and having to translate SQL to CI, has been driving me batty at times. I can use this as a last resort to push what I need through....

You don't need to use the Query Builder. Just use your queries as is with query bindings instead.
https://codeigniter.com/user_guide/datab...y-bindings