Since version 9.4.1, my export templates with file downloads no longer work
The error displayed is "Error: SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'id' in ORDER BY is ambiguous"
After running various tests, I noticed that the problem only occurs when ascending or descending order is selected
If I do not select any sorting, I do not get any errors, and the export works
I am attaching the log file created by one of these templates
Thank's for your help
Hello,
Since version 9.4.1, my export templates with file downloads no longer work
I guess you mean latest 9.4.1 version? There was no change in code for custom table export in RO CSVI 9.4.1 version. The error you see is from database query not knowing the table id field belongs to.
May I ask why you are using custom table export to export Joomla user details? RO CSVI Supports Joomla user export template which also supports User custom fields. So why not use this export instead of Custom table export?
Kind regards,
Tharuna
=========================
If you use our extensions, please post a rating and a review at the Joomla! Extension Directory
Hello,
I guess you mean latest 9.4.1 version? There was no change in code for custom table export in RO CSVI 9.4.1 version. The error you see is from database query not knowing the table id field belongs to.
I meant if you are using RO CSVI 9.4.1 version as debug log shows you are on RO CSVI 9.3.0 version
Kind regards,
Tharuna
=========================
If you use our extensions, please post a rating and a review at the Joomla! Extension Directory
Hello,
I have noticed that as soon as I add a display order, the export no longer works, even if I leave the order ascending.
I had already encountered this problem during the previous update, but as it is a site that I migrated from Joomla 3.5 to 5 in recent weeks, I thought the problem was due to something else. And as I was using Easy Profile on the old site, which is no longer compatible, I had to recreate a new export template for the new fields anyway.
But it is true that the template already created on the old site to export Hikashop orders with user names no longer worked if I changed the order, which is still the case, and with the same error saying that the table selected for sorting cannot be found.
Templates with linked tables have this problem as soon as an order is added to a field, with a message saying that the table selected for sorting cannot be found.
What surprises me is that this template created to export users worked before the update with a descending order.
In the old site, I didn't use the template to export Joomla users because I couldn't do it with Easy Profile, and for this new template I took the same approach to create it, but it's true that I can solve this sorting problem by recreating a template from the Joomla user export example template.
As for Hikashop orders, I believe that when I created this template a few years ago, the Hikashop example model did not allow me to export the tables I wanted, but I can do so now that I am using custom user fields and Easy Profile.
I don't understand your last message about RO CSVI 9.3.0?
I am indeed using version 9.4.1, but the problem was already present with version 9.3.0.
Hi,
So I checked the debug log and see this is a custom SQL export for a user export. The problem is this line:
ORDER BY ``.`id` DESC,`a1agl_jsn_users`.`id` DESC
Notice that the "id" field has no table name specified. The settings you have set on the Fields tab are not used when you are writing your own SQL query. So you need to update the SQL query. If you need help with that, feel free to post your SQL query and we can check.
The order by should be replaced with a1agl_users.id .
Let us know how that goes.
Kind regards,
RolandD
=========================
If you use our extensions, please post a rating and a review at the Joomla! Extension Directory
Thank you for your reply, but the problem is that this is not a manual request. I joined the tables with RO CSVI and also set the display order with RO CSVI.
Everything works this way, and it allows you to join different tables; it's just the output order that poses a problem. It's as if RO CSVI isn't writing this query correctly.
Hi,
As I was reading your reply I realized I had mistaken the export type. Yes, I see now as well with your screenshot what is going on. I had also tested this export and it works fine for me. Your screenshot shows what is missing, namely there is no table alias filled in. That is why RO CSVI isn't writing the query correctly because it is doing exactly as you asked, not to put in an alias :)
So fill in the table alias fields and you should be good to go. The alias is needed as it is possible to join the same table more than once in an SQL query.
Once you have entered the aliases, the query should work as expected.
Let us know how it goes.
Kind regards,
RolandD
=========================
If you use our extensions, please post a rating and a review at the Joomla! Extension Directory
Thank you for your reply. Yes, indeed, aliases should be added. I found this information while doing some research.
Unfortunately, it doesn't work. If I add aliases, even just one, it crashes with the same error, even without specifying a sort order.
However, if I don't add any aliases and don't specify a sort order, it works.
I don't want to take up your time, but if you want to try, I've specified the account access details in the "Site Details" tab.
Hello,
Thank you for the details. I was able to find the cause for the error and fixed it with attached patch file. I have also loaded patch file on your site and now there is no error in export even with sort by field added. Please test and let me know.
Kind regards,
Tharuna
=========================
If you use our extensions, please post a rating and a review at the Joomla! Extension Directory
Great, thank you. With the patch, I no longer have any problems ;)
Log in to reply to this topic.