.joinRelation in wrong place in generated SQL #180
Description
I'm having an issue with mixing join and joinRelation. The join is being promoted in front of the joinRelation in the resulting SQL, even though joinRelation is listed first in the query. This is a problem, because I am relying on a field from the joinRelation for the join.
Category
.query()
.from('categories as c')
.distinct('c.id', 'c.name')
.joinRelation('videos', { alias: 'v' })
.join('video_schedules as vs', 'v.id', 'vs.video_id')
.where({
'v.active': 1,
'vs.site_id': 1
})
.andWhere('vs.live', '<=', Math.floor(Date.now()/1000))
.andWhere(function() {
this.where('vs.expire', 0).orWhere('vs.expire', '>=', Math.floor(Date.now()/1000))
})
.debug()
.then(console.log);Here is my relation from the Category class:
videos: {
relation: Model.ManyToManyRelation,
modelClass: __dirname + '/Video',
join: {
from: 'categories.id',
through: {
from: 'video_categories.category_id',
to: 'video_categories.video_id',
modelClass: __dirname + '/VideoCategory'
},
to: 'videos.id'
}
}Here is the generated SQL:
select distinct `c`.`id`, `c`.`name`
from `categories` as `c`
inner join `video_schedules` as `vs` on `v`.`id` = `vs`.`video_id`
inner join `video_categories` as `video_categories_rel_videos` on `video_categories_rel_videos`.`category_id` = `categories`.`id`
inner join `videos` as `v` on `video_categories_rel_videos`.`video_id` = `v`.`id`
where `v`.`active` = ?
and `vs`.`site_id` = ?
and `vs`.`live` <= ?
and (`vs`.`expire` = ? or `vs`.`expire` >= ?)And, finally, here is the error:
Unhandled rejection Error: ER_BAD_FIELD_ERROR: Unknown column 'v.id' in 'on clause'
at Query.Sequence._packetToError
The .join portion comes before the .joinRelation portion in the generated SQL. Also, the joinRelation is using the full table name rather than the aliased name from the query:
inner join `video_categories` as `video_categories_rel_videos` on `video_categories_rel_videos`.`category_id` = `categories`.`id`Notice:
= `categories`.`id`even though I've specified .from('categories as c') in the query. Thanks!