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,
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 recordBy 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,
Technical Lead
interactivetools.com