I've got some complicated queries coming up and I'm wondering what the
options are. The current task is to find the chemicals whose
canonical name start with a certain letter. Or find the chemicals
whose name starts with a non-alphabetic character. There are 6000
chemicals with 200 fields each, so I'm not sure I want the entire
objects in memory. I need only the ID and name (for hyperlinks in
TurboGears).
The relevant fields are:
class Chemical(SQLObject):
canonical_name = ForeignKey("Name")
names = RelatedJoin("Name")
class Name(SQLObject):
name = UnicodeCol(length=500, notNone=True)
chemicals = RelatedJoin("Chemical")
'.names' is a list of all names (canonical and synonyms).
'.canonical_name' points to one of the '.names' records. I'm ignoring
synonyms for this task.
(The reason it's a many:many relationship is there are 85,000 names.
Sometimes the same name is canonical for one chemical but a synonym
for one or more others.)
I can see the SQL in my head and could just do connection.queryAll with:
SELECT Chemical.id AS id, Name.name AS name
FROM Chemical, Chemical_Name, Name
WHERE Chemical.id = Chemical_Name.Chemical_id
AND Name.id = Chemical_Name.Name_id
AND Chemical.canonical_nameID = Name.id
AND Name.name LIKE 'A%'
ORDER BY 2
But I'm looking for more SQLObject-ish alternatives.
Chemical.select(??.startswith('A'))
=> Er, but my criteria is actually in the related table, not Chemical.
Chemical.q.canonical_name.name.startswith('A')
=> Illegal. .canonical_name doesn't exist but .canonical_nameID does.
Chemical.select(Name.q.name.startswith('A')).count()
>>> Chemical.select(Name.q.name.startswith('A')).count()
1/QueryOne: SELECT COUNT(*) FROM Chemical, Name WHERE (Name.name LIKE 'A%')
=> Missing the join expression, will return too many records.
>>> Chemical.select(AND(Name.q.name.startswith('A'),
Chemical.q.canonical_nameID == Name.q.id)).count()
1/QueryOne: SELECT COUNT(*) FROM Chemical, Name WHERE ((Name.name
LIKE 'A%') AND (Chemical.canonical_nameID = Name.id))
446L
=> This works but it requires me manually setting the join condition.
I'm not supposed to know that .canonical_nameID exists, right?
There's a Select object mentioned in "How to use database agnostic SQL
in SQLObject", but it doesn't seem to be officially documented.
http://www.groovie.org/articles/2005/11/01/how-to-use-database-agnostic-sql-in-sqlobject
I played with that and got:
>>> sql = conn.sqlrepr(Select([Chemical.q.id, Name.q.name],
where=AND(Name.q.name.startswith('A'), Chemical.q.canonical_nameID ==
Name.q.id),orderBy=Name.q.name))
>>> sql
"SELECT Chemical.id, Name.name FROM Chemical, Name WHERE ((Name.name
LIKE 'A%') AND (Chemical.canonical_nameID = Name.id)) ORDER BY
Name.name"
>>> conn.queryAll(sql)
( tuple of several 2-tuples listed )
=> That works. I guess that's what I want. Any other ideas?
--
Mike Orr <[EMAIL PROTECTED]>
([EMAIL PROTECTED] address is semi-reliable)
-------------------------------------------------------
This SF.net email is sponsored by: Splunk Inc. Do you grep through log files
for problems? Stop! Download the new AJAX search engine that makes
searching your log files as easy as surfing the web. DOWNLOAD SPLUNK!
http://sel.as-us.falkag.net/sel?cmd=lnk&kid3432&bid#0486&dat1642
_______________________________________________
sqlobject-discuss mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss
