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, [EMAIL PROTECTED] 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" <[EMAIL PROTECTED]> 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: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
--
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
