Skip to content

Instantly share code, notes, and snippets.

@w33ble
Created May 28, 2014 23:06
Show Gist options
  • Save w33ble/a225947db6265d3a78bc to your computer and use it in GitHub Desktop.
Save w33ble/a225947db6265d3a78bc to your computer and use it in GitHub Desktop.
Ugly Bookshelf and Knex.raw example
# bookshelf code, to compliment the knex code above
exports.PG = Bookshelf.PG = Bookshelf.initialize
client: 'pg'
connection: config.get('/postgres')
# property photo model
exports.PropertyPhoto = PropertyPhoto = Bookshelf.PG.Model.extend
tableName: 'property_photos'
# property model
exports.Property = Property = Bookshelf.PG.Model.extend
tableName: 'properties'
property_photos: ->
this.hasMany PropertyPhoto
exports.run = ->
# fetch all properties and their associated photos
# photos will be attached as an array of objects with the key `property_photos`
query = """
select *, (
select json_agg(photos)
from (
select * from property_photos
where property_id = properties.id
order by rank asc
) AS photos
) as property_photos
from properties
"""
Bookshelf.PG.knex.raw query
example = require '01'
example.run.then (results) ->
console.log results.length # 1833 records, as expected
rawProperty = results[250]
console.log rawProperty.property_photos.length # 7 photos, as an array of objects
# make a collection that uses our Property model
Properties = example.PG.collection.extend
model: example.Property
# now let's make these results a bookshelf collection
Properties.forge(results)
console.log Properties.length # 1833, just like above
property = Properties.at(250) # reference the model of the same property as above
# let's see if the model has the related property_photos association set automatically
console.log property.related('property_photos').length # 0, :(
# well, at least the data is there....
console.log property.get('property_photos') # 7 photos, as an array of objects
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment