Older Versions => jDownloads 3.2 => Bugs => Topic started by: AlainR on 20.09.2018 23:01:15

Title: Category view recent order false SQL
Post by: AlainR on 20.09.2018 23:01:15
I want to order the recent file first and no files appear. When I change order (in menu item settings), all files appear.
In debug mode, I find the end of the SQL is wrong with two DESC :
Code: [Select]
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.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.featured, 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 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, AS creator, AS modifier,l.title AS license_title, l.url AS license_url, l.description AS license_text, as lid,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, as parent_id, parent.alias as parent_alias, AS menuf_itemid, AS menuc_cat_itemid,c.published,
  CASE WHEN is null THEN c.published ELSE 0 END AS parents_published

  FROM sp6qi_jdownloads_files AS a

  LEFT JOIN sp6qi_jdownloads_files AS aa
  on aa.file_id = a.other_file_id

  LEFT JOIN sp6qi_jdownloads_categories AS c
  ON = a.cat_id

  LEFT JOIN sp6qi_users AS ua
  ON = a.created_id

  LEFT JOIN sp6qi_users AS uam
  ON = a.modified_id

  LEFT JOIN sp6qi_users AS u
  on = a.created_id

  LEFT JOIN sp6qi_users AS u2
  on = a.modified_id

  LEFT JOIN sp6qi_jdownloads_licenses AS l
  on = a.license

  LEFT JOIN sp6qi_jdownloads_ratings AS r
  on r.file_id = a.file_id

  LEFT JOIN sp6qi_jdownloads_categories as parent
  ON = c.parent_id

  LEFT JOIN (SELECT id, link, access, published
  from sp6qi_menu
  GROUP BY link) AS menuf
  on LIKE CONCAT('index.php?option=com_jdownloads&view=download&id=',a.file_id)
  AND menuf.published = 1
  AND menuf.access IN (1,1,2,3,6)

  LEFT JOIN (SELECT id, link, access, published
  from sp6qi_menu
  GROUP BY link) AS menuc
  on LIKE CONCAT('index.php?option=com_jdownloads&view=category&catid=',a.cat_id)
  AND menuc.published = 1
  AND menuc.access IN (1,1,2,3,6)

  FROM sp6qi_jdownloads_categories AS cat JOIN sp6qi_jdownloads_categories AS parent
  ON cat.lft BETWEEN parent.lft
  AND parent.rgt 
  AND parent.published != 1
  GROUP BY ) AS badcats
  ON =

  WHERE a.access IN (1,1,2,3,6)
  AND c.access IN (1,1,2,3,6)
  CASE WHEN is null THEN a.published ELSE 0 END IN (0,1,2)
  AND a.cat_id = 19
  AND (a.publish_from = '0000-00-00 00:00:00' OR a.publish_from <= '2018-09-20 20:47:24')
  AND (a.publish_to = '0000-00-00 00:00:00' OR a.publish_to >= '2018-09-20 20:47:24')

  ORDER BY  c.lft,  a.date_added DESC   desc
  LIMIT 50

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'desc LIMIT 50' at line 16

Many thanks in advance for your help.
Best regards.

Joomla! 3.8.12
jDownloads Version 3.2.62
PHP    5.6.38
MySQL  5.5.5-10.0.34-MariaDB
Title: Re: Category view recent order false SQL
Post by: AlainR on 20.09.2018 23:23:42
OK, I remove DESC on line 79 from components/com_jdownloads/helpers/query.php file and it works fine.
Title: Re: Category view recent order false SQL
Post by: Arno on 24.09.2018 11:18:35
Okay. Sorry for the delay. I will check this.