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 mbareara - January 10, 2020

Thank you Daniel

Is it the same if i use this code?

// load record from 'restaurants'
  list($restaurantsRecords, $restaurantsMetaData) = getRecords(array(
    'tableName'   => 'restaurants',
    'where'       => whereRecordNumberInUrl(0),
    'loadUploads' => true,
    'allowSearch' => false,
    'limit'       => '1',
  ));
  $localiRecord = @$restaurantsRecords[0]; // get first record


 list($reviewRecords, $reviewMetaData) = getRecords(array(
    'tableName'   => 'review',
	'where' => 'restaurant = ' . getNumberFromEndOfUrl(), 
	  'loadUploads' => false,
    'allowSearch' => false,
	 'limit'       => '1',
  ));
  $reviewRecord = @$reviewRecords[0]; // get first record