# 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