Re: Couple of questions...

John Lenton <[email protected]> writes:
> On Tue, Apr 20, 2004 at 09:46:39AM -0700, Aaron Freeman wrote:
[...]
> > How would I get writers by publisher?
> >
> > More specifically, is there an elegant way to do a nested join in a single
> > query, or do I just have to do a single writer-book join and then iterate
> > over the resultset for the second join?
> 
> I don't think so. I'd do it with
> 
>    [ i.valueForKeyPath('books.author') for i in ec.fetch('Publisher') ]
> 
> which is clean enough for me. I guess what you'd want is for
> valueForKeyPath to be callable on a class... :)


  Yes, that's another approach indeed. But while I'm a fervent defender
for KeyValueCoding ;)) in such cases one should be careful. Here are
some thoughts:

  - if the database have a lot of books, the KVC approach will load them
    all in memory: KVC traverses relationships at the object level, and
    in this case this means fetching Books published by every Publisher,
    then these Books' Authors. This is probably the worst case one can
    get. That's probably why you were asking for a callable vFKP on a
    class, weren't you?

  - In the resulting lists, if an author has published N books for a
    publisher, the corresponding list will receive N duplicates for that
    author.

The one posted earlier:
  >>> ec.fetch('Writer', 'books.publisher.bizName=="P1"')
does not have these possible drawbacks.


However and admittedly the dotted notation as it is designed for now can
only handle one kind of semantics for to-many relationships: for
example, there is no way to ask for authors whose books were published
by one single publisher only.

  In other words, then you'll have to use KVC or plain python, traverse
  the relationships and check properties, or, hmmm, get back to plain
  SQL w/ things like:

    SELECT DISTINCT t0.ID, t0.LAST_NAME, t0.FIRST_NAME, t0.AGE,
                    t0.FK_WRITER_ID, t0.BIRTHDAY
    FROM WRITER t0
    WHERE NOT EXISTS (SELECT * FROM BOOK,WRITER,PUBLISHER
                      WHERE WRITER.ID=t0.ID
                            AND WRITER.ID=BOOK.FK_WRITER_ID 
                            AND BOOK.FK_PUBLISHER=PUBLISHER.ID
                            AND PUBLISHER.BIZ_NAME!='P1')

  just to get the authors who have no other publisher than P1.


This makes me think that it could be nice to have something like

  >>> ec.fetch('Writer', 'NOT EXISTS(books.publisher.bizName!="P1")'

so that we could avoid having to write plain SQL just like above (yerk:)


        What do you all think?

-- Sébastien.



-------------------------------------------------------
This SF.Net email is sponsored by: IBM Linux Tutorials
Free Linux tutorial presented by Daniel Robbins, President and CEO of
GenToo technologies. Learn everything from fundamentals to system
administration.http://ads.osdn.com/?ad_id70&alloc_id638&op=click