Re: Help with Search, may require outer joins??
Re: Help with Search, may require outer joins??
- Subject: Re: Help with Search, may require outer joins??
- From: Pierre Frisch <email@hidden>
- Date: Fri, 13 May 2005 01:57:44 -0700
Hi Owen,
I think you are reaching one of the undocumented limits of EOF query
generation. If you examine the generated SQL you will see that EOF
did not correctly traverse the many to many relationship if it is
materialized by a real object. You have two solutions: if the volume
is not too big do it in two or three fetches i.e. fetch the
intermediate tables first and then the publication, Alternatively I
can send you a set of classes that can code a subquery and an IN
qualifier. They work in all cases I have tried them but I cannot make
any guarantee and the documentation is poor -:).
Pierre
On 12-May-05, at 8:51 PM, Owen McKerrow wrote:
Hi All,
Im having some trouble with a query to my database and was
wondering if anyone could point me in the right direction.
Its a websites for entering details about academic publications. So
the order in which the authors appears is important, i.e. there is
an order and when every the publication is shown the authors need
to be displayed in that order. This part works correctly. Its
getting the publications which belong to a given user is where it
gets tricky.
OK first the tables
Person <--->>Person Name ( in case someone ever changes their name,
we need to remember their old name as that was what the book was
published under)
Publication <--->> AuthorPubs <<---> Person Name ( A publication
has many authors, which belong to a persons name. In AuthorPubs I
store the order of the authors )
Publication <--->> EditorPubs <<---> Person Name ( A publication
has many editors, which belong to a persons name. In EditorPubs I
store the order of the editors )
Person <--->> Publication ( A Publication has 1 owner, i.e the user
who entered the publication)
So what I want to do is ask the DB for all Publications belonging
to a particular person. So any publication where they are either
the owner, or an editor or an author.
I've made my fetch spec in EOModler so that
(personAuthorPubs.personName.person = $owner) OR
(personEditorPubs.personName.person = $owner) OR (person = $owner)
However this is only returning publications for which I am the
owner, not ones where I am an author or an editor but not the
owner. If I remove the (person = $owner) attribute it only returns
publications where I am the only editor or author, if there is more
than 1 it doesn't come back.
Now I think this has to do with outer joins, but Im rusty when it
comes to such things. Ive read up on teh web and in my Db book, but
Im still not sure Im doing the right thing. As in I think I need
the relationship from Publication to AuthorPubs and EditorPubs to
be Left Outer joins, but when I try this I still get the same results.
Any ideas ?
Im using WO 5.2.3 and Openbase 8 ( sorry to everyone thats on both
mailing lists who gets this twice)
Owen McKerrow
WebMaster, emlab
http://emlab.uow.edu.au
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
- - - - - - - - - -
People who prefer typing to pointing then seem to prefer acronyms
to save typing :-)
-Denis Stanton, On people using Command Line Interfaces
_______________________________________________
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list (email@hidden)
Help/Unsubscribe/Update your Subscription:
40spearway.com
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