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: <email@hidden>
- Date: Thu, 21 Apr 2005 10:51:28 +0200
- Thread-topic: [ANN] ExistsInRelationshipQualifier: WHERE EXISTS (SELECT 1FROM A WHERE ...)
Kaj!
The problem is that in my code I did not account for flattened relationships. I skip right over the relationship which brings me to the Person entity. Thus the subselect works only on that entity. It should indeed create all intermediate joins.
Thus for now you would have to do without the flattened relationship if you would like to use the ExistsInRelationshipQualifier.
You will need the following relationships: Sporreundersokelse.besvart and Besvart.person. I don't think they need to be mapped to the EO for a database fetch.
The code would be something along the lines of:
Person person = // person with ID 1638;
NSArray args = new NSArray(new Object[] { person });
EOQualifier personQual = EOQualifier.qualifierWithQualifierFormat("person = %@", args);
EOQualifier existsQual = new ExistsInRelationshipQualifier("besvart", personQual);
EOFetchSpecification fs = new EOFetchSpecification("Sporreundersokelse", existsQual, null);
NSArray sporreundersokelser = editingContext().objectsWithFetchSpecification(fs);
I will look into the code of ExistsInRelationshipQualifier to see what I can do about flattened relationships. No promises though.
Pierre.
-----Original Message-----
From: Kaj Hejer [mailto:email@hidden]
Sent: Thursday, April 21, 2005 10:03 AM
To: Pierre Bernard
Cc: email@hidden
Subject: Re: [ANN] ExistsInRelationshipQualifier: WHERE EXISTS (SELECT
1FROM A WHERE ...)
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
**********************************************************************
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:
This email sent to email@hidden