Ugly Bookshelf and Knex.raw example

01.coffee

# 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

02.coffee

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