• Hello Fabrik Community

    Fabrik is now in the hands of the development team that brought you Fabrik for Joomla 4. We have recently transitioned the Fabrik site over to a new server and are busy trying to clean it up. We have upgraded the site to Joomla 4 and are running the latest version of Fabrik 4. We have also upgraded the Xenforo forum software to the latest version. Many of the widgets you might have been used to on the forum are no longer operational, many abandoned by the developers. We hope to bring back some of the important ones as we have time.

    Exciting times to be sure.

    The Fabrik 4.0 Official release is now available. In addition, the Fabrik codebase is now available in a public repository. See the notices about these in the announcements section

    We wish to shout out a very big Thank You to all of you who have made donations. They have really helped. But we can always use more...wink..wink..

    Also a big Thank You to those of you who have been assisting others in the forum. This takes a very big burden off of us as we work on bugs, the website and the future of Fabrik.

Order by in dbjoin not working

Status
Not open for further replies.

bespokeappstt

Hubstaff freelancer
I have a dbjoin with a nested query. I want the multi-select drop down to order the numbers. The parent list element is rad_serialassets___serial_number and the list with the dbjoin is rad_rentals___serial. First I tried:
Code:
WHERE {thistable}.id IN (SELECT a.id FROM rad_serialassets AS a LEFT JOIN rad_rentals_repeat_serial AS b ON a.id = b.serial WHERE b.parent_id = "{rad_rentals___id_raw}") OR {thistable}.available = 1 order by "{rad_rentals___serial_raw}" DESC
The I tried
Code:
WHERE {thistable}.id IN (SELECT a.id FROM rad_serialassets AS a LEFT JOIN rad_rentals_repeat_serial AS b ON a.id = b.serial WHERE b.parent_id = "{rad_rentals___id_raw}") OR {thistable}.available = 1 order by "{rad_serialassets___serial_number_raw}" DESC
None of them work. Using {thistable}.serial throwed an error and the page didin't even load. How can I order those numbers in the drop down?
upload_2021-10-31_19-28-39.png
 
1. oder by inside an IN subquery won't do anything, it doesn't matter if you have IN (1,2,3) or IN (3,2,1)
2. order by "{rad_rentals___serial_raw}": what should this do, it will become something like order by "5", you'll need a DB column name, no placeholder

Enable and use fabrikdebug to see which queries are used in the end.
 
I've decided to use the List settings and set the 'Order by' to use the 'Serial_number'. Though not perfect this some sorting taking place. The serial are first grouped by the first few digits. From there it's sorted in ascending order:

upload_2021-11-8_14-44-42.png
I also went into phpmyadmin and changed the order of the columns. This was recommended on Stackexchnage. Here is the url: https://dba.stackexchange.com/questions/140941/is-it-possible-to-reorder-tables-in-phpmyadmin The first column gets sorted in ascending order:
upload_2021-11-8_14-46-53.png

Now the dbjoin looks like this:
upload_2021-11-8_14-47-30.png

The serials are being grouped by the first few digits and then sorted. I can live with that.
 

Attachments

  • upload_2021-11-8_13-46-56.png
    upload_2021-11-8_13-46-56.png
    8.2 KB · Views: 104
Status
Not open for further replies.
We are in need of some funding.
More details.

Thank you.

Members online

Back
Top