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 Michael Burschik <Bu...@lotto-berlin.de> on 2004/04/08 15:24:31 UTC

Can I do this with Criteria?

Dear list members,

can I do this with Criteria?

select t1.something from table1 as t1, table2 as t2
   where t1.id=t2.id
   and t1.value=somevalue
   and t2.value=someothervalue

I tried the addAlias method, but this did not work as expected. Any
suggestion would be greatly appreciated.

Regards and Happy Easter

Michael Burschik


---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org

Re: Can I do this with Criteria?

Posted by sk...@engr.uky.edu.
Hi,

   Did you try setting an Alias in the <project>-schema.xml ? I remember
seeing some option there. It's surprising that addAlias didn't work. I think
may be you should use addAlias for each Criteria object you use in the
expression (which is against the whole point of using the Alias). I'm a newbie 
and I'm just guess some stuff. To Pros out there, sorry if I'm wrong, and 
correct me if I'm wrong.

-Sarav

Quoting Michael Burschik <Bu...@lotto-berlin.de>:

> Dear list members,
> 
> can I do this with Criteria?
> 
> select t1.something from table1 as t1, table2 as t2
>    where t1.id=t2.id
>    and t1.value=somevalue
>    and t2.value=someothervalue
> 
> I tried the addAlias method, but this did not work as expected. Any
> suggestion would be greatly appreciated.
> 
> Regards and Happy Easter
> 
> Michael Burschik
> 
> 
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
> For additional commands, e-mail: torque-user-help@db.apache.org
> 
> 




---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org

Calculated values

Posted by Alexandru Dovlecel <al...@siemens.com>.
Hi,

I have a table X connected with a toMany relation to an XHistory table. How
can I obtain the first and the last XHistory.changeDate field for each X in
my select? This would be equivalent with getting the "creation date" and
"last changed date".

I want to select all X that belongs to user with ID = 10, and also to
include those two history entries for each X (the first history and the last
history).

In plain sql would be (I think...)
initdate as MIN( XHistory.dateOfChange ) , chgDate as MAX(
XHistory.dateOfChange ) GROUP BY X.id .

How to do this in Torque???
Alex


---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org

Re: Can I do this with Criteria?

Posted by Kostyantyn Shchekotykhin <ko...@ifit.uni-klu.ac.at>.
Hallo Michael,
I'm not sure but i'll try
	Criteria criteria = new Criteria();
         // create aliases
         criteria.addAlias("t1", TablePeer.TABLE_NAME);
         criteria.addAlias("t2", TablePeer.TABLE_NAME);
         // add what we want to select
         criteria.addSelectColumn(TablePeer.SOMETHING);
         // where clause
         criteria.addJoin("t1.id", "t2.id");
         criteria.add("t1.value", (Object)"somevalue");
         criteria.add("t2.value", (Object)"somevalue");

you can write instead "t1.value" more sophisticated solution like
String alias1 = "t1";
criteria.add(alias1 + "." + extractColumnName(TablePeer.VALUE), (Object)"somevalue");

Regards,
Kostya

Michael Burschik wrote:

> Now, you might think I don't really need the alias feature, but that is
> because I made a slight mistake in my example. What I actually need to do is
> this:
> 
>   select t1.something from TABLE as t1, TABLE as t2
>     where t1.id=t2.id
>     and t1.value=somevalue
>     and t2.value=someothervalue
> 
> You see, I use the same table more than once in order to avoid sub-selects.
> 
> Since I wasn't able to figure out a way to do this with Criteria, I tried to
> do without by piecing together an SQL statement and using the Torque
> connection to submit it to the database. Strangely enough, the select took
> very long to complete (something like three minutes) and the result was not
> correct, although the same statement typed at the command prompt (mysql) was
> blindingly fast and delivered the correct results. Can anyone suggest a
> reason for this behaviour?
> 
> Regards
> 
> Michael Burschik
> 
> 
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
> For additional commands, e-mail: torque-user-help@db.apache.org
> 

---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org

RE: Can I do this with Criteria?

Posted by Michael Burschik <Bu...@lotto-berlin.de>.
On Thursday, 8 April 2004, Bogdan Vatkov wrote:
> Could you explain iun details what you need to select ...
> because as far as I can see in your statement you will
> have one and the same row doubled
> t1.id = t2.id
> says when row 5 from table A is row 5 from A
> at the same time
> "value" colunm from that row to has different values
>  ...that is nosense
>
> of course this is valid only if .id is a primary key
> is that your select exactly ?
>
> regards,
> bogdan

I have a table of lottery results. Each row contains the ID of the draw, the
ID of the number (e.g. first number drawn) and the "value", i.e. the actual
number drawn. Thus, the ID is not the primary key of the table. What I need
to do is to select a draw by checking whether it includes certain numbers.

Regards

Michael Burschik



---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org

Re: Can I do this with Criteria?

Posted by Bogdan Vatkov <bv...@globaltech-bg.com>.
Could you explain iun details what you need to select ...
because as far as I can see in your statement you will
have one and the same row doubled
t1.id = t2.id
says when row 5 from table A is row 5 from A
at the same time
"value" colunm from that row to has different values
 ...that is nosense

of course this is valid only if .id is a primary key
is that your select exactly ?

regards,
bogdan

----- Original Message -----
From: "Michael Burschik" <Bu...@lotto-berlin.de>
To: "'Apache Torque Users List'" <to...@db.apache.org>
Sent: Thursday, April 08, 2004 7:02 PM
Subject: RE: Can I do this with Criteria?


> Now, you might think I don't really need the alias feature, but that is
> because I made a slight mistake in my example. What I actually need to do
is
> this:
>
>   select t1.something from TABLE as t1, TABLE as t2
>     where t1.id=t2.id
>     and t1.value=somevalue
>     and t2.value=someothervalue
>
> You see, I use the same table more than once in order to avoid
sub-selects.
>
> Since I wasn't able to figure out a way to do this with Criteria, I tried
to
> do without by piecing together an SQL statement and using the Torque
> connection to submit it to the database. Strangely enough, the select took
> very long to complete (something like three minutes) and the result was
not
> correct, although the same statement typed at the command prompt (mysql)
was
> blindingly fast and delivered the correct results. Can anyone suggest a
> reason for this behaviour?
>
> Regards
>
> Michael Burschik
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
> For additional commands, e-mail: torque-user-help@db.apache.org
>


---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org

RE: Can I do this with Criteria?

Posted by Michael Burschik <Bu...@lotto-berlin.de>.
Now, you might think I don't really need the alias feature, but that is
because I made a slight mistake in my example. What I actually need to do is
this:

  select t1.something from TABLE as t1, TABLE as t2
    where t1.id=t2.id
    and t1.value=somevalue
    and t2.value=someothervalue

You see, I use the same table more than once in order to avoid sub-selects.

Since I wasn't able to figure out a way to do this with Criteria, I tried to
do without by piecing together an SQL statement and using the Torque
connection to submit it to the database. Strangely enough, the select took
very long to complete (something like three minutes) and the result was not
correct, although the same statement typed at the command prompt (mysql) was
blindingly fast and delivered the correct results. Can anyone suggest a
reason for this behaviour?

Regards

Michael Burschik


---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org