Function or statement for Order by clauses

[LazyRichard]

I'm not sure this is standard SQL or not. but this is useful when using MySQL.

SELECT * 
FROM mytable
WHERE id IN (3,2,1,4)
 ORDER BY FIELD(id,3,2,1,4)

or other database like MSSQL

SELECT *
FROM mytable
WHERE id IN (3,2,1,4)
ORDER BY
    CASE id
        WHEN 3 THEN 1
        WHEN 2 THEN 2
        WHEN 1 THEN 3
        WHEN 4 THEN 4
    END

the above cases supported?

[jeffgbutler]

The first one you can accomplish by writing your own implementation of SortSpecification.

I will need to make a change to support the second, but it shouldn't be too difficult.

[LazyRichard]

Thanks for your response.

I've been thinking about the first case, but since I'm still not very familiar with this library, I could use some guidance.

The SortSpecification definition is as follows:

public interface SortSpecification {
    SortSpecification descending();

    String orderByName();

    boolean isDescending();
}

So, ultimately, it seems like the orderByName function should deliver the parameters I need.

Is it correct to assume that orderByName should return FIELD (id, 1,2,3)?

The reason being, to safely handle the values using MyBatis without directly injecting them into the query, and instead, storing them in a parameter map similar to FragmentAndParameters or WhereClauseProvider.

This way, MyBatis can handle them securely and use type handlers appropriately. However, I'm not entirely sure how to accomplish this.

Could you provide some guidance on how to achieve this?

[jeffgbutler]

You are correct that orderByName should return FIELD (id, 1,2,3). But, as you say, that does not give you access to the parameter map.

I will fix this in the next release and deprecate the current method. I believe the new method signature will be FragmentAndParameters renderForOrderBy(RenderingContext)