I would like to get other user_id
s from the same table that match the event_id
s. I've tried with a sub-query and a this.on
function in a leftJoin and outerLeftJoin. Can't get around the 'not unique table/alias' error with the code below.
knex('user_2_event')
.select(
'event.*',
'user_2_event.user_id as main_user_id'
)
.where('user_2_event.user_id',17)
.join('event', 'event.event_id', 'user_2_event.event_id')
.leftOuterJoin('user_2_event', function(){
this.on('user_2_event.event_id', '=', 'event.event_id')
})
or this instead of the above leftOuterJoin, which produces an 'error with my syntax'.
.join(
knex('user_2_event')
.select('user_2_event.user_id as other_user')
.where('user_2_event.event', '=','event.event_id')
)
After searching in various ways for guidance on getting at data in separate ways on the same table (however one expresses that in technical terms), it was suggested to me to use aliases on the same table. Voila, quick and easy without worrying about join functions and sub-queries.
knex('user_2_event as e1')
.select(
'event.*',
'e1.user_id as user_id',
'e2.user_id as other_id'
)
.where('e1.user_id',17)
.join('event', 'event.event_id', 'e1.event_id')
.leftJoin('user_2_event as e2', 'e2.event_id', 'event.event_id')