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 "Pfiester, Jan" <ja...@ict.fraunhofer.de> on 2006/10/16 17:40:05 UTC
Problems to set up a subquery
Hi everyone,
I ran into problems to setup the following query using criteria:
SELECT DISTINCT * FROM kundenadresse
WHERE kundenadress_id NOT IN(
SELECT kundenadress_id FROM adressenreferenz
);
Could someone please give me some help to come up with a proper solution?
Hints for further reading on this topic would also be very much appreciated!
Many thanks in advance,
Jan
---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org
Re: Problems to set up a subquery
Posted by Thomas Vandahl <tv...@apache.org>.
Thomas Fischer wrote:
> Hm, writing SQL is one of the the things that Torque wants to avoid. One
> of the reasons for this is portability between databases. Though Torque
> is certainly not perfectly portable, it is much better than plain SQL.
[...]
> So in my eyes, one should think twice before using custom SQL. There are
> cases where it cannot be avoided (see below), but I would think twice
> before using it.
I second that. It is sometimes surprising what *can* be done with the
Torque Criteria. Custom SQL breaks the basic idea of Torque.
Bye, Thomas.
---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org
Re: Problems to set up a subquery
Posted by Αντώνης Λεμπέσης <an...@di.uoa.gr>.
I agree with you, but there are a few cases that there is no alternative:
delete all records from a table ( FooPeer.doDelete(new Criteria())
will not work). We had to iterate over the list of all records and call
delete for each one.
complex queries with subqueries and/or multiple references to the
same column. It can be done using Criteria, but some times I find it
much easier to create the query by hand, especially if many parts of the
query are optional.
Antonis
Thomas Fischer wrote:
> Hm, writing SQL is one of the the things that Torque wants to avoid.
> One of the reasons for this is portability between databases. Though
> Torque is certainly not perfectly portable, it is much better than
> plain SQL. Another reason is that the compiler checks if the columns
> you use in your query is still in your database model (it will not
> find the relevant constants if a column is removed or renamed and the
> model is regenerated)
> (of cousre, the constants can also be used to build SQL to have the
> same effect, but this is quite a hassle).
>
> So in my eyes, one should think twice before using custom SQL. There
> are cases where it cannot be avoided (see below), but I would think
> twice before using it.
>
> Thomas
>
> On Tue, 17 Oct 2006, antleb@di.uoa.gr wrote:
>
>> Hello,
>>
>> One thing that I have found very useful in complex queries is to
>> bypass the
>> Criteria objects.
>> All you have to do is to write the SQL query, execute it using
>> BasePeer.executeQuery() to get a List of Records and then create a
>> list of
>> persistent objects using the populateObjects of the corresponding
>> Peer class.
>> In this case, you can use:
>>
>> String query = "SELECT DISTINCT * FROM kundenadresse WHERE
>> kundenadress_id
>> NOT IN (SELECT kundenadress_id FROM adressenreferenz)";
>> List kundenadresses =
>> KundenadressePeer.populateObjects(BasePeer.executeQuery(query));
>>
>> It works fine if you have a query of the form: "select * from
>> foo...", ie you
>> select all the columns of a single table. I don't have the source
>> code handy to
>> check if this query would work (my feeling is that this would work
>> also):
>>
>> String query = "SELECT * FROM FOO f, BAR b WHERE f.b = b.f AND ...";
>> List list = FooPeer.populateObjects(BasePeer.executeQuery(query));
>>
>> Enjoy,
>> Antonis
>>
...
---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org
RE: Problems to set up a subquery
Posted by Thomas Fischer <tf...@apache.org>.
Hm, writing SQL is one of the the things that Torque wants to avoid. One
of the reasons for this is portability between databases. Though Torque is
certainly not perfectly portable, it is much better than plain SQL.
Another reason is that the compiler checks if the columns you use in your
query is still in your database model (it will not find the relevant
constants if a column is removed or renamed and the model is regenerated)
(of cousre, the constants can also be used to build SQL to have the same
effect, but this is quite a hassle).
So in my eyes, one should think twice before using custom SQL. There are
cases where it cannot be avoided (see below), but I would think twice
before using it.
Thomas
On Tue, 17 Oct 2006, antleb@di.uoa.gr wrote:
> Hello,
>
> One thing that I have found very useful in complex queries is to bypass the
> Criteria objects.
> All you have to do is to write the SQL query, execute it using
> BasePeer.executeQuery() to get a List of Records and then create a list of
> persistent objects using the populateObjects of the corresponding Peer class.
> In this case, you can use:
>
> String query = "SELECT DISTINCT * FROM kundenadresse WHERE kundenadress_id
> NOT IN (SELECT kundenadress_id FROM adressenreferenz)";
> List kundenadresses =
> KundenadressePeer.populateObjects(BasePeer.executeQuery(query));
>
> It works fine if you have a query of the form: "select * from foo...", ie you
> select all the columns of a single table. I don't have the source code handy to
> check if this query would work (my feeling is that this would work also):
>
> String query = "SELECT * FROM FOO f, BAR b WHERE f.b = b.f AND ...";
> List list = FooPeer.populateObjects(BasePeer.executeQuery(query));
>
> Enjoy,
> Antonis
>
>> Hi,
>>
>> Subqueries are implemented and documented in the svn version(3.2.1-dev),
>> but not in Torque 3.2.0.
>> Either build Torque from svn or, if you want to use Torque 3.2.0. you can
>> code the subquery as custom criteria (see
>> http://db.apache.org/torque/releases/torque-3.2
>> /runtime/reference/read-from-db.html#Using_the_CUSTOM_modifier_to_use_custom_SQL)
>> (you can also build the subquery via another Criteria and its
>> toString()-method if you do not want to hand-code it).
>>
>> Thomas
>>
>> "Pfiester, Jan" <ja...@ict.fraunhofer.de> schrieb am 16.10.2006
>> 17:40:05:
>>
>>> Hi everyone,
>>>
>>> I ran into problems to setup the following query using criteria:
>>>
>>> SELECT DISTINCT * FROM kundenadresse
>>> WHERE kundenadress_id NOT IN(
>>> SELECT kundenadress_id FROM adressenreferenz
>>> );
>>>
>>> Could someone please give me some help to come up with a proper solution?
>>> Hints for further reading on this topic would also be very much
>> appreciated!
>>>
>>> Many thanks in advance,
>>> Jan
>>>
>>> ---------------------------------------------------------------------
>>> 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
>>
>>
>
>
> --
>
>
>
> ---------------------------------------------------------------------
> 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: Problems to set up a subquery
Posted by an...@di.uoa.gr.
Hello,
One thing that I have found very useful in complex queries is to bypass the
Criteria objects.
All you have to do is to write the SQL query, execute it using
BasePeer.executeQuery() to get a List of Records and then create a list of
persistent objects using the populateObjects of the corresponding Peer class.
In this case, you can use:
String query = "SELECT DISTINCT * FROM kundenadresse WHERE kundenadress_id
NOT IN (SELECT kundenadress_id FROM adressenreferenz)";
List kundenadresses =
KundenadressePeer.populateObjects(BasePeer.executeQuery(query));
It works fine if you have a query of the form: "select * from foo...", ie you
select all the columns of a single table. I don't have the source code handy to
check if this query would work (my feeling is that this would work also):
String query = "SELECT * FROM FOO f, BAR b WHERE f.b = b.f AND ...";
List list = FooPeer.populateObjects(BasePeer.executeQuery(query));
Enjoy,
Antonis
> Hi,
>
> Subqueries are implemented and documented in the svn version(3.2.1-dev),
> but not in Torque 3.2.0.
> Either build Torque from svn or, if you want to use Torque 3.2.0. you can
> code the subquery as custom criteria (see
> http://db.apache.org/torque/releases/torque-3.2
> /runtime/reference/read-from-db.html#Using_the_CUSTOM_modifier_to_use_custom_SQL)
> (you can also build the subquery via another Criteria and its
> toString()-method if you do not want to hand-code it).
>
> Thomas
>
> "Pfiester, Jan" <ja...@ict.fraunhofer.de> schrieb am 16.10.2006
> 17:40:05:
>
> > Hi everyone,
> >
> > I ran into problems to setup the following query using criteria:
> >
> > SELECT DISTINCT * FROM kundenadresse
> > WHERE kundenadress_id NOT IN(
> > SELECT kundenadress_id FROM adressenreferenz
> > );
> >
> > Could someone please give me some help to come up with a proper solution?
> > Hints for further reading on this topic would also be very much
> appreciated!
> >
> > Many thanks in advance,
> > Jan
> >
> > ---------------------------------------------------------------------
> > 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
>
>
--
---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org
Re: inputValidator usage
Posted by Thomas Fischer <tf...@apache.org>.
Sorry, did not see that Greg already responded to this. Please disregard
my last email.
Thomas
On Tue, 17 Oct 2006, Thomas Fischer wrote:
> I have not used this tag myself nor did I hear of anybody using it. I am not
> even sure it works. Sorry, you have to finf out yourself, it seems :-(
>
> If you have found out what it does, can you please tell us? Thanks.
>
> Thomas
>
> On Mon, 16 Oct 2006, Alvaro Coronel wrote:
>
>> Hello everybody.
>>
>> Can anyone please provide a useful pointer or hint about the usage of the
>> inputValidator tag?
>>
>> Advice not only about schema definition but also at code level would be
>> appreciated.
>>
>> TIA
>> �lvaro Coronel.
>>
>>
>> ---------------------------------
>> Get your own web address for just $1.99/1st yr. We'll help. Yahoo! Small
>> Business.
>
Re: inputValidator usage
Posted by Thomas Fischer <tf...@apache.org>.
I have not used this tag myself nor did I hear of anybody using it.
I am not even sure it works. Sorry, you have to finf out yourself, it
seems :-(
If you have found out what it does, can you please tell us? Thanks.
Thomas
On Mon, 16 Oct 2006, Alvaro Coronel wrote:
> Hello everybody.
>
> Can anyone please provide a useful pointer or hint about the usage of the inputValidator tag?
>
> Advice not only about schema definition but also at code level would be appreciated.
>
> TIA
> �lvaro Coronel.
>
>
> ---------------------------------
> Get your own web address for just $1.99/1st yr. We'll help. Yahoo! Small Business.
RE: inputValidator usage
Posted by Greg Monroe <Gr...@DukeCE.com>.
This dates back to the V1.0 DTD in CVS. The only place
that this is used is to set the String InputValidator
property of the ColumnMap class in the 3.2.1-dev version.
So, if you want to use it, you could probably set it
to your own validation /text to object type conversion
class, then when processing input do something like:
setColumnWithValidation( BaseObject record,
String colName,
String value)
throws Exception {
validatorClassName = record.getTableMap()
.getColumn(PeerName).getInputValidator());
Class vClass = Class.forName(validatorClassName);
Validate v = (Validate) vClass.newInstance();
setByName(colName, v.convert(value) );
}
> -----Original Message-----
> From: Alvaro Coronel [mailto:alvarocoronel67@yahoo.com]
> Sent: Monday, October 16, 2006 12:50 PM
> To: Apache Torque Users List
> Subject: inputValidator usage
>
> Hello everybody.
>
> Can anyone please provide a useful pointer or hint about the
> usage of the inputValidator tag?
>
> Advice not only about schema definition but also at code
> level would be appreciated.
>
> TIA
> Álvaro Coronel.
>
>
> ---------------------------------
> Get your own web address for just $1.99/1st yr. We'll help.
> Yahoo! Small Business.
>
Duke CE Privacy Statement
Please be advised that this e-mail and any files transmitted with it are confidential communication or may otherwise be privileged or confidential and are intended solely for the individual or entity to whom they are addressed. If you are not the intended recipient you may not rely on the contents of this email or any attachments, and we ask that you please not read, copy or retransmit this communication, but reply to the sender and destroy the email, its contents, and all copies thereof immediately. Any unauthorized dissemination, distribution or copying of this communication is strictly prohibited.
---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org
inputValidator usage
Posted by Alvaro Coronel <al...@yahoo.com>.
Hello everybody.
Can anyone please provide a useful pointer or hint about the usage of the inputValidator tag?
Advice not only about schema definition but also at code level would be appreciated.
TIA
Álvaro Coronel.
---------------------------------
Get your own web address for just $1.99/1st yr. We'll help. Yahoo! Small Business.
RE: Problems to set up a subquery
Posted by Thomas Fischer <fi...@seitenbau.net>.
Hi,
Subqueries are implemented and documented in the svn version(3.2.1-dev),
but not in Torque 3.2.0.
Either build Torque from svn or, if you want to use Torque 3.2.0. you can
code the subquery as custom criteria (see
http://db.apache.org/torque/releases/torque-3.2
/runtime/reference/read-from-db.html#Using_the_CUSTOM_modifier_to_use_custom_SQL)
(you can also build the subquery via another Criteria and its
toString()-method if you do not want to hand-code it).
Thomas
"Pfiester, Jan" <ja...@ict.fraunhofer.de> schrieb am 16.10.2006
17:40:05:
> Hi everyone,
>
> I ran into problems to setup the following query using criteria:
>
> SELECT DISTINCT * FROM kundenadresse
> WHERE kundenadress_id NOT IN(
> SELECT kundenadress_id FROM adressenreferenz
> );
>
> Could someone please give me some help to come up with a proper solution?
> Hints for further reading on this topic would also be very much
appreciated!
>
> Many thanks in advance,
> Jan
>
> ---------------------------------------------------------------------
> 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