Subquery errors in objection v1.0.0 #842
Description
I have several reporting queries that use subqueries. Here is a basic example of a static model method using a subquery:
static getTrafficDailyHits(accountId, start, end) {
const subquery = AggAffHit.query()
.alias('h')
.select('h.date', 'h.raw', 'h.unique')
.joinRelation('site', { alias: 's' })
.where('s.account_id', accountId)
.andWhere('h.date', '>=', start)
.andWhere('h.date', '<', end)
.as('d');
return AggAffHit.query()
.select(
'c.c_date as date',
AggAffHit.raw('ifnull(sum(`d`.`raw`), 0) as `raw`'),
AggAffHit.raw('ifnull(sum(`d`.`unique`), 0) as `unique`')
)
.from('calendar as c')
.leftJoin(subquery, 'c.c_date', 'd.date')
.where('c.c_date', '>=', start)
.andWhere('c.c_date', '<', end)
.groupBy('c.c_date');
}This works in v0.9.4 and produces the following SQL:
select `c`.`c_date` as `date`,
ifnull(sum(`d`.`raw`), 0) as `raw`,
ifnull(sum(`d`.`unique`), 0) as `unique`
from `calendar` as `c` left join (
select `h`.`date`,
`h`.`raw`,
`h`.`unique`
from `agg_aff_hit` as `h`
inner join `sites` as `s` on `s`.`id` = `h`.`site_id`
where `s`.`account_id` = 3
and `h`.`date` >= '2018-03-16'
and`h`.`date` < '2018-03-17'
) as `d` on `c`.`c_date` = `d`.`date`
where `c`.`c_date` >= '2018-03-16'
and `c`.`c_date` < '2018-03-17'
group by `c`.`c_date`However, in v1.0.0, it seems that the .from() method in the main query is bleeding into the subquery. Here is the SQL from v1.0.0, which produces an error:
select `c`.`c_date` as `date`,
ifnull(sum(`d`.`raw`), 0) as `raw`,
ifnull(sum(`d`.`unique`), 0) as `unique`
from `calendar` as `c` left join (
select `h`.`date`,
`h`.`raw`,
`h`.`unique`
from `calendar` as `h`
inner join `sites` as `s` on `s`.`id` = `h`.`site_id`
where `s`.`account_id` = 3
and `h`.`date` >= '2018-03-16'
and `h`.`date` < '2018-03-17'
) as `d` on `c`.`c_date` = `d`.`date`
where `c`.`c_date` >= '2018-03-16'
and `c`.`c_date` < '2018-03-17'
group by `c`.`c_date`
ER_BAD_FIELD_ERROR: Unknown column 'h.date' in 'field list'Notice in v0.9.4, the table in the from clause in the subquery (which is the correct table name for the AggAffHit model):
from `agg_aff_hit` as `h`
versus the same clause in v1.0.0 (which seems to be using the table in the from() method in the main query):
from `calendar` as `h`
I'm not sure if this is a bug or intended as a result of the refactoring done in v1.0.0.