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 < [email protected]> 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 <[email protected]> > > > 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 > > >
