[Solved] sorting results (SQL ORDER BY)

I note that several people have been asking how to sort records. Having just had to do it myself I thought I'd publish the details here (for version 1.7).
For standard sorting, you can select from various options under the 'Menu Manager: Edit Menu Item' entry, under the 'Advanced Options' tab. To go further you need to hack the code as follows.
1) Add the name of your custom sorting method to the Advanced Options tab
Locate components/com_joodb/views/catalog/tmpl/default.xml
At about lines 39 and 82 (yes, it's there twice) locate the following code...
<option value="random">RANDOM</option>
...and add a similar line below it, substituting values of your choice:
<option value="mysort">mysort</option>
Once you've saved this, the option will be available in Menu Manager.
2) Add the code to do the sorting
Locate components/com_joodb/models/catalog.php
At about line 76, locate the following code...
if ($orderby=="random") {
$this->setState('orderby',$orderby);
} else
...and change it to the following, substituting your description instead of mysort:
if ($orderby=="random") {
$this->setState('orderby',$orderby);
} else if ($orderby=="mysort") {
$this->setState('orderby',$orderby);
} else
Then, near the foot of the page, locate the following code...
if ($this->getState('orderby')== "random") {
$query .= ' ORDER BY RAND() ';
} else
...and change it to the following, substituting your description instead of mysort, and your field names after ORDER BY:
if ($this->getState('orderby')== "random") {
$query .= ' ORDER BY RAND() ';
} else if ($this->getState('orderby')== "mysort") {
$query .= ' ORDER BY field_name_1, field_name_2, ';
} else
Save and go.

If, like me, you're trying to do a custom sort as descried above, the above solution doesn't work - the JooDatabase code has changed.

Here's a hack using a different approach that works with Version 2.3 (the current version at the time of writing)


1) In phpMyAdmin, create a MySQL View of the table, with the desired sort order
If you've not done this before, read this tutorial first.

When you create the SQL query, you need to concatenate together two or more fields to form a new field that you will sort on. IF you can't do that, this solution won't work.

Your SQL should look something like:

I included `cf_id` to ensure that all values were unique.

Run this query and save it as `view_of_my_table` or similar.


2) In the JooDatabase code, remove the primary field check:
This allows joodb to connect to the MySQL View, instead of a table.

Find the code (currently on line 69) that reads

and replace it with

...as described in this tip .

You'll have to check that this modification is still in place after all future updates of JooDatabase, as the file may get overwritten.


3) Edit the JooDatabase component:
Assuming you have already created this, then in the 'General Options':

1) Change Main table: from my_table to view_of_my_table (the view you created)

2) Change Index (ID): from cf_id to my_view



4) Check the Joomla menu item that's linking to JooDatabase
Under 'Advanced', you want Ordering to be set to Ordering, and Article Order set to ascending or descending.


5) Test...