SQL query in Sequelize getter method

Question

I am using the Postgres extension 'earthdistance' to calculate lat / long.

I also use Sequelize to access the database, and I want to define a getter method for calculating and sorting by distance from a set of coordinates.

The following query works fine:

SELECT name, 
       earth_distance(ll_to_earth( 51.5241182, -0.0758046 ), 
       ll_to_earth(latitude, longitude)) as distance_from_current_location 
FROM "Branches" 
ORDER BY distance_from_current_location ASC;

And I can use it with the sequelize.query () method, but I want all model queries to be part of the model.

How can I specify WHERE conditions from the getter method in the model definition?

Thank!

Answer 1

, , , where. , Dynamic SQL, WHERE .

proc, , :

CREATE FUNCTION GetEarthDistance (v_Foo bigint) RETURNS type AS $$
DECLARE 
   v_Name varchar(256);
BEGIN
SELECT name INTO v_Name, 
   earth_distance(ll_to_earth( 51.5241182, -0.0758046 ), 
   ll_to_earth(latitude, longitude)) as distance_from_current_location 
FROM Branches 
WHERE somecol > v_foo
ORDER BY distance_from_current_location ASC;

RETURN v_Name; 
END;
$$ LANGUAGE 'plpgsql';