Re: [SQLObject] How to order a table by fields of an other table connected to the first one by ForeignKey()

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

On Thu, Jan 07, 2016 at 03:03:22PM +0000, Paul Otto Seidon <p.o...@da...> wrote:
> Hi all,
> 
> this seems somehow related to a recent post asking about "querying for 
> foreign key". Here as well, I guess, tables have to be joined, but I 
> don't see how to pack this into a single ORDERBY clause.
> 
> The main table is Entry, which owns _status = ForeignKey( "Status"). 
> Status owns a field 'value' besides some other fields. Now I would like 
> the Entry rows I select by some other criterion (e.g. their priority) be 
> ordered by the value field of the Status of the Entry.
> 
> When I ORDERBY Status.q._value then SQLOBJECT correctly tells me, that 
> Entry doesn't own a _value field. All other combinations don't work 
> neither. 
> 
> This has to be done more SQL-ish, right?
> 
> Anyone willing to help me out here?

class Test1(SQLObject):
    name = StringCol()
    test2 = ForeignKey('Test2')

class Test2(SQLObject):
    age = IntCol()

Test2.createTable()
Test1.createTable()

test2_1 = Test2(age=0)
test2_2 = Test2(age=42)

Test1(name='1', test2=test2_1)
Test1(name='2', test2=test2_2)

print list(Test1.select(Test1.q.test2 == Test2.q.id, orderBy=Test2.q.age))

   Output:

SELECT test1.id, test1.name, test1.test2_id FROM test1, test2 WHERE ((test1.test2_id) = (test2.id)) ORDER BY test2.age
[<Test1 1 name='1' test2ID=1>, <Test1 2 name='2' test2ID=2>]

> Cheers
> Paul

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