SQL Alias for calculated fields/columns

[timowest]

Is it possible to create a calculated field using query dsl that has an alias, and produces SQL similar to

Select mytable.a + mytable.b + mytable.c as mycalculation
from mytable

Also how would this calculation be referenced if it was in an subquery?

Many Thanks

timowest

Aliases for calculated expressions are not yet supported, but I guess support for this is necessary to enhance the expressiveness of the Querydsl SQL dialect.

The SQL query


select mytable.a + mytable.b + mytable.c as mycalculation
from mytable


should be expressable as


new SubQuery().from(mytable)
    .list(mytable.a.add(mytable.b).add(mytable.c).as(mycalculation))


I will provide a more concrete suggestion tomorrow.

owoolgar

Ideally what I am hoping to achieve is something like this:

query.from(
subQuery.from(Table.table).list(Table.table.field1.add(Table.table.field2).add(Table.table.field3).a
s("myCalculation"))).
list(myCalculation.avg(), myCalculation.min(). myCalculation.max());

However in my workflow case the inner query is more complicated and I have a load of joins

Thanks

timowest

Ok, I will add the methods needed for such constructs today and let you know how it works.

owoolgar

Thanks for your very quick replies and openness. I was unsure if this was a feasible or appropriate feature. If we can get this working that would be awesome.

timowest

Here is a similar query like yours expressed in Querydsl


// alias for the salary
PNumber<BigDecimal> sal = new PNumber<BigDecimal>(BigDecimal.class, "sal");
// alias for the subquery
PathBuilder<Object[]> sq = new PathBuilder<Object[]>(Object[].class, "sq");

// query execution
query().from(
        sq().from(employee)
        .list(employee.salary.add(employee.salary).add(employee.salary).as(sal)).as(sq)
      ).list(sq.get(sal).avg(), sq.get(sal).min(), sq.get(sal).max());


which is turned into


select avg(sq.sal), min(sq.sal), max(sq.sal)
from (select (e.SALARY + e.SALARY + e.SALARY) as sal from EMPLOYEE2 e) as sq


The variables sal and sq can be reused. You can for example declare a set of general subquery aliases as PathBuilder instances.
PathBuilder can be used for dynamic Path construction like shown in the example.

The method creates an SQLQuery instance and sq returns a SQLSubQuery instance.

as(String path) is not supported, since we want to keep Querydsl typesafe.

With reusable Path instances for operation and query aliases the code for the specific example is reduced to the query execution.

This example has been tested against all supported dialects of Querydsl SQL (Oracle, MySQL, Postgres, MSSQL, Derby, HSQLDB and H2)

This functionality is available in version 1.6.2 which has just been released.

owoolgar

Thanks for the work on this.. I tried it out its its exactly what I need.

timowest

Ok. Great. Feel free to suggest simplifications and additions.

Querydsl SQL is still a bit rough around the edges, but is improving steadily.

McKinley

Just to point out that if all you want is a shorter reference in your code then

ENumber sal = employee.salary.add(employee.salary).add(employee.salary);

works just fine. You can pass sal around instead of the repeating the long chain.

'm trying to join to a subquery and there doesn't seem to be a way. I can get by putting it in the from() clause, but soon I will want to join two subqueries to each other in the same statement. Is there some way to do this now?

Thanks

timowest

At the moment this is not possible.

I will add the following methods to SQLQuery :


    SQLQuery fullJoin(SubQuery<?> o);

    SQLQuery innerJoin(SubQuery<?> o);

    SQLQuery join(SubQuery<?> o);
   
    SQLQuery leftJoin(SubQuery<?> o);

McKinley

I thought I could work around this by putting the subquery in the from clause and then use a right outer join. I don't see support for right joins. It that correct or is the some way to do that with the join() method?

Thanks

timowest

Same thing, rightJoin is missing as well, and will be added.

McKinley

Thank you! I'm trying it out now.

https://bugs.launchpad.net/querydsl/+bug/594700

timowest

Ok, I will make the release later today.