How to execute a raw request in Bookshelf.js

Question

I want to achieve this

SELECT id, ( 3959 * acos( cos( radians(37) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(-122) ) + sin( radians(37) ) * sin( radians( lat ) ) ) ) AS distance FROM markers HAVING distance < 25 ORDER BY distance LIMIT 0 , 20; 

from https://developers.google.com/maps/articles/phpsqlsearch_v3?hl=fr#createtable

How can I make this request with a bookshelf.

I have it now:

 var raw = '( 3959 * acos( cos( radians(37) ) * cos( radians( '+ req.params.lat + ' ) ) * cos( radians( '+req.params.lng+' ) - radians(-122) ) + sin( radians(37) ) * sin( radians( '+req.body.lng+' ) ) ) ) AS distance'; new Places().query(function(qb) { qb.where('lat',req.params.lat); qb.where('lng',req.params.lng); qb.column(raw); qb.having('distance', '>', 25); }).fetch({ debug: false }).then(function(collection) { console.log(collection); if (collection === undefined) { // no such result res.json(404,{error: "No Places found."}); } else { // found, list json res.json(200, collection); } }); 

Answer 1

His.

 qb.column(qb.knex.raw(raw)); 

Answer 2

I found this to work .. no qb. Instance / reference required:

 var Bookshelf = require('bookshelf').mysqlAuth; var rawSql = 'SELECT .. <etc>'; Bookshelf.knex.raw(rawSql).then(..); 

Answer 3

I tried something like this that worked fine

 return **Location**.query(qb => { return ***query*** }) .fetchAll() .then(data => data.toJSON()); 

WITH:

• Location is a model (I do this inside Strapi services)

• The request may be any unhandled request.