Thread: [SQLObject] Question about *JOIN*
SQLObject is a Python ORM.
Brought to you by:
ianbicking,
phd
From: Arnaud F. <ar...@an...> - 2006-08-19 20:19:43
|
Hello, According to the sqlobject documentation, when i do a *JOIN*, it should do a SELECT *, however i haven't the same behavior there: list(model.TrackerPackage.select(join=LEFTJOIN(None,model.CustomFlagData))) 1/Select : SELECT tracker_package.id, tracker_package.lock_date, tracker_package.lock_period, tracker_package.done_by_date, tracker_package.info_id, tracker_package.locked_by_id, tracker_package.done_by_id, tracker_package.tracker_id FROM tracker_package LEFT JOIN custom_flag_data WHERE 1 = 1 1/QueryR : SELECT tracker_package.id, tracker_package.lock_date, tracker_package.lock_period, tracker_package.done_by_date, tracker_package.info_id, tracker_package.locked_by_id, tracker_package.done_by_id, tracker_package.tracker_id FROM tracker_package LEFT JOIN custom_flag_data WHERE 1 = 1 It doesn't do a SELECT * at all. Any idea about that? It would be great if it is possible to have all the informations of the JOIN table. Regards, Arnaud Fontaine |
From: Oleg B. <ph...@ma...> - 2006-08-20 10:54:42
|
On Sat, Aug 19, 2006 at 10:16:54PM +0200, Arnaud Fontaine wrote: > list(model.TrackerPackage.select(join=LEFTJOIN(None,model.CustomFlagData))) > 1/Select : SELECT tracker_package.id, tracker_package.lock_date, > tracker_package.lock_period, tracker_package.done_by_date, > tracker_package.info_id, tracker_package.locked_by_id, > tracker_package.done_by_id, tracker_package.tracker_id FROM > tracker_package LEFT JOIN custom_flag_data WHERE 1 = 1 > > It doesn't do a SELECT * at all. .select() only selects columns for its table; TrackerPackage in this case. > It would be great > if it is possible to have all the informations of the JOIN table. It wouldn't. Where should SQLObject put this information? class MyTable(SQLObject): name = StringCol() age = IntCol() I have declared a table with two columns. If I join the table with another table - where SQLObject should put additional columns? There are no definitions for them. Even worse - if I join the table with itself (using Alias) - how do I distinguish columns form one table from the columns from the same joined table? When one does .select() on a table SQLObject only fetches columns for that table. Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: Arnaud F. <ar...@an...> - 2006-08-20 11:27:33
|
>>>>> "Oleg" == Oleg Broytmann <ph...@ma...> writes: Oleg> When one does .select() on a table SQLObject only fetches Oleg> columns for that table. Hello, I understand. But is there a way to avoid excess overhead of the RDBMS. I mean, i have a table package with its associated bugs associated using a RelatedJoin, the bugs are related to a status table. So, for each package, there are about 6 SQL requests for each package. With about 300 packages, it is really annoying. Any idea for optimizing this kind of things ? Regards, Arnaud Fontaine |
From: Oleg B. <ph...@ma...> - 2006-08-20 11:45:39
|
On Sun, Aug 20, 2006 at 01:24:30PM +0200, Arnaud Fontaine wrote: > is there a way to avoid excess overhead of the RDBMS. from sqlobject.sqlbuilder import Select connection.queryAll(connection.sqlrepr(Select(...))) Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: Arnaud F. <ar...@an...> - 2006-08-20 13:02:55
|
>>>>> "Oleg" == Oleg Broytmann <ph...@ma...> writes: Oleg> from sqlobject.sqlbuilder import Select Oleg> connection.queryAll(connection.sqlrepr(Select(...))) Hey, Thanks a lot. Last question: is there a way to have a dict with column name as key directly instead of a tuple? Regards, Arnaud Fontaine |
From: Oleg B. <ph...@ma...> - 2006-08-20 13:13:35
|
On Sun, Aug 20, 2006 at 03:00:17PM +0200, Arnaud Fontaine wrote: > Thanks a lot. Last question: is there a way to have a dict with column > name as key directly instead of a tuple? Only with .select() - there is a method sqlmeta.asDict() - because sqlmeta knows the names and the order of the columns. sqlbuilder.Select() knows the names but the names could collide: Select([Table1.q.name, Table2.q.name]) - what keys you would want in the dictionary? And when you run connection.query() (or .queryAll) Select has been forgotten already - there is only a query string, so Select cannot build the dictionary. Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: Arnaud F. <ar...@an...> - 2006-08-20 13:57:21
|
Hello, I have the following tables: class Package(SQLObject): name = StringCol(alternateID=True, length=255) [...] class TrackerPackage(SQLObject): [...] info = ForeignKey('Package') tracker = ForeignKey('Tracker') bugs = RelatedJoin('Bug') class Bug(SQLObject): [...] packages = RelatedJoin('TrackerPackage') status = RelatedJoin('BugStatus') class BugStatus(SQLObject): name = StringCol(alternateID=True, length=40) bugs = RelatedJoin('Bug') class Tracker(SQLObject): [...] packages = MultipleJoin('TrackerPackage') flags = MultipleJoin('CustomFlagDefinition') At the moment, i'm doing something like that : tracker.get(1) for package in tracker.packages: print package.info.name [...] for bug in package.bugs: print bug.bugNumber for status in bug.status: print status.name I wonder if there is a way to do a *JOIN* or someting else for avoiding too much SQL requests. I can't figure out how i could do that with sqlobject. Regards, Arnaud Fontaine |