[SOLVED] MyBlog Integration has Broken SQL

[Christopher Ambler]

I have used the integration with MyBlog, and it appears to work fine with the single exception of the view that shows a paginated list of reviews for blog entries. When looking at a blog entry, there is a button to view all reviews. When clicking it, the page comes up to see them, and pagination is correct, but there are no reviews shown. The value of $reviews is empty. In tracing through why this is, I trapped SQL calls inside of S2's base class for MyModel and found that the SQL is being crafted as below.

 

What you will see below is that the JOIN statements have multiple joins for the same tables and give them duplicate aliases. I can't seem to find out why this is happening, but I suspect you will see this and know immediately.

 

In looking at the queries created on the actual blog entry page (that only shows the first 5 comments), the query is correct. But on the detail page, it's got the duplicates.

 

Help!

 

Not unique table/alias: 'Listing' SQL=SELECT Review.id AS `Review.review_id`,
Review.pid AS `Review.listing_id`,
Review.mode AS `Review.extension`,
Review.created AS `Review.created`,
Review.modified AS `Review.modified`,
Review.userid AS `User.user_id`,
CASE WHEN CHAR_LENGTH(User.name) THEN User.name ELSE Review.name END AS `User.name`,
CASE WHEN CHAR_LENGTH(User.username) THEN User.username ELSE Review.username END AS `User.username`,
Review.email AS `User.email`,
Review.location AS `User.location`,
Review.ipaddress AS `User.ipaddress`,
Review.title AS `Review.title`,
Review.comments AS `Review.comments`,
Review.posts AS `Review.posts`,
Review.author AS `Review.editor`,
Review.published AS `Review.published`,
Rating.ratings AS `Rating.ratings`,
(Rating.ratings_sum/Rating.ratings_qty) AS `Rating.average_rating`,
Review.vote_helpful AS `Vote.yes`,
(Review.vote_total - Review.vote_helpful) AS `Vote.no`,
(Review.vote_helpful/Review.vote_total)*100 AS `Vote.helpful`,
Review.owner_reply_text AS `Review.owner_reply_text`,
Review.owner_reply_approved AS `Review.owner_reply_approved`,
Criteria.id AS `Criteria.criteria_id`,
Criteria.criteria AS `Criteria.criteria`,
Criteria.state AS `Criteria.state`,
Criteria.tooltips AS `Criteria.tooltips`,
Criteria.weights AS `Criteria.weights`
FROM jos_jreviews_comments AS Review
LEFT JOIN jos_jreviews_ratings AS Rating ON Review.id = Rating.reviewid
LEFT JOIN jos_users AS User ON Review.userid = User.id
LEFT JOIN jos_content AS Listing ON Review.pid = Listing.id
INNER JOIN jos_jreviews_categories AS JreviewsCategory ON Listing.catid = JreviewsCategory.id AND JreviewsCategory.`option` = 'com_myblog'
LEFT JOIN jos_categories AS Category ON Category.id = Listing.catid
LEFT JOIN jos_jreviews_criteria AS Criteria ON JreviewsCategory.criteriaid = Criteria.id
LEFT JOIN jos_content AS Listing ON Review.pid = Listing.id
LEFT JOIN jos_jreviews_categories AS JreviewsCategory ON Listing.catid = JreviewsCategory.id
LEFT JOIN jos_jreviews_criteria AS Criteria ON JreviewsCategory.criteriaid = Criteria.id
WHERE 1 = 1 AND ( 
  Review.pid= 703
  AND Review.author = 0
  AND Review.published = 1
  AND Review.mode = "com_myblog"
)
GROUP BY Review.id
ORDER BY `Review.created` DESC
LIMIT 10

[Alejandro]

This section is only visible with a valid subscription. If you have a valid subscription, please login.

[Christopher Ambler]

This section is only visible with a valid subscription. If you have a valid subscription, please login.