Hi All,
I ran into a problem adding group-by clauses to my query.
What I do it just this:
var qbSelect = foundSet.getQuery();
var newJoin = qbSelect.joins.add(relationName, aliasName);
var qbColumn = newJoin.getColumn(groupingColumnName);
qbSelect.groupBy.add(qbColumn);
All works well if my original foundset doesn’t have a join to the table I’m adding a grouping on.
So, in a scenario when I want to group assignments by employee code from employee table, I get the following:
My original foundset query is something like this, it’s already got the join to employees:
‘SELECT idassignment from assignment join employee on assignment.idemployee = employee.idemployee Order by assignment.idassignment’.
When I add my join via ```
qbSelect.joins.add(relationName, relatedColumnName);
column “employee.employeecode” must appear in the GROUP BY clause or be used in an aggregate function
The error message also has the query I"m trying to load and it's like this:
select assignment.idassignment
from assignment assignment
left outer join employee employee on assignment.idemployee=employee.idemployee
left outer join employee employeecode on assignment.idemployee=employeecode.idemployee
group by assignment.idassignment , assignment.startdate , employeecode.employeecode
order by assignment.startdate asc, employee.employeecode asc, assignment.idassignment asc
So it looks like I'm adding a join with alias 'employeecode', the query builder adds that field to the group-by clause. Which is fine.
But at that point the order-by clause already has a reference to the same column from the same table but the other alias: 'employee.employeecode'.
I tried to clear the qbSelect.sort and add my own column to it, but it just doesn't work, it sticks that column there anyway.
How do I figure this out?