Re: [SQLObject] querying with foreign key

[Oleg B. <ph...@ph...>]

Hi!

On Wed, Jun 24, 2015 at 04:13:21PM +0000, Taia Modlin <tai...@ya...> wrote:
> Hello,
> I was wondering if it is possible to filter a table based on a field from it's foreign key.
> Thanks

   There are two ways: simple and efficient. Let's see an example:

from sqlobject import *

__connection__ = "sqlite:/:memory:?debug=1&debugOutput=1"

class Test1(SQLObject):
    name = StringCol()

class Test2(SQLObject):
    name = StringCol()
    test1 = ForeignKey("Test1")

Test1.createTable()
Test2.createTable()

test1 = Test1(name="one")
test2 = Test2(name="One", test1=test1)

test1 = Test1(name="two")
test2 = Test2(name="Two", test1=test1)

# Simple way: run through all rows
for row in Test2.select():
    if row.test1.name == "one":
        print row.name

# Efficient way: do join
for row in Test2.select((Test2.q.test1==Test1.q.id) & (Test1.q.name=="one")):
    print row

   The join produces the following SQL:

SELECT test2.id, test2.name, test2.test1_id FROM test1, test2 WHERE (((test2.test1_id) = (test1.id)) AND ((test1.name) = ('one')))

   The advantage of join is efficiency, the disadvantage is that you
have to write it manually even if SQLObject has got enough information
to do it for you.

   See also a short note on using .throughTo magic attribute:

http://sqlobject.org/SelectResults.html#throughto-join-name-and-throughto-foreign-key-name

   but it doesn't apply to my example.

Oleg.
-- 
     Oleg Broytman            http://phdru.name/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.