0 Members and 1 Guest are viewing this topic.

Category view recent order false SQL
« 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.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.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 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,l.title AS license_title, l.url AS license_url, l.description AS license_text, l.id 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, parent.id as parent_id, parent.alias as parent_alias,menuf.id AS menuf_itemid,menuc.id AS menuc_cat_itemid,c.published,
  CASE WHEN badcats.id 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 c.id = a.cat_id

  LEFT JOIN sp6qi_users AS ua
  ON ua.id = a.created_id

  LEFT JOIN sp6qi_users AS uam
  ON uam.id = a.modified_id

  LEFT JOIN sp6qi_users AS u
  on u.id = a.created_id

  LEFT JOIN sp6qi_users AS u2
  on u2.id = a.modified_id

  LEFT JOIN sp6qi_jdownloads_licenses AS l
  on l.id = a.license

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

  LEFT JOIN sp6qi_jdownloads_categories as parent
  ON parent.id = c.parent_id

  LEFT JOIN (SELECT id, link, access, published
  from sp6qi_menu
  GROUP BY link) 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,2,3,6)

  LEFT JOIN (SELECT id, link, access, published
  from sp6qi_menu
  GROUP BY link) AS menuc
  on menuc.link 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)

  OUTER JOIN (SELECT cat.id as id
  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 cat.id ) AS badcats
  ON badcats.id = c.id

  WHERE a.access IN (1,1,2,3,6)
  AND c.access IN (1,1,2,3,6)
  CASE WHEN badcats.id 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
« Last Edit: 20.09.2018 23:04:25 by AlainR »
Re: Category view recent order false SQL
« Reply #1 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.
Re: Category view recent order false SQL
« Reply #2 on: 24.09.2018 11:18:35 »
Okay. Sorry for the delay. I will check this.
Best Regards / Gruß
Please make a Donation for jDownloads and/or write a review on the Joomla! Extensions directory!