You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@metamodel.apache.org by Chandan Madhesia <ch...@gmail.com> on 2013/11/01 22:53:08 UTC
Facing issue in sub query
Hi
I am trying to execute where clause of the following form.
SELECT * FROM <someTable> WHERE <someColumn> IN (SELECT <someOtherColumn>
from <someOtherTable>) ;
This is the code.
import java.util.Date;
import org.eobjects.metamodel.query.FunctionType;
import org.eobjects.metamodel.query.OperatorType;
import org.eobjects.metamodel.query.Query;
import org.eobjects.metamodel.query.SelectItem;
import org.eobjects.metamodel.schema.Column;
import org.eobjects.metamodel.schema.ColumnType;
import org.eobjects.metamodel.schema.MutableColumn;
import org.eobjects.metamodel.schema.MutableTable;
import org.eobjects.metamodel.schema.Table;
public class MetamodelTest {
static class SupplyFact {
static Table table = new MutableTable("supply_fact");
static Column dayKey = new MutableColumn("day_key",
ColumnType.BIGINT).setTable(table);
static Column adRequests = new MutableColumn("ad_requests",
ColumnType.BIGINT).setTable(table);
}
static class DateDim {
static Table dateDim = new MutableTable("date_dim");
static Column dayKey = new MutableColumn("day_key",
ColumnType.BIGINT).setTable(dateDim);
static Column fullDate = new MutableColumn("full_date",
ColumnType.DATE).setTable(dateDim);
}
public static void main(String[] args) {
Query query = new Query();
query.select(new
SelectItem(SupplyFact.dayKey).setAlias(SupplyFact.dayKey.getName()))
.select(new SelectItem(FunctionType.SUM, SupplyFact.adRequests))
.from(SupplyFact.table)
.where(SupplyFact.dayKey, OperatorType.IN, new
Query().select(DateDim.dayKey).from(DateDim.dateDim).where(DateDim.fullDate,
OperatorType.GREATER_THAN, new
Date()).getSelectClause().getSelectItem(DateDim.dayKey))
.groupBy(SupplyFact.dayKey);
System.out.println(query.toSql());
}
}
The Error I get is :
Exception in thread "main" java.lang.IllegalStateException: Could not
convert date_dim.day_key to number
at
org.eobjects.metamodel.util.FormatHelper.formatSqlValue(FormatHelper.java:214)
at
org.eobjects.metamodel.util.FormatHelper.formatSqlValue(FormatHelper.java:206)
at org.eobjects.metamodel.query.FilterItem.toSql(FilterItem.java:271)
at
org.eobjects.metamodel.query.AbstractQueryClause.toSql(AbstractQueryClause.java:149)
at org.eobjects.metamodel.query.Query.toSql(Query.java:477)
at org.eobjects.metamodel.query.Query.toSql(Query.java:470)
at com.inmobi.metamodel.MetamodelTest.main(MetamodelTest.java:36)
IT seems that FilterItem converts everything to List if OperatorType.IN is
used . I think the operand type should be considered while generating query
string in toSql() method of FilterItem.
Can this be fixed ?
Regards
chandan
Re: Facing issue in sub query
Posted by Kasper Sørensen <i....@gmail.com>.
Hi Chandan,
For doing the change you would roughly have to cover two broad scenarios,
which apply almost every time we consider adding stuff to the query
interface of MetaModel:
Implementation for backends that support SQL. That would in this case be
quite easy I think - make sure that toSql() of FilterItem covers the
scenario, and make sure that the QueryRewriter classes (in ...dialect
package) support it as well.
Implementation for backends that do not support SQL. This one is slightly
more intricate. The class called QueryPostprocessDataContext is key, since
it is the common evaluator/executor of queries when the backend itself
cannot do the hard work. In there you have a few places where the WHERE
clause is evaluated, and checking if the operand is a Query will form a new
path through the code which has to be implemented. Either we convert
(execute) the query into a list, and simply reuse the existing list-based
IN support, or else I can imagine we can do something lazy.
... And of course it needs adding a quite thorough unittest suite for the
various backends.
... And in the query builder API we should support setting the query
argument to the "in(...)" method of WhereBuilder.
The same recipe actually holds for adding UNION. But one thing about adding
UNION that I am not completely sharp on, is how the Query API would look
like.
Regarding your scenario - I understand what you're doing and I guess you
can use MetaModel in this way. But as we say on MetaModel's website:
"MetaModel isn't a data mapping framework. Instead we emphasize abstraction
of metadata and ability to add data sources at runtime, making MetaModel
great for generic data processing applications, less so for applications
modeled around a particular domain."
Generally speaking, if you're "just" doing mapping of a fixed domain model,
then I think you will find that many ORM frameworks do a better job than
MetaModel.
Kind regards,
Kasper
2013/11/3 Chandan Madhesia <ch...@gmail.com>
> Hi Kasper
>
> Thanks for your reply.
>
> What kind of design and implementation changes would be needed to add
> support for subqueries in IN clause ? Can you point me in that direction ?
>
> I can think of applying the IN clause while joining the two tables, which
> should work.
>
> My purpose for using Metamodel was to easily generate sql for quering
> certain tables. Doing this without the need of reverse engineering schema
> and table definition from a datasource. To that end I want to manually
> define data model on which I want to query. I thought the Metamodel apis'
> provide for the requirement. Do you suggest not to use Metamodel for this
> purpose ?
>
> One more thing that is lacking is the union and union all clause
> implementation in Metamodel , any plan for adding those?
>
> Regards
> chandan
>
>
>
>
>
>
> On Sun, Nov 3, 2013 at 7:50 PM, Kasper Sørensen <
> i.am.kasper.sorensen@gmail.com> wrote:
>
> > Hi Chandan,
> >
> > Subqueries in IN clause (or as another operand in the WHERE clause for
> that
> > matter) is currently not supported by MetaModel. MetaModel thus far only
> > supports subqueries in the FROM clause. So to your question if it can be
> > fixed, then at least not with the current design - but someone could of
> > course spend the effort in implementing support for it, which would be
> > lovely.
> >
> > As a workaround, usually you can express the query with a join instead
> (and
> > in your case then also an additional WHERE and GROUP BY item). Reading
> your
> > code I couldn't find out exactly how because I think you might be missing
> > something to join/link the two tables together? What would your preferred
> > SQL query look like?
> >
> > One remark to your code: It seems you're defining the columns and tables
> > yourself and putting them into some class structure which represents the
> > tables? This is not how MetaModel was thought to be used; rather than you
> > defining the model, it would be exposed by the library through the
> metadata
> > discovery. Thus, calling for instance ...
> >
> > DataContext dataContext = ...
> > Table[] tables = dataContext.getDefaultSchema().getTables();
> >
> > ... would get you the *actual *tables of the data source you're working
> > with. Similarly you can get tables, columns, schemas etc. by name and by
> > traversal. Not sure if you are aware of this when coding?
> >
> > Best regards,
> > Kasper
> >
> >
> > 2013/11/1 Chandan Madhesia <ch...@gmail.com>
> >
> > > Hi
> > >
> > > I am trying to execute where clause of the following form.
> > >
> > > SELECT * FROM <someTable> WHERE <someColumn> IN (SELECT
> <someOtherColumn>
> > > from <someOtherTable>) ;
> > >
> > > This is the code.
> > >
> > > import java.util.Date;
> > > import org.eobjects.metamodel.query.FunctionType;
> > > import org.eobjects.metamodel.query.OperatorType;
> > > import org.eobjects.metamodel.query.Query;
> > > import org.eobjects.metamodel.query.SelectItem;
> > > import org.eobjects.metamodel.schema.Column;
> > > import org.eobjects.metamodel.schema.ColumnType;
> > > import org.eobjects.metamodel.schema.MutableColumn;
> > > import org.eobjects.metamodel.schema.MutableTable;
> > > import org.eobjects.metamodel.schema.Table;
> > >
> > > public class MetamodelTest {
> > >
> > > static class SupplyFact {
> > > static Table table = new MutableTable("supply_fact");
> > > static Column dayKey = new MutableColumn("day_key",
> > > ColumnType.BIGINT).setTable(table);
> > > static Column adRequests = new MutableColumn("ad_requests",
> > > ColumnType.BIGINT).setTable(table);
> > > }
> > > static class DateDim {
> > > static Table dateDim = new MutableTable("date_dim");
> > > static Column dayKey = new MutableColumn("day_key",
> > > ColumnType.BIGINT).setTable(dateDim);
> > > static Column fullDate = new MutableColumn("full_date",
> > > ColumnType.DATE).setTable(dateDim);
> > > }
> > > public static void main(String[] args) {
> > > Query query = new Query();
> > > query.select(new
> > > SelectItem(SupplyFact.dayKey).setAlias(SupplyFact.dayKey.getName()))
> > > .select(new SelectItem(FunctionType.SUM, SupplyFact.adRequests))
> > > .from(SupplyFact.table)
> > > .where(SupplyFact.dayKey, OperatorType.IN, new
> > >
> > >
> >
> Query().select(DateDim.dayKey).from(DateDim.dateDim).where(DateDim.fullDate,
> > > OperatorType.GREATER_THAN, new
> > > Date()).getSelectClause().getSelectItem(DateDim.dayKey))
> > > .groupBy(SupplyFact.dayKey);
> > > System.out.println(query.toSql());
> > > }
> > > }
> > >
> > >
> > > The Error I get is :
> > > Exception in thread "main" java.lang.IllegalStateException: Could not
> > > convert date_dim.day_key to number
> > > at
> > >
> > >
> >
> org.eobjects.metamodel.util.FormatHelper.formatSqlValue(FormatHelper.java:214)
> > > at
> > >
> > >
> >
> org.eobjects.metamodel.util.FormatHelper.formatSqlValue(FormatHelper.java:206)
> > > at org.eobjects.metamodel.query.FilterItem.toSql(FilterItem.java:271)
> > > at
> > >
> > >
> >
> org.eobjects.metamodel.query.AbstractQueryClause.toSql(AbstractQueryClause.java:149)
> > > at org.eobjects.metamodel.query.Query.toSql(Query.java:477)
> > > at org.eobjects.metamodel.query.Query.toSql(Query.java:470)
> > > at com.inmobi.metamodel.MetamodelTest.main(MetamodelTest.java:36)
> > >
> > >
> > > IT seems that FilterItem converts everything to List if OperatorType.IN
> > is
> > > used . I think the operand type should be considered while generating
> > query
> > > string in toSql() method of FilterItem.
> > >
> > > Can this be fixed ?
> > >
> > > Regards
> > > chandan
> > >
> >
>
Re: Facing issue in sub query
Posted by Chandan Madhesia <ch...@gmail.com>.
Hi Kasper
Thanks for your reply.
What kind of design and implementation changes would be needed to add
support for subqueries in IN clause ? Can you point me in that direction ?
I can think of applying the IN clause while joining the two tables, which
should work.
My purpose for using Metamodel was to easily generate sql for quering
certain tables. Doing this without the need of reverse engineering schema
and table definition from a datasource. To that end I want to manually
define data model on which I want to query. I thought the Metamodel apis'
provide for the requirement. Do you suggest not to use Metamodel for this
purpose ?
One more thing that is lacking is the union and union all clause
implementation in Metamodel , any plan for adding those?
Regards
chandan
On Sun, Nov 3, 2013 at 7:50 PM, Kasper Sørensen <
i.am.kasper.sorensen@gmail.com> wrote:
> Hi Chandan,
>
> Subqueries in IN clause (or as another operand in the WHERE clause for that
> matter) is currently not supported by MetaModel. MetaModel thus far only
> supports subqueries in the FROM clause. So to your question if it can be
> fixed, then at least not with the current design - but someone could of
> course spend the effort in implementing support for it, which would be
> lovely.
>
> As a workaround, usually you can express the query with a join instead (and
> in your case then also an additional WHERE and GROUP BY item). Reading your
> code I couldn't find out exactly how because I think you might be missing
> something to join/link the two tables together? What would your preferred
> SQL query look like?
>
> One remark to your code: It seems you're defining the columns and tables
> yourself and putting them into some class structure which represents the
> tables? This is not how MetaModel was thought to be used; rather than you
> defining the model, it would be exposed by the library through the metadata
> discovery. Thus, calling for instance ...
>
> DataContext dataContext = ...
> Table[] tables = dataContext.getDefaultSchema().getTables();
>
> ... would get you the *actual *tables of the data source you're working
> with. Similarly you can get tables, columns, schemas etc. by name and by
> traversal. Not sure if you are aware of this when coding?
>
> Best regards,
> Kasper
>
>
> 2013/11/1 Chandan Madhesia <ch...@gmail.com>
>
> > Hi
> >
> > I am trying to execute where clause of the following form.
> >
> > SELECT * FROM <someTable> WHERE <someColumn> IN (SELECT <someOtherColumn>
> > from <someOtherTable>) ;
> >
> > This is the code.
> >
> > import java.util.Date;
> > import org.eobjects.metamodel.query.FunctionType;
> > import org.eobjects.metamodel.query.OperatorType;
> > import org.eobjects.metamodel.query.Query;
> > import org.eobjects.metamodel.query.SelectItem;
> > import org.eobjects.metamodel.schema.Column;
> > import org.eobjects.metamodel.schema.ColumnType;
> > import org.eobjects.metamodel.schema.MutableColumn;
> > import org.eobjects.metamodel.schema.MutableTable;
> > import org.eobjects.metamodel.schema.Table;
> >
> > public class MetamodelTest {
> >
> > static class SupplyFact {
> > static Table table = new MutableTable("supply_fact");
> > static Column dayKey = new MutableColumn("day_key",
> > ColumnType.BIGINT).setTable(table);
> > static Column adRequests = new MutableColumn("ad_requests",
> > ColumnType.BIGINT).setTable(table);
> > }
> > static class DateDim {
> > static Table dateDim = new MutableTable("date_dim");
> > static Column dayKey = new MutableColumn("day_key",
> > ColumnType.BIGINT).setTable(dateDim);
> > static Column fullDate = new MutableColumn("full_date",
> > ColumnType.DATE).setTable(dateDim);
> > }
> > public static void main(String[] args) {
> > Query query = new Query();
> > query.select(new
> > SelectItem(SupplyFact.dayKey).setAlias(SupplyFact.dayKey.getName()))
> > .select(new SelectItem(FunctionType.SUM, SupplyFact.adRequests))
> > .from(SupplyFact.table)
> > .where(SupplyFact.dayKey, OperatorType.IN, new
> >
> >
> Query().select(DateDim.dayKey).from(DateDim.dateDim).where(DateDim.fullDate,
> > OperatorType.GREATER_THAN, new
> > Date()).getSelectClause().getSelectItem(DateDim.dayKey))
> > .groupBy(SupplyFact.dayKey);
> > System.out.println(query.toSql());
> > }
> > }
> >
> >
> > The Error I get is :
> > Exception in thread "main" java.lang.IllegalStateException: Could not
> > convert date_dim.day_key to number
> > at
> >
> >
> org.eobjects.metamodel.util.FormatHelper.formatSqlValue(FormatHelper.java:214)
> > at
> >
> >
> org.eobjects.metamodel.util.FormatHelper.formatSqlValue(FormatHelper.java:206)
> > at org.eobjects.metamodel.query.FilterItem.toSql(FilterItem.java:271)
> > at
> >
> >
> org.eobjects.metamodel.query.AbstractQueryClause.toSql(AbstractQueryClause.java:149)
> > at org.eobjects.metamodel.query.Query.toSql(Query.java:477)
> > at org.eobjects.metamodel.query.Query.toSql(Query.java:470)
> > at com.inmobi.metamodel.MetamodelTest.main(MetamodelTest.java:36)
> >
> >
> > IT seems that FilterItem converts everything to List if OperatorType.IN
> is
> > used . I think the operand type should be considered while generating
> query
> > string in toSql() method of FilterItem.
> >
> > Can this be fixed ?
> >
> > Regards
> > chandan
> >
>
Re: Facing issue in sub query
Posted by Kasper Sørensen <i....@gmail.com>.
Hi Chandan,
Subqueries in IN clause (or as another operand in the WHERE clause for that
matter) is currently not supported by MetaModel. MetaModel thus far only
supports subqueries in the FROM clause. So to your question if it can be
fixed, then at least not with the current design - but someone could of
course spend the effort in implementing support for it, which would be
lovely.
As a workaround, usually you can express the query with a join instead (and
in your case then also an additional WHERE and GROUP BY item). Reading your
code I couldn't find out exactly how because I think you might be missing
something to join/link the two tables together? What would your preferred
SQL query look like?
One remark to your code: It seems you're defining the columns and tables
yourself and putting them into some class structure which represents the
tables? This is not how MetaModel was thought to be used; rather than you
defining the model, it would be exposed by the library through the metadata
discovery. Thus, calling for instance ...
DataContext dataContext = ...
Table[] tables = dataContext.getDefaultSchema().getTables();
... would get you the *actual *tables of the data source you're working
with. Similarly you can get tables, columns, schemas etc. by name and by
traversal. Not sure if you are aware of this when coding?
Best regards,
Kasper
2013/11/1 Chandan Madhesia <ch...@gmail.com>
> Hi
>
> I am trying to execute where clause of the following form.
>
> SELECT * FROM <someTable> WHERE <someColumn> IN (SELECT <someOtherColumn>
> from <someOtherTable>) ;
>
> This is the code.
>
> import java.util.Date;
> import org.eobjects.metamodel.query.FunctionType;
> import org.eobjects.metamodel.query.OperatorType;
> import org.eobjects.metamodel.query.Query;
> import org.eobjects.metamodel.query.SelectItem;
> import org.eobjects.metamodel.schema.Column;
> import org.eobjects.metamodel.schema.ColumnType;
> import org.eobjects.metamodel.schema.MutableColumn;
> import org.eobjects.metamodel.schema.MutableTable;
> import org.eobjects.metamodel.schema.Table;
>
> public class MetamodelTest {
>
> static class SupplyFact {
> static Table table = new MutableTable("supply_fact");
> static Column dayKey = new MutableColumn("day_key",
> ColumnType.BIGINT).setTable(table);
> static Column adRequests = new MutableColumn("ad_requests",
> ColumnType.BIGINT).setTable(table);
> }
> static class DateDim {
> static Table dateDim = new MutableTable("date_dim");
> static Column dayKey = new MutableColumn("day_key",
> ColumnType.BIGINT).setTable(dateDim);
> static Column fullDate = new MutableColumn("full_date",
> ColumnType.DATE).setTable(dateDim);
> }
> public static void main(String[] args) {
> Query query = new Query();
> query.select(new
> SelectItem(SupplyFact.dayKey).setAlias(SupplyFact.dayKey.getName()))
> .select(new SelectItem(FunctionType.SUM, SupplyFact.adRequests))
> .from(SupplyFact.table)
> .where(SupplyFact.dayKey, OperatorType.IN, new
>
> Query().select(DateDim.dayKey).from(DateDim.dateDim).where(DateDim.fullDate,
> OperatorType.GREATER_THAN, new
> Date()).getSelectClause().getSelectItem(DateDim.dayKey))
> .groupBy(SupplyFact.dayKey);
> System.out.println(query.toSql());
> }
> }
>
>
> The Error I get is :
> Exception in thread "main" java.lang.IllegalStateException: Could not
> convert date_dim.day_key to number
> at
>
> org.eobjects.metamodel.util.FormatHelper.formatSqlValue(FormatHelper.java:214)
> at
>
> org.eobjects.metamodel.util.FormatHelper.formatSqlValue(FormatHelper.java:206)
> at org.eobjects.metamodel.query.FilterItem.toSql(FilterItem.java:271)
> at
>
> org.eobjects.metamodel.query.AbstractQueryClause.toSql(AbstractQueryClause.java:149)
> at org.eobjects.metamodel.query.Query.toSql(Query.java:477)
> at org.eobjects.metamodel.query.Query.toSql(Query.java:470)
> at com.inmobi.metamodel.MetamodelTest.main(MetamodelTest.java:36)
>
>
> IT seems that FilterItem converts everything to List if OperatorType.IN is
> used . I think the operand type should be considered while generating query
> string in toSql() method of FilterItem.
>
> Can this be fixed ?
>
> Regards
> chandan
>