Help with LeftJoin

4 posts by 2 authors in: Forums > CMS Builder
Last Post: January 10, 2020   (RSS)

By mbareara - January 7, 2020

I have Two Tables: RESTAURANTS (num, name, city, address etc) REVIEW (num, restaurant, vote for service, vote for food etc)

REstaurants.num = Review.restaurant

Of course i have many restaurant, and non for only restaurants there is a review.

I would have a Restaurant page, and, in this page i would show a vote for service from review table. 

I know that i need a leftjoin (i suppose) but i'm a little confused

Thank you in advance for some help :-)

By daniel - January 9, 2020

Hi mbareara,

To use the leftJoin option in getRecords in this scenario, the basic syntax generally looks like this:

list($restaurantRecords, $restaurantDetails) = getRecords(array(
  'tableName' => 'restaurants',
  'leftJoin' => array(
    'review' => 'ON restaurants.num = review.restaurant',
  ),
));

This hasn't been tested, so it may require some tweaking to work with your code, and you'll want to also add any other filtering options to getRecords that you need. The returned records should include related reviews for each restaurant record.

Let me know if you have any issues or questions!

Thanks,

Daniel
Technical Lead
interactivetools.com

By daniel - January 10, 2020

Hi mbareara,

Yes, that is generally the same thing, but with a few small differences:

  • Here you're limiting the returned reviews to 1, whereas the leftJoin example would return all reviews.
  • This returns two arrays of records instead of one, which can be more or less complex to handle depending on how it's being used. If it's just on a record detail page then it's likely easier.

But if your code is returning the data you want in the way you want to use it, I'd recommend sticking with it! Let me know if you have any other questions.

Thanks,

Daniel
Technical Lead
interactivetools.com