[SQL-CVS] [ sqlobject-Patches-1410527 ] allow the use of foreign keys in selects (small jewel)

[SourceForge.net <no...@so...>]

Patches item #1410527, was opened at 2006-01-20 10:42
Message generated for change (Comment added) made by phd
You can respond by visiting: 
https://sourceforge.net/tracker/?func=detail&atid=540674&aid=1410527&group_id=74338

Please note that this message will contain a full copy of the comment thread,
including the initial issue submission, for this request,
not just the latest update.
Category: None
Group: None
Status: Open
Resolution: None
Priority: 5
Submitted By: Pau Aliagas (linux4u)
Assigned to: Nobody/Anonymous (nobody)
Summary: allow the use of foreign keys in selects (small jewel)

Initial Comment:

Many times you need to make a query that needs to
filter using the foreign key. This should be trivial to
do: just adding the foreign table or foreign table
SHOULD work. But it does not.

Better see the example:

class handset_brand(SQLObject):
    brand = StringCol(alternateID = True, length = 50)
    handsets = SQLMultipleJoin('handset')

class handset(SQLObject):
    handset_brand = ForeignKey('handset_brand')
    handset_made_in = ForeignKey('country')
    model = StringCol(alternateID = True, length = 25)

class country(SQLObject):
    name StringCol(alternateID = True, length = 50)
    isocode StringCol(alternateID = True, length = 3)

Query 1
-------
I want to query the handsets of one concrete brand.

* solution 1
 ----------
Now the best way to do it would be:

handset_brands.get(4).handsets.filter(handset.q.model
== "mymodel")

but you NEED to define the MultipleJoin.

* solution 2
  ----------
You can use the dotq syntax:

handset.select(handset.q.handset_brandID==4)

For me the main problem is the you have to use the
foreign key name defined in Style, so if you change the
Style it would stop working. That's bad.

* expected behaviour
  ------------------
I'd like to be able to do:

handset.select(brandID = 4)

or

b = brand.get(4)
handset.select(brand = b)

One of both would be GREAT, but both would be AWSOME.
This is the natural way of thinking.

Query 2
-------
What if if want to filter using two foreign keys?
For instance to query the handsets of one concrete
brand made in one country.


* solution 1
  ----------
It would NOT work, you cannot query directly using both
foreign tables.

* solution 2
  ----------
You can use the dotq syntax:

handset.select(AND(handset.q.handset_brandID==4,
handset.q.countryID==1))

Again, you have to use the foreign key name defined in
Style. I try to avoid it, it feels like there SHOULD be
a better way.

* expected behaviour
  ------------------
I'd like to be able to do:

handset.select(brandID = 4, countrID = 1)

or

b = brand.get(4)
c = country.get(1)
handset.select(brand = b, country = c)

One of both, depending on the occasion, sometimes it
would be better to use one way, sometimes the other one.

In fact this is the way "INSERT" works!! Why not SELECT?


The good news is that this small patch (3 lines) add
this powerful features :)

It really was trivial to do thanks to the well-designed
code. Thanks very much for the hard work.

As expected, it passes all the tests.

----------------------------------------------------------------------

>Comment By: Oleg Broytmann (phd)
Date: 2006-02-09 20:16

Message:
Logged In: YES 
user_id=4799

I understand it. But I think

s = TestWorkKey.selectBy(composer=c, title='Symphony No. 9')

does what you want, doesn't it?

----------------------------------------------------------------------

Comment By: Pau Aliagas (linux4u)
Date: 2006-02-09 20:00

Message:
Logged In: YES 
user_id=140558

What I want is to use the object or the field name seemlessly, so that I don't need to use the foreign key name that depends on the Stlye. I'd always use the object name like this.    

----------------------------------------------------------------------

Comment By: Oleg Broytmann (phd)
Date: 2006-01-31 18:20

Message:
Logged In: YES 
user_id=4799

This works in the trunk. Look at
http://svn.colorstudy.com/SQLObject/trunk/sqlobject/tests/test_foreignKey.py:

    # Select by usual way                                  
                                                  
    s = TestWorkKey.selectBy(composerID=c.id,
title='Symphony No. 9')                                         
    assert s[0]==w1                                        
                                                  
    # selectBy object                                      
                                                  
    s = TestWorkKey.selectBy(composer=c, title='Symphony No.
9')                                              
    assert s[0]==w1

Isn't it what you want?

----------------------------------------------------------------------

Comment By: Pau Aliagas (linux4u)
Date: 2006-01-20 10:51

Message:
Logged In: YES 
user_id=140558

There is a little mistake in the example: s/select/selectBy

where it says:
handset.select(brand = b, country = c)

it should say:
handset.selectBy(brand = b, country = c)

This is how it really works (select always uses the dotq
syntax).

----------------------------------------------------------------------

You can respond by visiting: 
https://sourceforge.net/tracker/?func=detail&atid=540674&aid=1410527&group_id=74338