Answers inline, and haven't tested them, but I'm pretty sure they're
correct, except where I note questions. I mostly did this as a way to
learn SQLObject in a more complicated way than I had up till now. And I
read the whole of this thread, but I've got something around 300
messages to catch up on still, so if my mailreader missed one and this
is answered, sorry.
Todd Greenwood wrote:
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...
How about:
list(Page.select(EXISTS(Select(Entry.q.mod_date_time, where=(Page.q.id
== Entry.q.pageID),orderBy="entry.mod_date_time desc", limit=1)
Note, there isn't a way to get orderBy to work with descending using the
Entry.q notation that I can see, so I used a string. I'm pretty sure
it's right, but not positive.
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
This one's easy, unless you mis-transcribed it: LEFTJOINOn not
LEFTJOINon. And it's probably the best for getting the actual data you
want, if you do:
list(Page.select(join=LEFTJOINon(Page, Entry, Page.q.id == Entry.q.pageID),
orderBy="entry.mod_date_time desc", limit=10))
There doesn't seem to be a distinct, so this probably won't give what
you want exactly, but by removing the limit it should at least give you
a list to parse the first 10 distinct ones out. It also has the same
string for the orderBy as above.
I am stuck, but this is a good thing, as it is highlighting my shallow
understanding of SQLObject...
-Todd
