Magento2 add custom values in collection->setOrder

Question

I need to add some custom values in setOrder() of a collection, but it is not working.

$collection->setOrder("((lat - ".$lat.")*(lat - ".$lat.")) + ((lng - ".$lng.")*(lng - ".$lng."))", 'ASC');

The final part of my collection query like this:

ORDER BY ((lat - -34.9226513)*(lat - -34.9226513)) + ((lng - 138.6094486)*(lng - 138.6094486)) ASC

Please help anybody. Thanks in advance.

Answer 1

I'm not sure I've really understood what you want to do, but in order to get a correct SQL statement, you have to check if your variables are positive or negative. Or the simplest way would be to use the pgh method abs() which always return the positive value of a number.

Something like this should do the trick and allways give you correct SQL statement:

$collection->setOrder("((lat - ".abs($lat).")*(lat - ".abs($lat).")) + ((lng - ".abs($lng).")*(lng - ".abs($lng)."))", 'ASC');