LeftJoin Question
6 posts by 2 authors in: Forums > CMS Builder
Last Post: December 13, 2016 (RSS)
By KCMedia - December 10, 2016
Hi guys
I have this section called Products which lists details about all the products.
Then there are 2 other multi sections for FAQ's and Testimonials.
I have the FAQ's and testimonials setup with a list item for each so that each of the FAQ and Testimonials can be associated with products.
I want to be able to pull the details from each of them and then display with the related product page.
How can i put all the related records for each of the FAQ and Testimonials.?
This is what i have but it wont pull the related records
// load record from 'products'
list($products, $productsMeta) = getRecords(array(
'tableName' => 'products',
'where' => 'products.num =' . getNumberFromEndOfUrl(),
'loadUploads' => true,
'allowSearch' => false,
'limit' => '1',
'leftJoin' => array('faqs' => 'title', 'testimonials' => 'title'),
//'debugSql' => true,
));
$products = @$products[0];
Craig
KC Media Solutions
www.kcmedia.biz
By Dave - December 13, 2016
Hi Craig,
How many records do you have in each of the three sections? If it's not a lot it may be easier to just load all the records from all three sections and loop over them. Also it's a best practice to associate records by recordNum instead of "title" so that if the title changes (or is corrected) the records are still associated (since the record num won't change).
Hope that helps!
interactivetools.com
By KCMedia - December 13, 2016
hi Dave
Well at the moment there isnt any but we are expecting a few in there and the products could be changing a lot so that is why LeftJoin would be better as the FAQ and Testimonials will be linked to each product. they will not be shown on other pages on the product page related to the product.
I have change the associated records to use this but still it wont load any records from the leftJoin tables and i have no idea why it wouldnt be.
// load record from 'products'
list($products, $productsMeta) = getRecords(array(
'tableName' => 'products',
'where' => 'products.num =' . getNumberFromEndOfUrl(),
'loadUploads' => true,
'allowSearch' => false,
'limit' => '1',
'leftJoin' => array('faqs' => 'num', 'testimonials' => 'num'),
//'debugSql' => true,
));
$products = @$products[0];
Craig
KC Media Solutions
www.kcmedia.biz
By KCMedia - December 13, 2016
also when i turn on Debug here is what is outputted but still wont load any records for the FAQ's and Testimonials
SELECT SQL_CALC_FOUND_ROWS `products`.*,
faqs.`num` as `faqs.num`,
faqs.`products` as `faqs.products`,
faqs.`question` as `faqs.question`,
faqs.`content` as `faqs.content`,
testimonials.`num` as `testimonials.num`,
testimonials.`products` as `testimonials.products`,
testimonials.`name` as `testimonials.name`,
testimonials.`content` as `testimonials.content`
FROM `cmsb_products` as `products`
LEFT JOIN `cmsb_faqs` AS `faqs` ON products.`num` = faqs.num
LEFT JOIN `cmsb_testimonials` AS `testimonials` ON products.`num` = testimonials.num
WHERE (products.num =1)
ORDER BY dragSortOrder DESC
Craig
KC Media Solutions
www.kcmedia.biz
By KCMedia - December 13, 2016
Hi Dave
I found a way around it so now each section FAQs and Testimonials is using its own Load Records statement and then using a custom Where statement to pull the ProductsNum from the Products section and then relating them to each record to display only the records related to that product.
Craig
KC Media Solutions
www.kcmedia.biz
By Dave - December 13, 2016
Ok, great to hear. Thanks for the update!
interactivetools.com