Please use the google group to ask questions - thanks.

by imario 17 Mar 14:09
expression "in query"

In my process of moving my application to avaje I now reached a point where I am a little bit lost :-(

I mainly use the Query API and need a feature which allows me to describe a select using "where propertyName_A in (select propertyName_B from itemTable where ...)"

Something like "Expr.in(String propertyName_A, Query query)" whould be great, where "query" needs to have ".select(propertyName_B)" configured for sure.

I tried to dig into this, but, well, facing some complicated data structures ;-)

Is this doable?

17 Mar 14:58
by imario

This is what I've found so fare ... it is close :-)

It works except for the fact that the generated select does not only contain those properties configured with .select() but also the id columns, which is clear for partial objects, but here I'd like to avoid this.

So the thing (or one of the things) is, to avoid rendering the id columns in the select and where clause.

BTW: This is something you do in Hibernate using Projections, probably such a API should be the way to go for Avaje too, but I just wanted to implement this very feature I need now.


import com.avaje.ebean.expression.Expression;
import com.avaje.ebean.expression.ExpressionRequest;
import com.avaje.ebean.query.OrmQuery;
import com.avaje.ebean.server.core.DefaultServer;
import com.avaje.ebean.server.core.QueryRequest;
import com.avaje.ebean.server.query.CQuery;
import com.avaje.ebean.server.query.CQueryBuilder;

import java.util.List;

public class InQueryExpression implements Expression
{
private final String propertyName;
private final OrmQuery query;

private transient CQuery builtQuery;

public InQueryExpression(String propertyName, OrmQuery query)
{
this.propertyName = propertyName;
this.query = query;
}

public String getPropertyName()
{
return propertyName;
}

public int queryPlanHash()
{
int hc = InQueryExpression.class.getName().hashCode();
hc = hc * 31 + propertyName.hashCode();
hc = hc * 31 + query.getQueryPlanHash();
return hc;
}

public int queryBindHash()
{
return 0;
}

public void addSql(ExpressionRequest request)
{
CQuery builtQuery = getBuiltQuery(request);

request.append(" (");
request.append(propertyName);
request.append(") in (");
request.append(builtQuery.getGeneratedSql());
request.append(") ");
}

private CQuery getBuiltQuery(ExpressionRequest request)
{
if (builtQuery == null)
{
DefaultServer defaultServer = (DefaultServer) request.getQueryRequest().getEbeanServer();
QueryRequest qr = defaultServer.createQueryRequest(query, request.getQueryRequest().getTransaction());
CQueryBuilder builder = new CQueryBuilder(defaultServer.getPlugin().getPluginCore());

builtQuery = builder.buildQuery(qr, defaultServer.getPlugin().getDbConfig().getBinder());
}

return builtQuery;
}

public void addBindValues(ExpressionRequest request)
{
CQuery builtQuery = getBuiltQuery(request);
List bindParams = builtQuery.getPredicates().getWhereExprBindValues(); // TODO: changed Ebean API here two times
if (bindParams == null)
{
return;
}

for (int i = 0; i
{
request.addBindValue(bindParams.get(i));
}
}
}

17 Mar 15:48
by imario

Ok, I managed to avoid rendering the id stuff too.

Please have a look at this patch if it makes you happy :-)

Index: ../ebean/src/com/avaje/ebean/server/query/CQueryPredicates.java
===================================================================
--- ../ebean/src/com/avaje/ebean/server/query/CQueryPredicates.java (revision 171)
+++ ../ebean/src/com/avaje/ebean/server/query/CQueryPredicates.java Tue Mar 17 15:46:54 CET 2009
@@ -479,4 +479,8 @@
return logPred;
}
}
+
+ public ArrayList getWhereExprBindValues() {
+ return whereExprBindValues;
-}
+ }
+}
Index: ../ebean/src/com/avaje/ebean/server/core/DefaultServer.java
===================================================================
--- ../ebean/src/com/avaje/ebean/server/core/DefaultServer.java (revision 171)
+++ ../ebean/src/com/avaje/ebean/server/core/DefaultServer.java Tue Mar 17 16:44:49 CET 2009
@@ -903,7 +902,7 @@
return findId(query, t);
}

- protected QueryRequest createQueryRequest(Query q, Transaction t) {
+ public QueryRequest createQueryRequest(Query q, Transaction t) {

OrmQuery query = (OrmQuery ) q;
BeanManager mgr = deploymentManager.getBeanManager(query.getBeanType());
Index: ../ebean/src/com/avaje/ebean/server/query/SqlTreeBuilder.java
===================================================================
--- ../ebean/src/com/avaje/ebean/server/query/SqlTreeBuilder.java (revision 171)
+++ ../ebean/src/com/avaje/ebean/server/query/SqlTreeBuilder.java Tue Mar 17 16:41:18 CET 2009
@@ -203,7 +203,7 @@

if (node.isRoot()) {
buildExtraJoins(node, myList);
- return new SqlTreeNodeRoot(node, props, myList, query.getIncludeTableJoin());
+ return new SqlTreeNodeRoot(node, props, myList, query.isReadId(), query.getIncludeTableJoin());

} else if (node.isManyJoin()) {
return new SqlTreeNodeManyRoot(node, props, myList);
Index: ../ebean/src/com/avaje/ebean/Query.java
===================================================================
--- ../ebean/src/com/avaje/ebean/Query.java (revision 171)
+++ ../ebean/src/com/avaje/ebean/Query.java Tue Mar 17 16:41:18 CET 2009
@@ -395,7 +395,14 @@
*/
public Query select(String fetchProperties);

- /**
+ /**
+ * read only those fields setup by {@link #select}
+ */
+ public void setReadId(boolean readSelectOnly);
+
+ public boolean isReadId();
+
+ /**
* Specify a property (associated bean) to join and fetch with
* its specific properties to include.
*


Index: ../ebean/src/com/avaje/ebean/query/DefaultOrmQuery.java
===================================================================
--- ../ebean/src/com/avaje/ebean/query/DefaultOrmQuery.java (revision 171)
+++ ../ebean/src/com/avaje/ebean/query/DefaultOrmQuery.java Tue Mar 17 16:41:36 CET 2009
@@ -56,6 +56,8 @@
*/
OrmQueryDetail detail;

+ boolean readId = true;
+
OrmQueryAttributes attributes;

/**
@@ -141,7 +143,7 @@

TransactionContext transactionContext;

- public DefaultOrmQuery(Class beanType, EbeanServer server) {
+ public DefaultOrmQuery(Class beanType, EbeanServer server) {
this.beanType = beanType;
this.server = server;
this.detail = new OrmQueryDetail();
@@ -738,4 +740,13 @@
this.generatedSql = generatedSql;
}

+ public boolean isReadId()
+ {
+ return readId;
-}
+ }
+
+ public void setReadId(boolean readId)
+ {
+ this.readId = readId;
+ }
+}
Index: ../ebean/src/com/avaje/ebean/server/query/CQuery.java
===================================================================
--- ../ebean/src/com/avaje/ebean/server/query/CQuery.java (revision 171)
+++ ../ebean/src/com/avaje/ebean/server/query/CQuery.java Tue Mar 17 15:44:59 CET 2009
@@ -714,4 +713,8 @@
public JoinNode getCurrentJoinNode(){
return currentJoinNode;
}
+
+ public CQueryPredicates getPredicates() {
+ return predicates;
-}
+ }
+}
Index: ../ebean/src/com/avaje/ebean/server/query/CQueryBuilder.java
===================================================================
--- ../ebean/src/com/avaje/ebean/server/query/CQueryBuilder.java (revision 171)
+++ ../ebean/src/com/avaje/ebean/server/query/CQueryBuilder.java Tue Mar 17 16:41:18 CET 2009
@@ -239,7 +239,7 @@
hasWhere = true;
}

- if (request.isFindById() || query.getId() != null){
+ if (query.isReadId() && (request.isFindById() || query.getId() != null)){
if (hasWhere){
sb.append(" and ");
} else {

17 Mar 21:09
by Rob

Hmmm... the intention was to use a "raw" expression for this...

Is there a reason why the "raw" expression does not work for you?

query.where().raw("propertyName_A in (select propertyName_B from itemTable where ...)");

18 Mar 07:02
by imario

I use the Query API whenever possible (not to say, I use it always), I don't like to use string operations to build a sql statement ...
In very rare cases I use NamedQueries, these are complicated but static sql statements.

With the Query API I often build the where clause dynamically. e.g. a various number of "or"s depending on the number of tokens passed in with a "search string".

So do I with the "in" use-case where the sub-select has a dynamic where clause. Sure, I can do this by building the sql string myself, but it just seems not right, given that the Query API provides a way for building "normal" queries.
And it looks so much nicer to create two query objects and then just tie them together.

If you don't want to add the InQueryExpression to Avaje it would be nice if you could open up the api at least to get in touch with the generated query and the binding values.

18 Mar 12:25
by Rob

Thats fair enough... I'll have a more indepth look later but it does look pretty neat.

I didn't understand what the "readId" meant? (but its late)

Cheers, Rob.

18 Mar 14:03
by imario

Normally each and every query has the key-fields genereated in the select clause and the where clause.

For the select, generated for the "in" use-case, these fields are not required (and results in an wrongly generated sql).
I introduced the "readId" boolean to control if the generated sql should contain key fields.

With readId=false and select("property") configuread, only this single property appears in the sql select clause and no key fields are generated in the where clause.

Ciao,
Mario

18 Mar 20:20
by Rob

Yes, of course... we want to exlude the id (that otherwise gets automatically added to the query).

Hmmm, the version column may also get automatically added... I need to check that.

Thanks, Rob.

19 Mar 12:08
by Rob

Logged as Enhancement http://www.avaje.org/bugdetail-92.html

I have got the code into HEAD.

There where a couple of tweaks... I could get around putting readId on the Query... and instead identified the query as being a "subquery" as opposed to a findUnique, findList, findSet or findMap.

So yeah, check it out and see if it does the business.

... and thats for the code too - great stuff/cool enhancement.

19 Mar 12:09
by Rob

Thanks that is!!! (rather than "thats")

20 Mar 09:31
by imario

I've updated to Avaje HEAD and it looks very good.
Thanks for applying it so fast!

Create a New Topic

Title:
Body:
 
Introduction User Guide (pdf) Install/Configure Public JavaDoc Whitepapers
General Database Specific Byte Code Deployment Annotations Features
Top Bugs Top Enhancements
woResponse