Learn how easy it is to sync an existing GitHub or Google Code repo to a SourceForge project! See Demo

Close

#38 allow the use of foreign keys in selects (small jewel)

closed-accepted
Oleg Broytman
None
5
2006-02-10
2006-01-20
Pau Aliagas
No

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.

Discussion

  • Pau Aliagas
    Pau Aliagas
    2006-01-20

    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).

     
  • Oleg Broytman
    Oleg Broytman
    2006-01-31

    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?

     
  • Pau Aliagas
    Pau Aliagas
    2006-02-09

    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.

     
  • Oleg Broytman
    Oleg Broytman
    2006-02-09

    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?

     
  • Pau Aliagas
    Pau Aliagas
    2006-02-09

    Logged In: YES
    user_id=140558

    I also want to do:

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

    This way I can just use the more convenient value without
    changing the code.

     
  • Oleg Broytman
    Oleg Broytman
    2006-02-10

    Logged In: YES
    user_id=4799

    Applied at the revision 1587 to the trunk, 1588 to the
    0.7-branch. I have slightly modified the patch and added a
    test. Please take a look.

     
  • Oleg Broytman
    Oleg Broytman
    2006-02-10

    • assigned_to: nobody --> phd
    • status: open --> closed-accepted
     
  • Pau Aliagas
    Pau Aliagas
    2006-02-17

    Logged In: YES
    user_id=140558

    These are my versions:

    mysql-4.1.16-1.FC4.1
    postgresql-8.0.7-1.FC4.1
    python-psycopg-1.1.21-1.fc4
    python-sqlite-1.1.6-1
    sqlite-3.1.2-3