Trouble with JOINS

[jimudall]

This is not really an issue, but rather a plea for help :).

I'm try to use OpenRecord to do a query on some tables that are joined together. The select I'm trying to execute is as follows: select distinct a.* from event_shift a join event_shift_role b on b.event_shift_id = a.event_shift_id join event_shift_role_user_account c on c.event_shift_role_id = b.event_shift_role_id join event_user_account d on d.event_user_account_id = c.event_user_account_id join event e on e.event_id = d.event_id join user_account f on f.user_account_id = d.user_account_id "where e.is_closed = false and e.is_live = true

There are a few tables - all joined by foreign keys. I believe I'm struggling with the relation definitions.

I'm using the 'class' syntax for definition of my models - which have the static 'definition' function.

I'm trying to execute the query thru my 'event_shift' OpenRecord model. So I have added the following hasMany() definition: this.hasMany('event_shift_role', {to: 'event_shift_id', from: 'event_shift_id'})

Which gets the first join. However, I'm stumped on defining the relations within that same model for the other chained relations. Thru fumbling about and googling, I've found some sort of construct that looks tempting - though I don't see how it is documented. Specifically this.hasMany('event_shift_role', {to: 'event_shift_id', from: 'event_shift_id'}) .hasMany('event_shift_role_user_account', {through: 'event_shift_role', to: 'event_shift_role_id', from: 'event_shift_role_id'})

But in all honestly, I'm simply flailing about here.

Could I ask you for assistance on how I can solve this?

P.S. As an aside, I tried to not use the relationships, but rather calling join() with a string (e.g. JOIN event_shift_role ON event_shift_role.event_shift_id = event_shift.event_shift_id') and chaining them together. However, doing so fails to generate the appropriate SQL. FYI I'm using postgres as my DB backend

P.P.S How can i add the distinct qualifier on the query? :)

[PhilWaldmann]

Hi Jim,

sorry for the delayed response. I highly recommend to use VIEWS. It's way more efficient than composing the sql query for every request.

However, if you like to use openrecord you should create a model per table (with the same name). something like:

//event_shift.js
module.exports = function EventShift(){
  this.hasMany('event_shift_roles', {from: 'event_shift_id', to: 'event_shift_id')
}

//event_shift_role.js
module.exports = function EventShiftRole(){
  this.hasMany('event_shift_role_user_accounts', {from: 'event_shift_role_id', to:'event_shift_role_id'})
  this.belongsTo('...')
}

//event_shift_role_user_account.js
module.exports = function EventShiftRoleUserAccount(){
  this.hasMany('event_user_accounts', {from: 'event_shift_role_id', to: 'event_shift_role_id'})
  this.belongsTo('...')
}

...

btw: if you have a table roles (id, name) and a table users(id, role_id) you don't need to specify the from and to option on relation definitions - openrecord will automatically detect them:

// role.js
module.exports = function Role(){
  this.hasMany('users')
}

// user.js
module.exports = function User(){
  this.belongsTo('role')
}

distinct in currently only supported on custom select() or on aggregate functions. That's another advantage of VIEWS ;)

Please let me know if this solves your problem.

Thanks, Philipp