Page 1 of 1

Query Builder group by cannot find column alias

PostPosted: Sun May 30, 2021 9:25 pm
by pitc
The group by clause fails to find the column alias defined in a complex result part.
In the result part I define:
Code: Select all
   q.result
                 ...
      .add(q.getColumn(edis_alias,'daily_sales').divide(q.getColumn(edis_alias,'daily_units').nullif(0)),'avg_unit_pr') //NOTE need check for div by 0)


In the group by I define:
Code: Select all
   q.groupBy
      .add(...)
                 ...
      .add(q.getColumn(edis_alias,'avg_unit_pr'))

Before I added these lines to the result and group by part the query runs fine.
The error message is
Cannot find column 'avg_unit_pr' in data source ....

Where the datasource is the table: edis_alias.
I am thinking that the alias 'avg_unit_pr' has become lost or assigned to some unknown datasource or my code is incorrect....the equivalent sql runs fine.
Any ideas are welcomed before I submit as a Jira issue.
Tom

Re: Query Builder group by cannot find column alias

PostPosted: Fri Jun 04, 2021 10:16 am
by rgansevles
Tom,

In a simple case it seems to work fine:

Code: Select all
var q = datasources.db.example_data.book_nodes.createSelect('mytablealias');
q.result.add(q.getColumn('mytablealias', 'label_text')).add(q.columns.node_id.count)
q.groupBy.add(q.getColumn('mytablealias', 'label_text'));


this results in sql
Code: Select all
select mytablealias.label_text, count(mytablealias.node_id) from book_nodes mytablealias group by  mytablealias.label_text


Can you show a complete example where it goes wrong?

Rob