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