LeftJoin Question

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!

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];

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

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.

Ok, great to hear.  Thanks for the update!