From: Ian S. <Ian...@et...> - 2004-05-28 15:50:04
|
Before I start digging into the source code does the following ability = already exist in SQLObject and, if not, does anyone else want it. AFAIK SQLObject can't do complex joins. For instance, I might want to do = a query like : SELECT TimeOfRequests.* FROM TimeOffRequests INNER JOIN Users ON = TimeOffRequests.cUserID =3D Users.cID WHERE Users.Dept =3D 12 The result will be a set of "A" objects so I'd like SO to treat them as = such. So if SQLObject doesn't already support it what I'd like to be = able to do is : timeOffReqs =3D TimeOffRequests.get(passThrough=3D"SELECT TOR.* FROM = TimeOffRequests TOR INNER JOIN Users U ON TOR.cUserID =3D U.cID WHERE = U.Dept =3D 12") Obviously, I've just broken the portability of my code so if there were = a more SO-ish way of doing it I'd gladly take the education.=20 However, in some DB's like Firebird you can get SQL results from a = StoredProc or a view or something and this pass-through capability could = prove useful. Thoughts? |
From: Ian B. <ia...@co...> - 2004-05-28 16:21:29
|
Ian Sparks wrote: > Before I start digging into the source code does the following > ability already exist in SQLObject and, if not, does anyone else want > it. > > AFAIK SQLObject can't do complex joins. For instance, I might want to > do a query like : > > SELECT TimeOfRequests.* FROM TimeOffRequests INNER JOIN Users ON > TimeOffRequests.cUserID = Users.cID WHERE Users.Dept = 12 Ah, and that is where you are wrong! TimeOffRequests.select(AND(TimeOffRequests.q.cUserID == Users.q.cID, Users.q.Dept == 12)) You can also do: TimeOffRequests.select("TimeOffRequests.cUserID = Users.cID AND Users.Dept = 12", clauseTables=['Users']) I imagine there are probably more complex queries that won't work, but one at a time... Ian |
From: Luke O. <lu...@me...> - 2004-05-28 17:21:04
|
At one time I thought we had discussed the possibility of adding additional WHERE conditions to joins? Maybe something to consider anew. A little offtopic from Ian Sparks' example, but on one-to-many joins something like: aUser.timeOffRequests.select(TimeOffRequests.q.startDate > aDate) (only those requests after aDate for that user) and on many-to-many (eg Users<->Meetings): aUser.meetings.select(Meetings.q.organizer == aUser) (those meetings the user organized) (could also be directly a method of SO, aObj.joinNameWhere() or something. but I think I prefer it being a method of the join.) I'll take a look at the implementation if there's interest, I know it would have helped me in the past (instead of manually doing a select with the join column id). Looks like it may require beefing up joins though, along the lines of the SelectResults class. Thoughts? Quoting Ian Bicking <ia...@co...>: > Ian Sparks wrote: >> Before I start digging into the source code does the following >> ability already exist in SQLObject and, if not, does anyone else want >> it. >> >> AFAIK SQLObject can't do complex joins. For instance, I might want to >> do a query like : >> >> SELECT TimeOfRequests.* FROM TimeOffRequests INNER JOIN Users ON >> TimeOffRequests.cUserID = Users.cID WHERE Users.Dept = 12 > > Ah, and that is where you are wrong! > > TimeOffRequests.select(AND(TimeOffRequests.q.cUserID == Users.q.cID, > Users.q.Dept == 12)) > |
From: David M. C. <da...@da...> - 2004-07-30 04:11:18
|
On Fri, May 28, 2004 at 11:49:54AM -0400, Ian Sparks wrote: > Before I start digging into the source code does the following ability > already exist in SQLObject and, if not, does anyone else want it. You can do Klass._connection.queryAll(sql) For example, I needed to quickly get a lookup dictionary of row IDs of a table that were in a joined table, so that I could determine which items in a tree widget would get expanders (without having to load all the joining objects) result = Klass.select(Join_klass.q.klassID==Klass.q.id) lookup = dict([(o.id,None) for o in result]) works OK, but dict(Klass._connection.queryAll("select klass_id, NULL from join_klass")) is 10 times faster even with cached objects. I think I saw that fetching only IDs instead of objects was on the TODO list. Dave Cook > AFAIK SQLObject can't do complex joins. For instance, I might want to do a query like : > > SELECT TimeOfRequests.* FROM TimeOffRequests INNER JOIN Users ON TimeOffRequests.cUserID = Users.cID WHERE Users.Dept = 12 > > The result will be a set of "A" objects so I'd like SO to treat them as such. So if SQLObject doesn't already support it what I'd like to be able to do is : > > timeOffReqs = TimeOffRequests.get(passThrough="SELECT TOR.* FROM TimeOffRequests TOR INNER JOIN Users U ON TOR.cUserID = U.cID WHERE U.Dept = 12") > > Obviously, I've just broken the portability of my code so if there were a more SO-ish way of doing it I'd gladly take the education. > > However, in some DB's like Firebird you can get SQL results from a StoredProc or a view or something and this pass-through capability could prove useful. > > Thoughts? > > > > > > > > > > ------------------------------------------------------- > This SF.Net email is sponsored by: Oracle 10g > Get certified on the hottest thing ever to hit the market... Oracle 10g. > Take an Oracle 10g class now, and we'll give you the exam FREE. > http://ads.osdn.com/?ad_id149&alloc_id66&op=click > _______________________________________________ > sqlobject-discuss mailing list > sql...@li... > https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss |