Re: pyDO 2 alpha

On Fri, Apr 22, 2005 at 05:10:30PM -0700, Gregory Brauer wrote:
> 
> I just realized what I needed to be a little more clear in
> my question.
> 
> What I am looking for is a way to do server side ordering *across*
> a join.  For instance "get some items from table A sorted by field
> B.foo of the items in table B that relate to the items in table A".
> 
> Currently with PyDO1 when running a query with n results you end up
> having to do n+1 queries.  With direct access to ORDER BY you can do
> it in a single query.  In order to make this work with PyDO1 I have
> had to implement my own getSome method that takes a raw "I hope you
> know what you are doing" query which I carefully formulate so that
> PyDO will be able to parse the query result.  If implementing ORDER
> BY is beyond the scope of PyDO2 for now, would it be possible to put
> in a standardized getSomeSpecial() method that allows those who
> understand what they are doing to pass in your own complex queries
> without having to hack every release of PyDO that comes out?

If the sql you want is 

  SELECT A.f1, A.f2, A.f3 FROM A, B 
  WHERE A.f1=B.f1 
  ORDER BY B.f2

that is still a little problematic or at least awkward to get out of
getSome(), because of the necessity of adding table B to the table
expression.  At the moment you'd need to subclass _baseSelect() to add
it.  Then you'd need to decide how you want to pass the list of tables
that should get added to the table expression part of the select
query.  If it was constant for the class, you could hard-code it and
this approach would work.  Another possibility is to run the query
with a custom method that calls getDBI().execute() and then build the
PyDO instances yourself from the result.

If adding extra tables were something that people really needed, I
suppose getSome() could grow another keyword argument, extraTables,
which would be passed to _baseSelect().  Then the table list could be
controlled at query-time.  This is probably the enhancement that you
want.  Am I right?

BTW, I see that postgresql 8.01 will permit you to run a query like

  SELECT * FROM A
  WHERE A.id=B.id
  ORDER BY B.bough_type DESC

-- although it does issue a NOTICE 'adding missing FROM-clause entry
for table "B"'.  

Right now, getSome() in PyDO2 supports passing it a string + bind
variables:

   A.getSome("A.id=B.id ORDER BY B.bough_type DESC")

If you aren't using a string, you can use "order", "offset", and
"limit" keyword arguments:

  A.getSome(EQ(FIELD('A.id'), FIELD('B.id')), order='B.bough_type DESC')

The arrayfetch method (which will probably change a bit) could also work:

   arrayfetch((A,B.project(('id',))), "A.id=B.id ORDER BY B.bough_type DESC")

You would, however, get 2-tuples of A and the B projection (with just
one column in this case).  

And then there are the new Join classes (not in the first alpha, but
in SVN):

   InnerJoin(A, 
             B.project(('id',)), 
             using=('id',)).getSome(order='B.bough_type DESC')

Again, you get pairs of instances.

Cheers,

js

-- 
Jacob Smullyan

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (GNU/Linux)

iD8DBQFCabimuqamFyFXXLIRAnTmAKDI0jn0TPhl5sBP93x3inqm1xXCDACgpRS3
6b6tM0Aiy7EtaPLRedIOYnk=
=0af1
-----END PGP SIGNATURE-----