Bug Report [fixed in 3.2.17]

[sasho]

Hi,

I think i might have discovered a bug in jDownloads. I am using the latest version of Joomla 3.

When I went to create a new Menu Item that links to a single category. Then I went to "downloads order" and selected "most recent first", I got the error below:

Code Select Expand


If difficulties persist, please contact the System Administrator of this site and report the error below.

   500 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DESC LIMIT 0, 10' at line 15 SQL=SELECT a.file_id, a.file_title, a.file_alias, a.description, a.description_long, a.file_pic, a.images, a.price, a.release, a.file_language, a.system, a.license, a.url_license, a.license_agree, a.size, a.date_added, a.file_date, a.publish_from, a.publish_to, a.use_timeframe, a.url_download, a.preview_filename, a.other_file_id, a.md5_value, a.sha1_value, a.extern_file, a.extern_site, a.mirror_1, a.mirror_2, a.extern_site_mirror_1, a.extern_site_mirror_2, a.url_home, a.author, a.url_author, a.created_id, a.created_mail, a.modified_id, a.modified_date, a.submitted_by, a.set_aup_points, a.downloads, a.cat_id, a.changelog, a.password, a.password_md5, a.views, a.metakey, a.metadesc, a.robots, a.update_active, a.custom_field_1, a.custom_field_2, a.custom_field_3, a.custom_field_4, a.custom_field_5, a.custom_field_6, a.custom_field_7, a.custom_field_8, a.custom_field_9, a.custom_field_10, a.custom_field_11, a.custom_field_12, a.custom_field_13, a.custom_field_14, a.access, a.language, a.ordering, a.published, a.checked_out, a.checked_out_time, a.modified_date as modified, a.modified_id,CASE WHEN a.publish_from = 0 THEN a.date_added ELSE a.publish_from END as publish_from,a.publish_to, a.images, a.metakey, a.metadesc, a.access, a.downloads, LENGTH(a.description_long) AS readmore,CASE WHEN badcats.id is not null THEN 0 ELSE a.published END AS state,aa.url_download AS filename_from_other_download,c.title AS category_title, c.access AS category_access, c.alias AS category_alias, c.cat_dir AS category_cat_dir, c.cat_dir_parent AS category_cat_dir_parent,u.name AS creator,u2.name AS modifier,u2.name AS modifier,l.title AS license_title, l.url AS license_url, l.description AS license_text,ROUND(r.rating_sum / r.rating_count, 0) AS rating, r.rating_count as rating_count, r.rating_sum as rating_sum,parent.title as parent_title, parent.id as parent_id, parent.alias as parent_alias,menuf.id AS menuf_itemid,c.published, CASE WHEN badcats.id is null THEN c.published ELSE 0 END AS parents_published FROM psk_jdownloads_files AS a LEFT JOIN psk_jdownloads_files AS aa on aa.file_id = a.other_file_id LEFT JOIN psk_jdownloads_categories AS c ON c.id = a.cat_id LEFT JOIN psk_users AS ua ON ua.id = a.created_id LEFT JOIN psk_users AS uam ON uam.id = a.modified_id LEFT JOIN psk_users AS u on u.id = a.created_id LEFT JOIN psk_users AS u2 on u2.id = a.modified_id LEFT JOIN psk_jdownloads_licenses AS l on l.id = a.license LEFT JOIN psk_jdownloads_ratings AS r on r.file_id = a.file_id LEFT JOIN psk_jdownloads_categories as parent ON parent.id = c.parent_id LEFT JOIN psk_menu AS menuf on menuf.link LIKE CONCAT('index.php?option=com_jdownloads&view=download&id=',a.file_id,'%') AND menuf.published = 1 AND menuf.access IN (1,1) LEFT OUTER JOIN (SELECT cat.id as id FROM psk_jdownloads_categories AS cat JOIN psk_jdownloads_categories AS parent ON cat.lft BETWEEN parent.lft AND parent.rgt AND parent.published != 1 GROUP BY cat.id ) AS badcats ON badcats.id = c.id WHERE a.access IN (1,1) AND c.access IN (1,1) AND CASE WHEN badcats.id is null THEN a.published ELSE 0 END = 1 AND a.cat_id = 2 AND (a.publish_from = '0000-00-00 00:00:00' OR a.publish_from <= '2014-11-26 11:04:59') AND (a.publish_to = '0000-00-00 00:00:00' OR a.publish_to >= '2014-11-26 11:04:59') ORDER BY c.lft, a.date_added DESC DESC LIMIT 0, 10



The query

Code Select Expand


SELECT
a.file_id, a.file_title, a.file_alias, a.description, a.description_long, a.file_pic, a.images, a.price, a.release,
a.file_language, a.system, a.license, a.url_license, a.license_agree, a.size, a.date_added, a.file_date, a.publish_from,
a.publish_to, a.use_timeframe, a.url_download, a.preview_filename, a.other_file_id, a.md5_value, a.sha1_value, a.extern_file,
a.extern_site, a.mirror_1, a.mirror_2, a.extern_site_mirror_1, a.extern_site_mirror_2, a.url_home, a.author, a.url_author,
a.created_id, a.created_mail, a.modified_id, a.modified_date, a.submitted_by, a.set_aup_points, a.downloads, a.cat_id, a.changelog,
a.password, a.password_md5, a.views, a.metakey, a.metadesc, a.robots, a.update_active, a.custom_field_1, a.custom_field_2,
a.custom_field_3, a.custom_field_4, a.custom_field_5, a.custom_field_6, a.custom_field_7, a.custom_field_8, a.custom_field_9,
a.custom_field_10, a.custom_field_11, a.custom_field_12, a.custom_field_13, a.custom_field_14, a.access, a.language, a.ordering,
a.published, a.checked_out, a.checked_out_time, a.modified_date as modified, a.modified_id,
CASE WHEN a.publish_from = 0 THEN a.date_added ELSE a.publish_from END as publish_from,a.publish_to, a.images, a.metakey,
a.metadesc, a.access, a.downloads, LENGTH(a.description_long) AS readmore,
CASE WHEN badcats.id is not null THEN 0 ELSE a.published END AS state,aa.url_download AS filename_from_other_download,c.title AS category_title,
c.access AS category_access, c.alias AS category_alias, c.cat_dir AS category_cat_dir, c.cat_dir_parent AS category_cat_dir_parent,u.name AS creator,
u2.name AS modifier,u2.name AS modifier,l.title AS license_title, l.url AS license_url, l.description AS license_text,ROUND(r.rating_sum / r.rating_count, 0) AS rating,
r.rating_count as rating_count, r.rating_sum as rating_sum,parent.title as parent_title, parent.id as parent_id, parent.alias as parent_alias,menuf.id AS menuf_itemid,c.published,
CASE WHEN badcats.id is null THEN c.published ELSE 0 END AS parents_published

FROM psk_jdownloads_files AS a
LEFT JOIN psk_jdownloads_files AS aa on aa.file_id = a.other_file_id
LEFT JOIN psk_jdownloads_categories AS c ON c.id = a.cat_id
LEFT JOIN psk_users AS ua ON ua.id = a.created_id
LEFT JOIN psk_users AS uam ON uam.id = a.modified_id
LEFT JOIN psk_users AS u on u.id = a.created_id
LEFT JOIN psk_users AS u2 on u2.id = a.modified_id
LEFT JOIN psk_jdownloads_licenses AS l on l.id = a.license
LEFT JOIN psk_jdownloads_ratings AS r on r.file_id = a.file_id
LEFT JOIN psk_jdownloads_categories as parent ON parent.id = c.parent_id
LEFT JOIN psk_menu AS menuf on menuf.link LIKE CONCAT('index.php?option=com_jdownloads&view=download&id=',a.file_id,'%') AND menuf.published = 1 AND menuf.access IN (1,1)
LEFT OUTER JOIN
(
SELECT cat.id as id
FROM psk_jdownloads_categories AS cat
JOIN psk_jdownloads_categories AS parent ON cat.lft BETWEEN parent.lft AND parent.rgt AND parent.published != 1 GROUP BY cat.id
) AS badcats ON badcats.id = c.id

WHERE
a.access IN (1,1)
AND c.access IN (1,1)
AND CASE WHEN badcats.id is null THEN a.published ELSE 0 END = 1
AND a.cat_id = 2
AND (a.publish_from = '0000-00-00 00:00:00' OR a.publish_from <= '2014-11-26 10:43:22')
AND (a.publish_to = '0000-00-00 00:00:00' OR a.publish_to >= '2014-11-26 10:43:22')

ORDER BY c.lft, a.date_added DESC DESC LIMIT 0, 10


Looks like the query has "DESC" twice in the end.

thanks,
Alex

[Arno]

Hi Alex,

Looks like the query has "DESC" twice in the end.

Yes, seems to be a bug. Will check this later today.

[bustergut]

I get the same error using jDownloads Version 3.2.27 and Joomla 3.3. Was the problem fixed?

[Arno]

What for 'sortorder' settings have you in your jD menu item?

[bustergut]

Yes recent date order.

[Arno]

Okay i have found a bug and will publish tonight here a fix.
You should use until this the sort order settings from the jD configuration in the menu.

[Arno]

- please install at first the newest version 3.2.28 (use Joomla update function in backend).
- copy afterwards the unzipped file below to: joomla_root/components/com_jdownloads/models/
Try it then again (with the same settings as in your first message) and post here your result.

[gelöscht durch Administrator]