SQL Query in Sequelize getter method

I'm using the Postgres extension 'earthdistance' for lat/long distance calculation.

I'm also using Sequelize to access the database and I want to define a getter method for calculation 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 using sequelize.query(), but I want to keep all the model queries part of the model.

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


-------------Problems Reply------------

Your best bet is probably to wrap the query in a stored procedure and pass in the arguments you want to use in the where clause. As stored procedures are compiled, this will perform better than a Dynamic SQL where you generate the WHERE clause on the fly.

Add whatever parameters and types to your stored proc as you need, and the result will look something like this:

CREATE FUNCTION GetEarthDistance (v_Foo bigint) RETURNS type AS $$
v_Name varchar(256);
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;
$$ LANGUAGE 'plpgsql';

Category:node.js Views:8 Time:2014-06-28

Related post

  • retrieve sql query from cakephp finder method 2010-12-14

    I'm creating a behavior that log to a table the sql query executed by a particular Model in a controller. Looking for a method to return me the sql query executed for a particular finder method (like $this->MyModel->find('all') ) I found on the

  • SQL query for mutual visited places 2011-04-27

    I'm working on a project for my University with Rails 3/PostgreSQL, where we have Users, Activities and Venues. An user has many activities, and a venue has many activities. An activity belongs to an user and to a venue and has therefore an user_id a

  • Use Replace Method In Linq-Sql Query 2010-07-10

    I used RegEx to remove HTML TAGS within LINQ-SQL query but the following error has thrown: Method 'System.String Replace(System.String, System.String, System.String)' is not supported for execution as SQL. Help helpDBSession = new Help(); IEnumerable

  • Custom Method in LINQ to SQL query 2010-09-03

    Is it possible to use custom method In query for example: var result = from u in context.MyTable where MyMethod(u) == 10 select u; --------------Solutions------------- As Pranay explains, you cannot have a custom (C#) method as part of the LINQ to SQ

  • Problem calling string manipulation method within linq-to-sql query 2011-01-25

    I'm having a frustrating issue trying to use LINQ to call a string manipulation method. I've done lots of searching now and have tried various method to get the line noted as 'FAILS' below to work. It currently throws an exception. Some things I've t

  • check out my sql query filter method,is this safe? 2011-09-03

    String searchSQLFilter(String keyword){ for(String filter:new String[]{"|","&","*","%",";","-","+",",","<",">"}){ keyword=keyword.replaceAll("\\Q"+filter+"\\E", ""); } keyword=keyword.replaceAll("'","\\\\'"); return keyword; } sql query: se

  • saving Image using sql query method 2012-03-16

    I am using following code: Note that i HAVE TO SEND SQL Query so using this procedure. currentReceipt.image is a byte[] String updateQuery = "INSERT INTO MAReceipts(userId, transactionId, transactionType, receiptIndex, referenceNo, image, smallThumb,

  • Enterprise Architect - The method SQLQuery of the repository object doesn't work like a native SQL query 2014-04-22

    I am using the enterprise architect via the API of SPARX in C#. In the repository class exists a method called "SQLQuery". I call the method with the following SQL parameter select obj1.name from t_object obj1 where obj1.stereotype = 'XYZ' and obj1.n

  • Proving SQL query equivalency 2008-09-11

    How would you go about proving that two queries are functionally equivalent, eg they will always both return the same result set. As I had a specific query in mind when I was doing this, I ended up doing as @dougman suggested, over about 10% of rows

Copyright (C) dskims.com, All Rights Reserved.

processed in 0.194 (s). 11 q(s)