Hi everyone,
I'm enjoying getting into SQLObject. I'm trying to translate a
relatively complex SQL query to a SQLObject model and query. I've
posted my SQLObject model, a PDF diagram of my MySQL schema, and the
relevant queries at:
http://qwerk.org/sqlobject.html
I'm trying to figure out how to translate the following SQL query
into Python code using SQLObject. I'm far from an SQL expert, but
here's the original query:
Query:
SELECT DISTINCT staff.id, CONCAT( staff.first_name, ' ',
staff.last_name ) AS name
FROM staff, staff_schools, staff_grades, staff_subjects, schools,
grades, subjects
WHERE staff.id = staff_schools.staff_id
AND staff_schools.school_id = schools.id
AND staff.id = staff_grades.staff_id
AND staff_grades.grade_id = grades.id
AND staff.id = staff_subjects.staff_id
AND staff_subjects.subject_id = subjects.id
AND (
schools.abbrev = "ike"
OR schools.abbrev = "mb"
)
AND (
grades.abbrev = "gr5"
)
AND (
subjects.abbrev = "socialstud"
OR subjects.abbrev = "aom"
)
The key part of this query is the last three WHERE clauses that
include references to schools, grades, and subjects. This query will
be created based on HTML form input and could have several items in
each of the sections. (ORs within and ANDs between.)
Here's what I try with SQLObject:
[EMAIL PROTECTED]:~/public_html$ python2.4
Python 2.4.1 (#2, May 5 2005, 11:32:06)
[GCC 3.3.5 (Debian 1:3.3.5-12)] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> from ibmodel import *
>>> Staff._connection.debug = True
>>> staff_members = Staff.select(
... AND(
... OR(School.q.abbrev == "ike",
... School.q.abbrev == "mb"),
... Grade.q.abbrev == "gr5",
... OR(Subject.q.abbrev == "socialstud",
... Subject.q.abbrev == "aom")),
... distinct = True)
>>> for staff in staff_members:
... print staff.firstName
...
1/Select : SELECT DISTINCT staff.id, staff.first_name,
staff.last_name,
staff.email FROM schools, grades, subjects, staff WHERE
(((schools.abbrev = 'ike') OR (schools.abbrev = 'mb')) AND
((grades.abbrev = 'gr5') AND ((subjects.abbrev = 'socialstud') OR
(subjects.abbrev = 'aom'))))
Tim
Scott
Paul
Betsy
Emily
Mary Jo
Jeremy
That's not the right answer. It doesn't look like the query has all
of the links that it needs.
Any suggestions? Maybe I'm asking too much of SQLObject and it won't
do as much heavy lifting as I thought it might.
-Tim
--
Timothy Wilson
Technology Integration Specialist
Hopkins ISD #270, Hopkins, MN, USA
ph: 952.988.4103 fax: 952.988.4311 blog: http://technosavvy.org
-------------------------------------------------------
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&kid=103432&bid=230486&dat=121642
_______________________________________________
sqlobject-discuss mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss
