humm it works for me

select id as value,  username as text from #__users order by text desc

what's your query?

For me it's not working in front-end, where your query is modified by tmpl/sql.php into
select id as value, username as text from #__users order by text desc **having value IN ('...')**
that is not a valid SQL query and should be written as
select id as value, username as text from #__users **having value IN ('...')** order by text desc
to be correct.
It does work in editing/form, where "having value..." is not added. For me it's almost a bug...
what do you think?
thanks

got it now, i didn't check frontend
probably we just need to remove the HAVING clause

Filtering is necessary to display only the field values associated to current item

When making a fix which checks if there is an "ORDER BY" in the SQL, we have to care for case-insensivity of SQL and for people having more than one space in their query between the words, so we should use a regular expression to find that (just some thoughts from scratch, I haven't checked code yet if it already replaces multiple white space by a single one).

Another thing is that there might be a "WINDOW" clause in the query, which would come between a "HAVING" and an "ORDER BY" clause. Not very common but can happen.

For the complete SELECT syntax in MySQL (or MariaDB) and PostgreSQL see here:

• https://dev.mysql.com/doc/refman/8.0/en/select.html
• https://www.postgresql.org/docs/13/sql-select.html

Of course we can manipulate the user's SQL statement, split it and add something in the middle, but the question is do we want that? Maybe it would be cleaner to have a separate parameter for the "ORDERR BY" clause in that field, and the user could enter an optional "ORDER BY" clause in that way.

the separate ORDER BY is a very good idea, currently not being able to have ORDER BY is a serious limitation since we expect to have ordered values in the select menu. In this way the sql construction would be very easy, with a sequence of BASIC SELECT - HAVIN - [ORDER BY]

The question would be if a separate order by would be a new feature. In this case it would have to go into 4.1.

But as far as I understand we have that issue also in J3, right?

yes, now adding ORDER BY breaks the field in J3 already, for me it's a sort of a bug

stion would be if a separate order by would be a new feature. In this case it would have to go into 4.1.

But as far as I understand we have that issue also in J3, right?

I would say thats a bug that can be fixed in 3.10 when there is a good solution to that. But I guess how its done now it needs an bigger regex which could have its own drawbacks, as @richard67 mentiond.

I would propose, but that would be a 4.1+ change, to allow (additional options) so we split the options similiar to the original SQL field: https://docs.joomla.org/SQL_form_field_type so in the code we know which part we have in front of us now and we build the final query ourself. On that specific site you should be able to override that file for this specific usecase.

having separate fields is a good option, can we have it for J4.1?
cheeers

Giuse