You are viewing a plain text version of this content. The canonical link for it is here.
Posted to torque-user@db.apache.org by Alex Hepp <he...@evamann.de> on 2004/12/22 16:42:30 UTC
is this really the way to combine several or´s ?
Hi List!
I tried several hours now, to get a simple or to work... I must say, that i
really would appreciate a bit of documentation on that.
what i want now is this:
SELECT * FROM movii_user where (FIRST_NAME LIKE "%hello%" or LAST_NAME LIKE
"%hello%" OR EMAIL LIKE "%hello%" or LOGIN_NAME="%hello%") AND IS_DELETED=0;
pretty simple. now, what i did is the following:
************************
**CODE
************************
// just for demonstration i replaced a String by "hello"
userKeyWord = "%hello%";
Criteria crit = new Criteria();
crit.add(MoviiUser.IS_DELETED,0);
Criteria.Criterion a1 = crit.getNewCriterion( MoviiUserPeer.LAST_NAME,
(Object) (userKeyWord), Criteria.LIKE);
Criteria.Criterion a2 = crit.getNewCriterion(MoviiUserPeer.FIRST_NAME,
(Object)userKeyWord, Criteria.LIKE);
Criteria.Criterion a3 = crit.getNewCriterion(MoviiUserPeer.LOGIN_NAME,
(Object) userKeyWord, Criteria.LIKE);
Criteria.Criterion a4 = crit.getNewCriterion(MoviiUserPeer.EMAIL,
(Object) userKeyWord,
Criteria.crit.add( a1.or( a2.or(a3.or(a4)) ));
************************
**CODE END
************************
is this really the right way, or am i completely wrong? It´s working though, but
in my eyes pretty uncool;)
Would be cool, if someone could tell me, if this is realisable more easy...
TIA
Alex Hepp
--
büro für design & informatik evamann gbr
alex hepp, dipl. informatiker (fh) | alkuinstr. 24 | d-54290 trier
tel.: 0651 991 61 03 | mobil: 0170 342 02 24
e-mail: hepp@evamann.de | www.evamann.de
Re: is this really the way to combine several or´s
Posted by "Henning P. Schmiedehausen" <hp...@intermeta.de>.
Alex Hepp <he...@evamann.de> writes:
>but that can become a quite difficult thing, when i want to produce an efficient
>and correct statement...
Torque is an O/R layer, not an SQL abstraction layer. If you want to
make sure that you get exactly that statement to your database, then
Torque might not be the right tool for you.
Regards
Henning
---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org
Re: is this really the way to combine several or´s ?
Posted by T E Schmitz <ma...@numerixtechnology.de>.
Hello Alex,
Alex Hepp wrote:
> thanks all,
>
> but that can become a quite difficult thing, when i want to produce an
> efficient and correct statement...
>
> is the following example statement possible with torque then? :
Yes, both examples are possible to configure with Torque. Just as you
did it using Criterions.
> SELECT * FROM my_users where (LAST_NAME="Hepp" AND FIRST_NAME="Alex") or
> LOGIN_NAME="heppa";
Criteria.Criterion a1 = crit.getNewCriterion( M.LAST_NAME,
"Hepp", Criteria.EQUAL);
Criteria.Criterion a2 = crit.getNewCriterion( M.FIRST_NAME,
"Alex", Criteria.EQUAL);
Criteria.Criterion a3 = crit.getNewCriterion( M.LOGIN_NAME,
"heppa", Criteria.EQUAL);
Criteria.Criterion a4 = crit.getNewCriterion( M.USER_ID,
"10023", Criteria.EQUAL);
crit.add(a3.or(a1.and(a2)));
> SELECT * FROM my_users where (LAST_NAME="Hepp" AND FIRST_NAME="Alex" or
> LOGIN_NAME="heppa") or USER_ID="10023";
crit.add a3.or(a4.or(a1.and(a2)));
I hope I got this right, if not shoot me down. But I'm sure you're
getting the gist of it.
>
> well, i feel like you can only do this with a well configured debug
> output;)
You just need to get your head round this and it will be quite simple.
While coding, keep org.apache.torque.util.BasePeer at DEBUG level and
you'll see the generated SQL. I have put some reasonably complicated
stuff together and it works fine.
I often paste a longish generated SELECT into my editor, format it with
some newlines and remove some columns, just to make it readable. I use
UltraEdit with code colouring, which makes the SQL statements easier to
read.
--
Seasons Greetings,
Tarlika
---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org
Re: is this really the way to combine several or´s ?
Posted by Alex Hepp <he...@evamann.de>.
thanks all,
but that can become a quite difficult thing, when i want to produce an efficient
and correct statement...
is the following example statement possible with torque then? :
SELECT * FROM my_users where (LAST_NAME="Hepp" AND FIRST_NAME="Alex") or
LOGIN_NAME="heppa";
or this:
SELECT * FROM my_users where (LAST_NAME="Hepp" AND FIRST_NAME="Alex" or
LOGIN_NAME="heppa") or USER_ID="10023";
well, i feel like you can only do this with a well configured debug output;)
Thanks to all for the help.
Kind Regards
Alex
T E Schmitz schrieb:
> Hallo Alex,
>
> This is exactly it. Particularly when using the same column in more than
> one condition you *have to* use Criterions and "or" or "and" them
> together. Criteria is based on a HashMap with the column name as the
> key. If you Criteria.add'ed a column condition it would overwrite the
> previously added condition (for the same column).
>
--
büro für design & informatik evamann gbr
alex hepp, dipl. informatiker (fh) | alkuinstr. 24 | d-54290 trier
tel.: 0651 991 61 03 | mobil: 0170 342 02 24
e-mail: hepp@evamann.de | www.evamann.de
Re: is this really the way to combine several or´s ?
Posted by T E Schmitz <ma...@numerixtechnology.de>.
Hallo Alex,
Alex Hepp wrote:
> what i want now is this:
> SELECT * FROM movii_user where (FIRST_NAME LIKE "%hello%" or LAST_NAME
> LIKE "%hello%" OR EMAIL LIKE "%hello%" or LOGIN_NAME="%hello%") AND
> IS_DELETED=0;
>
> ************************
> **CODE
> ************************
> Criteria crit = new Criteria();
> crit.add(MoviiUser.IS_DELETED,0);
>
> [...]
>
> Criteria.crit.add( a1.or( a2.or(a3.or(a4)) ));
> ************************
> **CODE END
> ************************
>
> is this really the right way, or am i completely wrong? It´s working
This is exactly it. Particularly when using the same column in more than
one condition you *have to* use Criterions and "or" or "and" them
together. Criteria is based on a HashMap with the column name as the
key. If you Criteria.add'ed a column condition it would overwrite the
previously added condition (for the same column).
--
Regards/Gruß,
Tarlika Elisabeth Schmitz
---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org
Re: is this really the way to combine several or ´s ?
Posted by Thomas Fischer <Fi...@seitenbau.net>.
Hi,
Alex Hepp <he...@evamann.de> schrieb am 22.12.2004 16:42:30:
> Hi List!
>
> I tried several hours now, to get a simple or to work... I must say, that
i
> really would appreciate a bit of documentation on that.
>
contributions to the docs are always welcome ;-) . But yes, documentation
is not very good in torque nowadays, but it takes a lot of time to write
good documentation...
>
> what i want now is this:
> SELECT * FROM movii_user where (FIRST_NAME LIKE "%hello%" or LAST_NAME
LIKE
> "%hello%" OR EMAIL LIKE "%hello%" or LOGIN_NAME="%hello%") AND
IS_DELETED=0;
>
> pretty simple. now, what i did is the following:
>
> ************************
> **CODE
> ************************
> // just for demonstration i replaced a String by "hello"
> userKeyWord = "%hello%";
>
> Criteria crit = new Criteria();
> crit.add(MoviiUser.IS_DELETED,0);
>
> Criteria.Criterion a1 = crit.getNewCriterion( MoviiUserPeer.LAST_NAME,
> (Object) (userKeyWord), Criteria.LIKE);
>
> Criteria.Criterion a2 = crit.getNewCriterion(MoviiUserPeer.FIRST_NAME,
> (Object)userKeyWord, Criteria.LIKE);
>
> Criteria.Criterion a3 = crit.getNewCriterion(MoviiUserPeer.LOGIN_NAME,
> (Object) userKeyWord, Criteria.LIKE);
>
> Criteria.Criterion a4 = crit.getNewCriterion(MoviiUserPeer.EMAIL,
> (Object) userKeyWord,
>
> Criteria.crit.add( a1.or( a2.or(a3.or(a4)) ));
> ************************
> **CODE END
> ************************
>
> is this really the right way, or am i completely wrong? It´s working
> though, but
> in my eyes pretty uncool;)
I do not know another way to create a multiple "or". Maybe there is another
way (I am not very experienced with the operators).
What kind of code would you like to write instead ? The problem is that the
correct operator precedence must be determined, in case the operators are
different. An example : In general (condition1 or (condition2 and
condition3)) is different to ((condition1 or condition2) and condition3).
And because the precedence should be the same on any db, Torque cannot rely
on the db's natural precedence , Torque has to enforce it. On the other
hand, the user should be able to create any precedence. Both is ensured
with the criterion.or function. Maybe not cool, but it works.
>
> Would be cool, if someone could tell me, if this is realisable more
easy...
> TIA
>
> Alex Hepp
Thomas
---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org