Hi Gunnar,
for subqueries you have to use a DBQuery object to wrap your subquery command.
// Define the sub query
DBCommand subCmd = db.createCommand();
subCmd.select(...);
// Wrap command for subquery
DBQuery SQ = new DBQuery(subCmd);
// Define the main query
DBCommand cmd = db.createCommand();
cmd.select(..);
cmd.select(SQ.findQueryColumn(...));
cmd.join(..., SQ.findQueryColumn(...));
The DBQuery class does the aliasing part.
There is an example for subqueries in the advanced sample application provided
with the distribution (empire-db-example-advanced). See function
querySample(...) in SampleAdvApp.java.
Hope this helps.
Regards
Rainer
P.S. What Andrew suggested can be useful for self-joins.
Gunnar Gr-Hovest wrote:
> re: How to generate sub queries
>
> Hi,
>
> I would like to know to which extent sub queries in the FROM part of an
> SQL statement are currently supported by empire-db. I tried to create
> such a sub query in the following way:
>
> DBCommand subQuery = db.createCommand();
>
> subQuery.select(MY_TABLE.ID.as("MY_KEY"), ...);
> subQuery.join(...);
> subQuery.where(...);
>
> DBCommand mainQuery = db.createCommand();
>
> mainQuery.select(subQuery.getCmdColumn(1), ...);
> mainQuery.join(subQuery.getCmdColumn(0), MY_TABLE.ID);
>
> The resulting SQL statement is:
>
> SELECT ...
> FROM ( SELECT ...
> FROM ...
> WHERE ...
> )
> INNER JOIN MY_TABLE t1 ON
> t1.ID = MY_KEY
>
> The execution of this statement on a MySQL database fails and I get a
> "MySQLSyntaxErrorException: Every derived table must have its own
> alias", i.e. the correct statement including the necessary alias would
> be:
>
> SELECT ...
> FROM ( SELECT ...
> FROM ...
> WHERE ...
> ) MY_ALIAS
> INNER JOIN MY_TABLE t1 ON
> t1.ID = MY_ALIAS.MY_KEY
>
> Is it possible for me to somehow add this missing alias? Or is there
> another way to generate such a sub query?
>
> Or is this type of subquerying simply not supported yet by empire-db?
>
> Thanks,
> Gunnar
