Re: [ANN] ExistsInRelationshipQualifier: WHERE EXISTS (SELECT 1FROM A WHERE ...)
Re: [ANN] ExistsInRelationshipQualifier: WHERE EXISTS (SELECT 1FROM A WHERE ...)
- Subject: Re: [ANN] ExistsInRelationshipQualifier: WHERE EXISTS (SELECT 1FROM A WHERE ...)
- From: Kaj Hejer <email@hidden>
- Date: Thu, 21 Apr 2005 10:02:36 +0200
Hi!
Thank you for answering!
The sql did started with "SELECT 1 FROM". The "'x'" istead of "1" was
just me debugging and copying from the wrong source. The rest of the
sql is correct. Sorry for making this mistake!
The relationships you describ bellow is correct. The primary keys are
as follows:
Person:
Table: PERSON
PK: PERSONID
Sporreundersokelse:
Table: SPORREUND
PK: SPORREUNDID
Table BESVART_SKJEMA has a compund key of PERSONID and SPORREUNDID
Sporreundersokelse.personerBesvart is a flattend relationship to Person.
Yes, I'm trying to find every instance of Sporreundersokelse which has
a relationship to Person 1638 with the BESVART_SKJEMA as linkingtable.
I fetched both ExistsInRelationshipQualifier.jav,
ExistsInRelationshipQualifierSupport.java,
QualifierGenerationSupport.java and Qualifier.java from your website
yesterday with the same result.
I don't use QualifierUtilities since I couldn't find then on your
website. I fetch customQualifiers.tar.gz. On the 28. feb 2005 you sent
me an enhanced version on QualifierGenerationSupport that didn't use
QualifierUtilities (but I use the one I fetch from your website
yesterday). Can it have been introduced someting strange in this
version?
When counting the rows in the linkingtable from sql I find the
following:
SQL> select SPORREUNDID from besvart_skjema where personid=1638;
SPORREUNDID
-----------
2963
7143
I have made an even smaller testcase :) which looks like
NSArray args = new NSArray(new Object[] { new Integer(1638) });
EOQualifier sporreundersokelseQual =
EOQualifier.qualifierWithQualifierFormat("personID = %@", args);
EOQualifier existsQual = new
ExistsInRelationshipQualifier("personerBesvart",
sporreundersokelseQual);
EOFetchSpecification fs = new
EOFetchSpecification("Sporreundersokelse", existsQual, null);
Application.setSqlDebug(true);
NSArray sporreundersokelser =
editingContext().objectsWithFetchSpecification(fs);
Application.setSqlDebug(false);
Controller.writeLog("count with fetch: " +
sporreundersokelser.count());
sporreundersokelser =
editingContext().objectsWithFetchSpecification(fs);
fs = new EOFetchSpecification("Sporreundersokelse", null, null);
sporreundersokelser =
EOQualifier.filteredArrayWithQualifier(sporreundersokelser,
existsQual);
Controller.writeLog("count with in-memory: " +
sporreundersokelser.count());
The output I get is
log: 2005.04.21 09:20:38:835 CEST:count database: 405
log: 2005.04.21 09:20:55:588 CEST:count in-memory: 2
The sql debuginfo I get is
<JavaFoundation>[2005-04-21 09:20:36 CEST] <main> === Begin Internal
Transaction
<JavaFoundation>[2005-04-21 09:20:36 CEST] <main> evaluateExpression:
<com.webobjects.jdbcadaptor.OraclePlugIn$OracleExpression: "SELECT
t0.BESKRIVELSE_BOKMAL, t0.EKSTERNREF, t0.STATUS_ANONYMT,
t0.maks_besvarelser_person, t0.maks_besvarelser_skjema,
t0.SPORREUNDNAVN_BOKMAL, t0.RESPONDENTGRUPPEID, t0.skjemaID,
t0.DATO_BESVARELSE_FRA, t0.DATO_BESVARELSE_TIL, t0.SPORREUNDID,
t0.TEKST_EPOST_INVITASJON_BOKMAL, t0.TEKST_EPOST_PURRING_BOKMAL FROM
SPORREUND t0 WHERE EXISTS (SELECT 1 FROM PERSON A0 WHERE A0.PERSONID =
?)" withBindings: 1:1638(personID)>
<JavaFoundation>[2005-04-21 09:20:38 CEST] <main> 405 row(s) processed
<JavaFoundation>[2005-04-21 09:20:38 CEST] <main> === Commit Internal
Transaction
I checked
ExistsInRelationshipQualifierSupport.sqlStringForSQLExpression and
printed out some of the objects:
log: 2005.04.21 09:59:24:956 CEST:eirQualifier: (personerBesvart
CONTAINS ((personID = 1638))
log: 2005.04.21 09:59:24:984 CEST:relationship: <EORelationship
personerBesvart 16240625>
log: 2005.04.21 09:59:25:39 CEST:entity: <EOEntity Sporreundersokelse
7660418>
log: 2005.04.21 09:59:25:40 CEST:keyPath: personerBesvart
log: 2005.04.21 09:59:25:41 CEST:subFetch: <class
com.webobjects.eocontrol.EOFetchSpecification(entityName=Person,
qualifier=(personID = 1638),
isDeep=true, usesDistinct=false,
sortOrdering=null,
hints=null,
_prefetchingRelationshipKeyPaths = null)>
log: 2005.04.21 09:59:25:82 CEST:subExpression:
<com.webobjects.jdbcadaptor.OraclePlugIn$OracleExpression: "SELECT
t0.PERSONID FROM PERSON t0 WHERE t0.PERSONID = ?" withBindings:
1:1638(personID)>
In my Application constructor I have
EOQualifierSQLGeneration.Support.setSupportForClass(
new ExistsInRelationshipQualifierSupport(),
ExistsInRelationshipQualifier.class);
Sorry about the PGP.sig!
-Kaj :)
On 20. apr 2005, at 10.24, <email@hidden> wrote:
Kaj!
You might want to make sure you've got the latest version of
ExistsInRelationshipQualifier and QualifierUtilities as available on
my web site.
I am a bit surprised by the SQL you are seeing. The code generated by
ExistsInRelationshipQualifier always starts with "SELECT 1 FROM". That
part is hard-coded. Or did you accidentliy mix up the 2 SQL
instructions quoted in your email.
Could you give me more details on your model. E.g. what are the
entities and their relationship. What I understood so far is:
Person <----------------->> BESVART_SKJEMA
<<----------------------------------> Sporreundersokelse
<- personerBesvart
What are the primary keys of those entities? Are there flattened
relationships?
What are you trying to achive? Are you trying to find every instance
of Sporreundersokelse which has a relationship to Person 1638 ?
You would be aiming for:
SELECT * from Sporreundersokelse t0
WHERE EXISTS (
SELECT 1 FROM BESVART_SKJEMA A0
WHERE A0.PERSONID = 1638
AND t0.SPORREUNDID = a0.SPORREUNDID);
That's exactly what you would get with my understanding of the
situation. If you don't we'll have to step through the code of
ExistsInRelationshipQualifierSupport.sqlStringForSQLExpression().
Pierre.
P.S.: Please refrain from using you PGP.sig when replying. The company
mail filter thinks its a script or worm and chokes.
**********************************************************************
This email and any files transmitted with it are intended solely for
the use of the individual or entity to whom they are addressed.
If you have received this email in error please notify the sender
of this message. (email@hidden)
This email message has been checked for the presence of computer
viruses; however this protection does not ensure this message is
virus free.
Banque centrale du Luxembourg; Tel ++352-4774-1; http://www.bcl.lu
**********************************************************************
_______________________________________________
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list (email@hidden)
Help/Unsubscribe/Update your Subscription:
email@hidden
This email sent to email@hidden
_______________________________________________
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list (email@hidden)
Help/Unsubscribe/Update your Subscription:
This email sent to email@hidden