• Open Menu Close Menu
  • Apple
  • Shopping Bag
  • Apple
  • Mac
  • iPad
  • iPhone
  • Watch
  • TV
  • Music
  • Support
  • Search apple.com
  • Shopping Bag

Lists

Open Menu Close Menu
  • Terms and Conditions
  • Lists hosted on this site
  • Email the Postmaster
  • Tips for posting to public mailing lists
Re: [ANN] ExistsInRelationshipQualifier: WHERE EXISTS (SELECT 1FROM A WHERE ...)
[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

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
References: 
 >RE: [ANN] ExistsInRelationshipQualifier: WHERE EXISTS (SELECT 1FROM A WHERE ...) (From: <email@hidden>)

  • Prev by Date: Re: WOHyperlink secure binding and insecure domain
  • Next by Date: RE: [ANN] ExistsInRelationshipQualifier: WHERE EXISTS (SELECT 1FROM A WHERE ...)
  • Previous by thread: RE: [ANN] ExistsInRelationshipQualifier: WHERE EXISTS (SELECT 1FROM A WHERE ...)
  • Next by thread: RE: [ANN] ExistsInRelationshipQualifier: WHERE EXISTS (SELECT 1FROM A WHERE ...)
  • Index(es):
    • Date
    • Thread