You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@cayenne.apache.org by atomix <sa...@yahoo.com> on 2012/02/09 10:33:51 UTC
HOW TO : Creating a SelectQuery has Expression like this?
I have another question regarding Expression in Cayenne :
I want my generated SQL like so :
*SELECT **
*FROM *Book B1
*JOIN *OldBook B2 *ON *B1.id= B2.id
*WHERE *B1.hit = B2.hit
so I used Expression.fromString("Book.hit = OldBook.hit");
... but it's not affect anything to the generated SQL, even when the String
is wrong!!!!??
Can anyone point me the way to do this kind of Query, not to metion
SQLTemplate because I know the way to do so!
Thank in advance!
--
View this message in context: http://cayenne.195.n3.nabble.com/HOW-TO-Creating-a-SelectQuery-has-Expression-like-this-tp3728912p3728912.html
Sent from the Cayenne - User mailing list archive at Nabble.com.
Re: HOW TO : Creating a SelectQuery has Expression like this?
Posted by Andrus Adamchik <an...@objectstyle.org>.
Ok, am seeing a common error - Expression.andExp creates a new expression, instead of appending to an existing one (unlike SelectQuery.andQualifier, which doesn't create a new SelectQuery .. I know this can be confusing).
So this:
> Expression qual = ExpressionFactory.matchExp("toTestresult", aTestresult);
> qual.andExp(Expression.fromString("toAnswer.status = status"));
> qual.andExp(Expression.fromString("status = 1"));
Should be written as:
Expression qual = ExpressionFactory.matchExp("toTestresult", aTestresult);
qual = qual.andExp(Expression.fromString("toAnswer.status = status"));
qual = qual.andExp(Expression.fromString("status = 1"));
Andrus
On Feb 9, 2012, at 1:44 PM, atomix wrote:
> I don't have the orginal source code here but here is a similar code :
>
> Testresultanswer is the result of an Answer which User fill;
> Testresultanswer has RelationShip toAnswer
>
> Testresult has (some) Testresultanswer
> Testresultanswer has RelationShip toTestresult
>
> Answer.status = 1 (correct Answer)
> if Testresultanswer.status = Answer.status = 1(User choose the right
> answer);
>
>
> // DIDN'T WORK ???
> public static int getScore2(Testresult aTestresult) {
> SelectQuery query = new SelectQuery(Testresultanswer.class);
>
> Expression qual = ExpressionFactory.matchExp("toTestresult", aTestresult);
> qual.andExp(Expression.fromString("toAnswer.status = status"));
> qual.andExp(Expression.fromString("status = 1"));
> query.setQualifier(qual);
>
> List<Testresultanswer> list = context.performQuery(query);
>
> return list.size();
>
> }
>
> // The method which made by SQL Template, WORK!!
> public static int getScore(Testresult aTestresult) {
>
> String sql = "select count(t1.idTestresultAnswer) as score"
> +" from testresultanswer t1 "
> +" inner join testresult t2 on t1.idTestresult = t2.idTestresult"
> +" join answer t3 on t1.idAnswer = t3.idAnswer"
> +" where"
> +" t2.idTestresult = $idTestresult and"
> +" t1.status = t3.status and t3.status = $rightValue"
> +" order by t1.idTestresultAnswer";
>
> //System.out.println(sql);
> SQLTemplate query = new SQLTemplate(Testresultanswer.class, sql);
> // put parameters
> Map map = new HashMap();
> map.put("idTestresult", aTestresult.getIdTestresult());
> map.put("rightValue", 1);
>
> query.setParameters(map);
>
> // *** let Cayenne know that result is a scalar
> SQLResult resultDescriptor = new SQLResult();
> resultDescriptor.addColumnResult("score");
> query.setResult(resultDescriptor);
>
> // List of Number's
> Number assetsValue = (Number) DataObjectUtils.objectForQuery(context,
> query);
>
> return assetsValue.intValue();
> }
>
> --
> View this message in context: http://cayenne.195.n3.nabble.com/HOW-TO-Creating-a-SelectQuery-has-Expression-like-this-tp3728912p3729086.html
> Sent from the Cayenne - User mailing list archive at Nabble.com.
>
Re: HOW TO : Creating a SelectQuery has Expression like this?
Posted by atomix <sa...@yahoo.com>.
Thank so much for your help! You saved my day!
I have some more question in the WebApp area, but may be is should be in
another Topic.
Again, THANK ! And wish you the best !
--
View this message in context: http://cayenne.195.n3.nabble.com/HOW-TO-Creating-a-SelectQuery-has-Expression-like-this-tp3728912p3729189.html
Sent from the Cayenne - User mailing list archive at Nabble.com.
Re: HOW TO : Creating a SelectQuery has Expression like this?
Posted by Andrus Adamchik <an...@objectstyle.org>.
Ah, you found it yourself :) See my other message explaining chaining expressions outside and inside the SelectQuery.
On Feb 9, 2012, at 1:56 PM, atomix wrote:
>
> Expression qual = ExpressionFactory.matchExp("toTestresult", aTestresult);
> //qual.andExp(Expression.fromString("getToAnswer.status = status"));
> //qual.andExp(Expression.fromString("status = 1"));
>
> query.andQualifier(Expression.fromString("toAnswer.status = status"));
> query.andQualifier(Expression.fromString("status = 1"));
> query.andQualifier(qual);
>
> Ahhhhhhhhh, got that!
>
> I should do like
> *query.andQualifier*
> not
> *qual.andExp*
>
> So, what are the differences between the two ?
>
>
> --
> View this message in context: http://cayenne.195.n3.nabble.com/HOW-TO-Creating-a-SelectQuery-has-Expression-like-this-tp3728912p3729107.html
> Sent from the Cayenne - User mailing list archive at Nabble.com.
>
Re: HOW TO : Creating a SelectQuery has Expression like this?
Posted by atomix <sa...@yahoo.com>.
Expression qual = ExpressionFactory.matchExp("toTestresult", aTestresult);
//qual.andExp(Expression.fromString("getToAnswer.status = status"));
//qual.andExp(Expression.fromString("status = 1"));
query.andQualifier(Expression.fromString("toAnswer.status = status"));
query.andQualifier(Expression.fromString("status = 1"));
query.andQualifier(qual);
Ahhhhhhhhh, got that!
I should do like
*query.andQualifier*
not
*qual.andExp*
So, what are the differences between the two ?
--
View this message in context: http://cayenne.195.n3.nabble.com/HOW-TO-Creating-a-SelectQuery-has-Expression-like-this-tp3728912p3729107.html
Sent from the Cayenne - User mailing list archive at Nabble.com.
Re: HOW TO : Creating a SelectQuery has Expression like this?
Posted by atomix <sa...@yahoo.com>.
GENERATED QUERY :
INFO - QueryLogger - SELECT t0.idTestResultAnswer,
t0.status, t0.value, t0.idAnswer, t0.idTestResult FROM testresultanswer t0
WHERE t0.idTestResult = ? [bind: 1->idTestResult:9]
INFO - QueryLogger - === returned 4 rows. - took 0 ms.
return 4;
the correct Method return 0;
--
View this message in context: http://cayenne.195.n3.nabble.com/HOW-TO-Creating-a-SelectQuery-has-Expression-like-this-tp3728912p3729092.html
Sent from the Cayenne - User mailing list archive at Nabble.com.
Re: HOW TO : Creating a SelectQuery has Expression like this?
Posted by atomix <sa...@yahoo.com>.
I don't have the orginal source code here but here is a similar code :
Testresultanswer is the result of an Answer which User fill;
Testresultanswer has RelationShip toAnswer
Testresult has (some) Testresultanswer
Testresultanswer has RelationShip toTestresult
Answer.status = 1 (correct Answer)
if Testresultanswer.status = Answer.status = 1(User choose the right
answer);
// DIDN'T WORK ???
public static int getScore2(Testresult aTestresult) {
SelectQuery query = new SelectQuery(Testresultanswer.class);
Expression qual = ExpressionFactory.matchExp("toTestresult", aTestresult);
qual.andExp(Expression.fromString("toAnswer.status = status"));
qual.andExp(Expression.fromString("status = 1"));
query.setQualifier(qual);
List<Testresultanswer> list = context.performQuery(query);
return list.size();
}
// The method which made by SQL Template, WORK!!
public static int getScore(Testresult aTestresult) {
String sql = "select count(t1.idTestresultAnswer) as score"
+" from testresultanswer t1 "
+" inner join testresult t2 on t1.idTestresult = t2.idTestresult"
+" join answer t3 on t1.idAnswer = t3.idAnswer"
+" where"
+" t2.idTestresult = $idTestresult and"
+" t1.status = t3.status and t3.status = $rightValue"
+" order by t1.idTestresultAnswer";
//System.out.println(sql);
SQLTemplate query = new SQLTemplate(Testresultanswer.class, sql);
// put parameters
Map map = new HashMap();
map.put("idTestresult", aTestresult.getIdTestresult());
map.put("rightValue", 1);
query.setParameters(map);
// *** let Cayenne know that result is a scalar
SQLResult resultDescriptor = new SQLResult();
resultDescriptor.addColumnResult("score");
query.setResult(resultDescriptor);
// List of Number's
Number assetsValue = (Number) DataObjectUtils.objectForQuery(context,
query);
return assetsValue.intValue();
}
--
View this message in context: http://cayenne.195.n3.nabble.com/HOW-TO-Creating-a-SelectQuery-has-Expression-like-this-tp3728912p3729086.html
Sent from the Cayenne - User mailing list archive at Nabble.com.
Re: HOW TO : Creating a SelectQuery has Expression like this?
Posted by Andrus Adamchik <an...@objectstyle.org>.
On Feb 9, 2012, at 12:58 PM, atomix wrote:
> "hit = toOldBook.hit" (Wrong)
This should work. Could you show how you build your entire query and what SQL Cayenne generate in the logs?
Andrus
Re: HOW TO : Creating a SelectQuery has Expression like this?
Posted by atomix <sa...@yahoo.com>.
Yes, they have 1-n relationship "toOldBook", I tested
"book.hit = toOldBook.hit" (Wrong)
"Book.hit = toOldBook.hit" (Wrong)
"hit = toOldBook.hit" (Wrong)
but
"toOldBook.hit = 1" ( Correct)
weird
"toOldHell = 1" (nothing name to oldHell of course)
the query still run without any exception???
In case you want to know , book.hit and OldBook.hit both is INTEGER ... So I
really confuse about, can Cayenne really understand the complex WHERE like
that...
--
View this message in context: http://cayenne.195.n3.nabble.com/HOW-TO-Creating-a-SelectQuery-has-Expression-like-this-tp3728912p3728983.html
Sent from the Cayenne - User mailing list archive at Nabble.com.
Re: HOW TO : Creating a SelectQuery has Expression like this?
Posted by Andrus Adamchik <an...@objectstyle.org>.
> so I used Expression.fromString("Book.hit = OldBook.hit");
You are on the right track with this, but you need to get your path's right. Aside from using aliases which is a separate topic, the general concept of expression paths is "they all must be relative to the root entity of the SelectQuery".
So there is a relationship between Book and OldBook. Don't know what it is called, let's assume it is "oldBookRelationship"... So then an expression can be built like this:
Expression.fromString("hit = oldBookRelationship.hit");
Hope this helps.
Andrus
On Feb 9, 2012, at 12:33 PM, atomix wrote:
> I have another question regarding Expression in Cayenne :
>
> I want my generated SQL like so :
>
> *SELECT **
> *FROM *Book B1
> *JOIN *OldBook B2 *ON *B1.id= B2.id
> *WHERE *B1.hit = B2.hit
>
> so I used Expression.fromString("Book.hit = OldBook.hit");
>
> ... but it's not affect anything to the generated SQL, even when the String
> is wrong!!!!??
>
> Can anyone point me the way to do this kind of Query, not to metion
> SQLTemplate because I know the way to do so!
>
> Thank in advance!