Order of Datbasejoin options (labels)

[Bauer]

Is there a reason for sorting databasejoin option labels alphabetically before presenting them???

I have numerous database join elements and they are all being sorted alphabetically.

While I can understand why this would be a welcomed feature - IMO, the individual labels in any databasejoin group should not be sorted alphabetically by default - but rather as an option.

For example - I have intentionally added labels to records in a table in the order (by id) that I want them to be listed - in either a databasejoin dropdown or checkbox group - yet they are being resorted and displayed alphabetically (by label) in the databasejoin element.

 

[Jaanus]

You can use where/order by field in dbjoin element admin form. Writing there
order by {thistable}.your_preferred_element
will do the work.

 

[Bauer]

But I don't want ANY order - there is no alphabetical or numeric logic to the way I want/need the items ordered.

The only solution as is (and using your suggestion) would be to add a special field to the table just for the list order (which is really the same as the id). But why should I have to add another field when all that needs to be done is to stop the Fabrik databasejoin plugin from "automatically" re-sorting the labels alphabetically to begin with?

I have already done that by simply remarking out the line of code that does that. (line 1937 of databasejoin.php).

But my point is questioning why that would/should always be done "automatically"??? IMO, as a simple solution to this issue, there should be another parameter in the Data tab of the databasejoin plugin configuration... a simple Yes/No prompt. For example, a prompt that reads... "Sort labels alphabetically?" Then, unless the user selects "Yes" for that parameter, don't mess with ordering the databasejoin query list (on line 1937 of databasejoin.php).

SOMETIMES people (or businesses) want items ordered a certain way for a reason. This is adding unnecessary work to overcome something that really shouldn't be happening to begin with.

 

[troester]

to overcome something that really shouldn't be happening to begin with

Without an "order by" you can't rely on any order (or non-order), it depends on your database machine.

If you don't want to have a random oder (in principal) there has to be an "order by".

So if you don't want the default "order by label" use the where/order by field to have your "items ordered a certain way for a reason"

 

[Bauer]

I have been working with databases since 1982. I understand what the "order by" clause does. ORDER BY - is applied to the result set after the rows are selected.

I always assumed that WITHOUT using the "order by" clause in the statement I can "rely on" the order to be the order the records were added to the table. Which is exactly what I want and need. And with the primary id key used as the key value, the Fabrik plugin works as expected and associates the correct value to the Fabrik list.

I'm now realizing the rules have changed since my heydays of dBase, FoxPro, and Access - but I have yet to find any definite answer. Most threads I am reading online suggest that for mySQL the default sort is insert order for MyISAM, and primary key ascending for InnoDB. But I don't think that's a guarantee, just how it's known to work.

These database join elements I am talking about are nothing complicated - they are simple tables with one or two fields to hold the titles (and description) used to identify a class or or type found in certain records. There is anywhere from 4 to a few dozen records/rows, at most, in each of these tables I am using for these particular databasejoins. There are no further joins or linked tables.

I like using databasejoin in cases like this for 2 reasons - 1. It stops me from having to manually recreate a dropdown select element containing the lists of those classes or types (and risk typos) in each form that uses the dropdown lists or checkboxes. 2. By providing a frontend edit to these Fabrik lists being used as databasejoins, it allows the client to select and edit her own "verbiage" for the labels which will then be used throughout the project in any form where these dropdown select or checkbox elements are needed/used. In these cases, these labels are intended to be listed by order of importance - not any alphanumeric "order".

I suppose the best thing to do - without ruffling any feathers or asking anyone to "re-invent the wheel" - would be for me to add a "listorder" field to those lists/tables to be used as a sorting "order by" in the database join lists. This would also allow the client, should she need to add to the list in the future, to insert new rows wherever she wants them to be - i.e. she is responsible for setting the order of display.

 

[rob]

ORDER BY - is applied to the result set after the rows are selected.

<sidenote of little relevance>Well its all inside the one query so from our point of view the query is a single entity.
Internally within mySQL I'm pretty sure that order by is performed before rows are selected to allow for use of any indexes that are applied on the order by column, I think that's the case as you can't use column alias's within the order part of the stateement.
On the other hand HAVING is performed after the select, so aliases can be used but then any index applied on that column doesn't help in speeding up the query.</sidenote end>

suppose the best thing to do - without ruffling any feathers or asking anyone to "re-invent the wheel" - would be for me to add a "listorder" field to those lists/tables to be used as a sorting "order by" in the database join lists. This would also allow the client, should she need to add to the list in the future, to insert new rows wherever she wants them to be - i.e. she is responsible for setting the order of display.

Yup that sounds like the best way forward.