I want to use an alias for the order by clause in ...

# exposed

[Jeff]

I want to use an alias for the order by clause in Exposed Kotlin like so:

6371 * ACOS(COS(RADIANS(:latitude)) * COS(RADIANS(`lat`)) * COS(RADIANS(:longitude) - RADIANS(`lng`)) + SIN(RADIANS(:latitude)) * SIN(RADIANS(`lat`))) AS `distance`

Anyone with an idea on how to implement this?

[spand]

Use the alias function

[Endre Deak]

alias.aliasOnlyExpression could be useful

[Jeff]

How exactly @spand @Endre Deak

[Jeff]

Anyone? Not sure how to use the alias function in this specific usecase.

[Endre Deak]

what’s your full query

[Endre Deak]

?

[Jeff]

To simplify, I'm getting a list of offices. I want to order them by location

[Jeff]

Something like:

Offices
    .leftJoin(Doctors, { Offices.doctor }, { Doctors.id })
    .slice(
       ...,
       ...,
).selectAll().limit(20).orderBy(...)

[Jeff]

I know I can have the calculations in Kotlin like so but I still don't understand how to use the alias function to order the query.

val distance = 6371 *
        Math.acos(
            Math.cos(Math.toRadians(myLatitude)) *
                    Math.cos(Math.toRadians(officeLatitude)) *
                    Math.cos(
                        Math.toRadians(officeLongitude) -
                                Math.toRadians(myLongitude)
                    ) +
                    Math.sin(Math.toRadians(myLatitude)) *
                    Math.sin(Math.toRadians(officeLatitude))
        )

[Jeff]

How do you think i can use alias.aliasOnlyExpression @Endre Deak?

[spand]

If you really want the database to do that then you need to convert the math expression to an exposed expression like you would in the .select { } block

[Jeff]

I would really need an example

[tapac]

As there is no such functions like acos/cos/etc in Exposed (and it would be great if you'll contribute them:) first of all you need to declare such functions:

fun CustomDoubleFunction(
    functionName: String,
    vararg params: Expression<*>
): CustomFunction<BigDecimal> = CustomFunction(functionName, DoubleColumnType(), *params)

fun Radiants(exp: Expression<BigDecimal>) = CustomDoubleFunction("RADIANTS", exp)

after that when you'll have all needed functions you can write:

val distance = Acos(Cos(Radiants(MyTable.lattitue))) * ...

[Endre Deak]

👆

A bunch of those functions would do that.