A classic two table join question:
I would like to select from this model like so:
* the most recently changed 10 wiki pages, sorted by entry date
How would I do this in:
* SQL
* SQLObject
* SQLObject dropping into SQL
Here is the model:
class Page(SQLObject):
pagename = StringCol(alternateID=True, length=30)
entries = MultipleJoin('Entry')
class Entry(SQLObject):
data = StringCol()
mod_date_time = DateTimeCol(default=datetime.now())
I have to admit my SQL is rusty, and I'm having a helluva time
understanding the SQLObject docs.
This is what I have:
----------------------------------------------------------
http://sqlobject.org/SQLObject.html
Subqueries (subselects)
You can run queries with subqueries (subselects) on those DBMS that can
do subqueries (MySQL supports subqueries from version 4.1).
Use corresponding classes and functions from SQLBuilder:
from sqlobject.sqlbuilder import EXISTS, Select
select = Test1.select(EXISTS(Select(Test2.q.col2,
where=(Outer(Test1).q.col1 == Test2.q.col2))))
generates the query:
SELECT test1.id, test1.col1 FROM test1 WHERE
EXISTS (SELECT test2.col2 FROM test2 WHERE (test1.col1 = test2.col2))
----------------------------------------------------------
>From these instructions, it seemed that I could do this:
list(Page.select(EXISTS(Select(Entry.q.mod_date_time, where=(Page.q.id
== Entry.q.pageID)).max('Entry.q.mod_date_time'))))
AttributeError: Select instance has no attribute 'max'
Well, I was hoping to tack on an orderBy clause, but I have yet to get
that far...
Ok, so how about trying to JOIN?
This might do the trick:
----------------------------------------------------------
http://sqlobject.org/SQLObject.html
from sqlobject.sqlbuilder import LEFTJOINOn
MyTable.select(
join=LEFTJOINOn(Table1, Table2,
Table1.q.name == Table2.q.value))
----------------------------------------------------------
So I try this:
from sqlobject.sqlbuilder import LEFTJOINOn
list(Page.select(join=LEFTJOINon(Page, Entry, Page.q.id ==
Entry.q.pageID)))
NameError: name 'LEFTJOINon' is not defined
I am stuck, but this is a good thing, as it is highlighting my shallow
understanding of SQLObject...
-Todd
