Sub-Query Support for SQL builder

[iintothewind]

Feature request

The initial sql statement is like:

select at.id, at.name, COALESCE(am.asset_deprecate,0) as asset_deprecate 
from (select ai.id, ai.name from asset_info ai join asset_clinical_record ar on ai.id = ar.asset_id where ai.site_id = :site_id and ai.hospital_id = :hospital_id and extract(year from ar.exam_date) = :year group by ai.id order by ai.id) as at 
left join (select ad.asset_id, sum(ad.deprecate_amount) as asset_deprecate from asset_depreciation ad where ad.site_id = :site_id and extract(year from ad.deprecate_date) = :year group by asset_id) as am 
on at.id = am.asset_id 
order by at.id

By using SQL builder, it could be:

new SQL()
  .SELECT("at.id", "at.name", "COALESCE(am.deprecation,0)")
  .FROM("(" + new SQL()
    .SELECT("ai.id", "ai.name")
    .FROM(" asset_info ai")
    .INNER_JOIN("asset_clinical_record ar on ai.id = ar.asset_id")
    .WHERE("ai.site_id = :site_id")
    .WHERE("ai.hospital_id = :hospital_id")
    .WHERE("extract(year from ar.exam_date) = :year")
    .GROUP_BY("ai.id")
    .ORDER_BY("ai.id")
    .toString().concat(") as at"))
  .LEFT_OUTER_JOIN("(" + new SQL()
    .SELECT("asset_id", "sum(deprecate_amount) as deprecation")
    .FROM("asset_depreciation")
    .WHERE("site_id = :site_id")
    .WHERE("extract(year from deprecate_date) = :year")
    .GROUP_BY("asset_id")
    .toString().concat(") as am on at.id = am.asset_id"))
  .ORDER_BY("at.id").toString();

It could be better if we can add SELECT directly to FROM and ...JOIN methods instead of doing "("+ stuff.

[h3adache]

This is slightly complicated. SQL builder is a simple query builder. It does not build up any type of state tree that can remember where subselects start and end. If from and join methods where to allow subselects directly we would have to build up a pointer to the From/Join that the select came from. This would be a larger rewrite of the SQL builder I think.

[Mooninaut]

Would it be possible to add overloads for the FROM(), WHERE() and various JOIN() methods that accept an SQL object parameter, and an optional alias? Then the parent SQL could append (SELECT ...) AS alias on output, without creating intermediate strings for each sub-query.