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:
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
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
Hi Alex,
QuoteLooks like the query has "DESC" twice in the end.
Yes, seems to be a bug. Will check this later today.
I get the same error using jDownloads Version 3.2.27 and Joomla 3.3. Was the problem fixed?
What for 'sortorder' settings have you in your jD menu item?
Yes recent date order.
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.
- 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]