Filter across relation (joins)

[techniq]

It would be very useful if you could apply a join and filter across the join . For example, with the following resources I want to find all the races at a specific address:

exports.Race = db.store.defineResource({
  name: 'race',
  relations: {
    belongsTo: {
      address: {
        localField: 'address',
        localKey: 'address_id'
      }
    },
  }
});

exports.Address = db.store.defineResource({
  name: 'address',
  relations: {
    hasMany: {
      race: {
        localField: 'races',
        foreignKey: 'address_id'
      }
    }
  }
});

Executing sqlAdapter.findAll(models.Race, { "address.city": "New York" }); would produce:

select * from race
join address ON race.address_id = address.id AND address.city = 'New York'

The equivalent knex query would be:

knex.select('*').from('race').join('address', function() {
  this.on('address.id', '=', 'race.address_id').on('address.city', '=', knex.raw('?', ['New York']))
})

[jmdobry]

What does sqlAdapter.findAll(models.Race, { "address.city": "New York" }); produce right now?

[techniq]

{ method: 'select',
  options: {},
  bindings: [ 'New York' ],
  sql: 'select * from `race` where `address`.`city` = ?' }

  Error: select * from `race` where `address`.`city` = 'New York' - Unknown column 'address.city' in 'where clause'
  ...