Well, as I thought that this issue was important for making queries much
natutral, I've cooked a patch to implement it. It was really really easy,
short and clean, but I think it's a GREAT addition to the code.
I've added it to the SF tracker (id = 1410527):
http://sourceforge.net/tracker/index.php?func=detail&aid=1410527&group_id=74338&atid=540674
I include here the explanation for those lazy souls.
------8<----8<----8<----8<----8<----8<----8<----8<----8<----8<------
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.selectBy(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.selectBy(brandID = 4)
or
b = brand.get(4)
handset.selectBy(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.selectBy(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.
--
Pau--- SQLObject-0.8.0/dbconnection.py 2006-01-02 10:09:37.000000000 +0100
+++ SQLObject-0.8.0.pau/sqlobject/dbconnection.py 2006-01-19
23:49:51.000000000 +0100
@@ -16,6 +16,7 @@
import sqlbuilder
from cache import CacheSet
import col
+import main
from joins import sorter
from converters import sqlrepr
import classregistry
@@ -690,8 +691,10 @@
obj = kw[col.foreignName]
if obj is None:
data[col.dbName] = None
+ elif isinstance(kw[col.foreignName], main.SQLObject):
+ data[col.dbName] = kw[col.foreignName].id
else:
- data[col.dbName] = obj.id
+ data[col.dbName] = kw[col.foreignName]
if not data:
return None
return ' AND '.join(
