CODEWITHSUNDEEP
bookshelf.jsknex.js
rgareth
rgareth

Reputation: 3526

Bookshelf.js relation for loosely related data

I have a hotel table, and an amenity table. The only way they are "related" is via location, using PostGIS for PostgreSQL. In SQL I use a query like this to find the 5 nearest amenities to a hotel:

SELECT amenity.name, amenity.brand, ST_Distance_Sphere(hotel.geom, amenity.geom) FROM amenity, hotel WHERE slug='city-plaza' ORDER BY ST_Distance(hotel.geom, amenity.geom) LIMIT 5;

Is there a way I could add a "nearestAmenities" field to the Hotel model using Bookshelf, which would be a collection of Amenity?

Upvotes: 1

Views: 1293

Answers (2)

rgareth
rgareth

Reputation: 3526

The solution I found to this was to use SQL views in addition to Bookshelf's belongsToMany feature. Here are the steps using the above table names, in case anyone else stumbles upon this question:

1) Add a view 'hotel_amenity': 

CREATE OR REPLACE VIEW hotel_amenity AS
SELECT h.id         hotel_id,
       a.id         amenity_id,
       ceil(ST_Distance(ST_Transform(h.geom, 3414), ST_Transform(a.geom, 3414))/100)/10 distance_km
  FROM amenity a
  CROSS JOIN hotel h;

2) Add a Amenity model with Bookshelf:

var Amenity = exports.Amenity = Bookshelf.Model.extend({
  tableName: 'amenity'
});

3) Add a Hotel model with Bookshelf, and reference Amenity using belongsToMany:

var Hotel = exports.Hotel = Bookshelf.Model.extend({
  tableName: 'hotel',
  nearbyAmenities: function() {
    return this.belongsToMany(Amenity, 'hotel_amenity')
    .withPivot('distance_km')
    .query('orderBy', 'distance_km', 'asc')
    .query('limit', 10);
  }
});

And don't forget to use withRelated when you fetch Hotel.

Upvotes: 1

Matt
Matt

Reputation: 7212

Have you tried using the knex.raw functionality?

knex.raw('SELECT amenity.name, amenity.brand, ... LIMIT 5').then(function(resp) {
  ...
});

Upvotes: 1

Related Questions