Closed nlassaux closed 6 months ago
What is the function here that doesn't meet the syntax function_name(param1, param2)
?
CustomFunction
seems to cover the two I see in your example
In that case it's the operator A <-> B
that'd need to be computed. The functions before are all right, sorry :)
For that, I think you can get away with a custom ArithmeticExpression from pypika.terms
. It'd be similar to how the primary operations are defined
left = ... # custom functions right = ... # point column order_by = ArithmeticExpression("<->", left, right)
# define query
EDIT:
ArithmeticExpression is used with enum which has a value attribute. A simple wrapper class can be used to have same attribute
from pypika import CustomFunction, Field, Query
from pypika.terms import ArithmeticExpression
class NewOperation:
value: str = "<->"
ST_SetSRID = CustomFunction("ST_SetSRID", ["col", "value"])
ST_MakePoint = CustomFunction("ST_MakePoint", ["left", "right"])
left = ST_SetSRID(ST_MakePoint(Field("left"), Field("right")), 4326)
right = Field("point")
orderby = ArithmeticExpression(NewOperation, left, right)
query = Query.from_("house").select("id").orderby(orderby)
That's exactly what's needed, thanks! It would be great if it was in the doc, so I opened a PR to add that: #779
Issue solved!
I'm using distance operators in the ORDER BY, because those use geographic indexes better than their function equivalents:
Unfortunately, I don't seem to find a correct way to define that custom distance. It looks like defining a custom function assumes a prefix expression notation
function_name(param1, param2)
, when I'd like a postfix version.Has anyone covered a similar case? Thanks!