Thread: [SQLObject] Multiple RelatedJoins between the same two classes
SQLObject is a Python ORM.
Brought to you by:
ianbicking,
phd
From: Tim B. <ti...@al...> - 2007-07-23 19:54:33
|
I posted this on the TurboGears list last week, and don't desire to cross-post. But as it didn't receive a substantive answer, perhaps it is better to send it directly to the SQLObject list. Thanks for your consideration. - Tim I'm trying to create more than one RelatedJoin between the same two classes. I need to construct many-to-many relationships between Persons and Projects, because: - one project can have multiple clients (when it's an open-source project) - one project can have multiple contractors working on it - one person can work as a contractor on multiple projects - one person can be the client that pays for multiple projects - one person can be a client on one project and a contractor on another project Is it possible to do this in SQLObject? How? I've tried to represent this simply as: class Person(SQLObject): name = StringCol() clientProjects = RelatedJoin("Project") contractorProjects = RelatedJoin("Project") class Project(SQLObject): name = StringCol() clients = RelatedJoin("Person") contractors = RelatedJoin("Person") But, when I add a client to a project in Catwalk, that client appears as a contractor as well on the same project! Makes sense; for example, I shouldn't expect SQLObject to distinguish between two identical references to RelatedJoin("Project"). But how can I distinguish the two references properly? I've tried various combinations of using joinColumn, otherColumn, and intermediateTable, but no success yet. The documentation on joinColumn and otherColumn is not readily understandable; perhaps a UML diagram of the references between the classes and database tables could clarify the documentation. The best luck I've had yet was to specify two intermediateTables - one for the project_client relation and one for the project_contractor relation. But, that resulted in all persons being added as clients, even when I tried to add them as contractors. Tim Black |
From: Frank W. <fra...@no...> - 2007-07-23 20:22:36
|
SGVsbG8gVGltLAoKaSBndWVzcywgdGhpcyBpcyByYXRoZXIgYSBjYXR3YWxrIHByb2JsZW0gdGhh biBhIHNxbG9iamVjdCBwcm9ibGVtLgoKaSBndWVzcyB5b3UgYXJlIHVzaW5nIHNxbG9iamVjdCB3 aXRoaW4gdHVyYm9nZWFycz8KCnRyeSBhZGRpbmcgeW91ciByZWxhdGlvbnMgaW4gdGhlIGludGVy YWN0aXZlIHNoZWxsLiB5b3UgY2FuIGdldCBvbmUgYnkgZXhlY3V0aW5nCgp0Zy1hZG1pbiBzaGVs bAoKZnJvbSB3aXRoaW4geW91ciBwcm9qZWN0IGZvbGRlci4KCnRoZW4geW91IGNhbiB0cnkgc3Ro LiBsaWtlCgpQcm9qZWN0LmdldCgxKS5hZGRDb250cmFjdG9yKENvbnRyYWN0b3IuZ2V0KDIpKQoK aSB0aGluayB0aGF0IHNxbG9iamVjdCBkb2VzIGRpc3Rpbmd1aXNoIGJldHdlZW4gdHdvIHJlbGF0 ZWQgam9pbnMKYmV0d2VlbiB0aGUgc2FtZSB0d28gdGFibGVzLi4uIGnCtG0gaGF2aW5nIGtpbmQg b2YgdGhlIHNhbWUgdXNlY2FzZSBhbmQKaXQgd29ya3MgcXVpdGUgd2VsbCAtIGNhdHdhbGsgaXMg dW5mb3J0dW5hdGVseSBub3QgcmVhbGx5CnByb2R1Y3Rpb24tcmVhZHkgZm9yIHNvbWUgY2VydGFp biB0aGluZ3MgLSBzb21ldGltZXMgZmVlbHMgbGlrZSBhIHRveS4KCkhUSCwKRnJhbmsKCjIwMDcv Ny8yMywgVGltIEJsYWNrIDx0aW1AYWx3YXlzcmVmb3JtZWQuY29tPjoKPgo+ICBJIHBvc3RlZCB0 aGlzIG9uIHRoZSBUdXJib0dlYXJzIGxpc3QgbGFzdCB3ZWVrLCBhbmQgZG9uJ3QgZGVzaXJlIHRv Cj4gY3Jvc3MtcG9zdC4gIEJ1dCBhcyBpdCBkaWRuJ3QgcmVjZWl2ZSBhIHN1YnN0YW50aXZlIGFu c3dlciwgcGVyaGFwcyBpdCBpcwo+IGJldHRlciB0byBzZW5kIGl0IGRpcmVjdGx5IHRvIHRoZSBT UUxPYmplY3QgbGlzdC4gIFRoYW5rcyBmb3IgeW91cgo+IGNvbnNpZGVyYXRpb24uICAtIFRpbQo+ Cj4gIEknbSB0cnlpbmcgdG8gY3JlYXRlIG1vcmUgdGhhbiBvbmUgUmVsYXRlZEpvaW4gYmV0d2Vl biB0aGUgc2FtZSB0d28KPiBjbGFzc2VzLiAgSSBuZWVkIHRvIGNvbnN0cnVjdCBtYW55LXRvLW1h bnkgcmVsYXRpb25zaGlwcyBiZXR3ZWVuIFBlcnNvbnMgYW5kCj4gUHJvamVjdHMsIGJlY2F1c2U6 Cj4KPiAgLSBvbmUgcHJvamVjdCBjYW4gaGF2ZSBtdWx0aXBsZSBjbGllbnRzICh3aGVuIGl0J3Mg YW4gb3Blbi1zb3VyY2UgcHJvamVjdCkKPiAgLSBvbmUgcHJvamVjdCBjYW4gaGF2ZSBtdWx0aXBs ZSBjb250cmFjdG9ycyB3b3JraW5nIG9uIGl0Cj4gIC0gb25lIHBlcnNvbiBjYW4gd29yayBhcyBh IGNvbnRyYWN0b3Igb24gbXVsdGlwbGUgcHJvamVjdHMKPiAgLSBvbmUgcGVyc29uIGNhbiBiZSB0 aGUgY2xpZW50IHRoYXQgcGF5cyBmb3IgbXVsdGlwbGUgcHJvamVjdHMKPiAgLSBvbmUgcGVyc29u IGNhbiBiZSBhIGNsaWVudCBvbiBvbmUgcHJvamVjdCBhbmQgYSBjb250cmFjdG9yIG9uIGFub3Ro ZXIKPiBwcm9qZWN0Cj4KPiAgSXMgaXQgcG9zc2libGUgdG8gZG8gdGhpcyBpbiBTUUxPYmplY3Q/ ICBIb3c/Cj4KPiAgSSd2ZSB0cmllZCB0byByZXByZXNlbnQgdGhpcyBzaW1wbHkgYXM6Cj4KPiAg Y2xhc3MgUGVyc29uKFNRTE9iamVjdCk6Cj4gICAgICBuYW1lID0gU3RyaW5nQ29sKCkKPiAgICAg IGNsaWVudFByb2plY3RzID0gUmVsYXRlZEpvaW4oIlByb2plY3QiKQo+ICAgICAgY29udHJhY3Rv clByb2plY3RzID0gUmVsYXRlZEpvaW4oIlByb2plY3QiKQo+Cj4gIGNsYXNzIFByb2plY3QoU1FM T2JqZWN0KToKPiAgICAgIG5hbWUgPSBTdHJpbmdDb2woKQo+ICAgICAgY2xpZW50cyA9IFJlbGF0 ZWRKb2luKCJQZXJzb24iKQo+ICAgICAgY29udHJhY3RvcnMgPSBSZWxhdGVkSm9pbigiUGVyc29u IikKPgo+ICBCdXQsIHdoZW4gSSBhZGQgYSBjbGllbnQgdG8gYSBwcm9qZWN0IGluIENhdHdhbGss IHRoYXQgY2xpZW50IGFwcGVhcnMgYXMgYQo+IGNvbnRyYWN0b3IgYXMgd2VsbCBvbiB0aGUgc2Ft ZSBwcm9qZWN0ISAgTWFrZXMgc2Vuc2U7IGZvciBleGFtcGxlLCBJCj4gc2hvdWxkbid0IGV4cGVj dCBTUUxPYmplY3QgdG8gZGlzdGluZ3Vpc2ggYmV0d2VlbiB0d28gaWRlbnRpY2FsIHJlZmVyZW5j ZXMKPiB0byBSZWxhdGVkSm9pbigiUHJvamVjdCIpLiAgQnV0IGhvdyBjYW4gSSBkaXN0aW5ndWlz aCB0aGUgdHdvIHJlZmVyZW5jZXMKPiBwcm9wZXJseT8KPgo+ICBJJ3ZlIHRyaWVkIHZhcmlvdXMg Y29tYmluYXRpb25zIG9mIHVzaW5nIGpvaW5Db2x1bW4sIG90aGVyQ29sdW1uLCBhbmQKPiBpbnRl cm1lZGlhdGVUYWJsZSwgYnV0IG5vIHN1Y2Nlc3MgeWV0LiAgVGhlIGRvY3VtZW50YXRpb24gb24g am9pbkNvbHVtbiBhbmQKPiBvdGhlckNvbHVtbiBpcyBub3QgcmVhZGlseSB1bmRlcnN0YW5kYWJs ZTsgcGVyaGFwcyBhIFVNTCBkaWFncmFtIG9mIHRoZQo+IHJlZmVyZW5jZXMgYmV0d2VlbiB0aGUg Y2xhc3NlcyBhbmQgZGF0YWJhc2UgdGFibGVzIGNvdWxkIGNsYXJpZnkgdGhlCj4gZG9jdW1lbnRh dGlvbi4gIFRoZSBiZXN0IGx1Y2sgSSd2ZSBoYWQgeWV0IHdhcyB0byBzcGVjaWZ5IHR3bwo+IGlu dGVybWVkaWF0ZVRhYmxlcyAtIG9uZSBmb3IgdGhlIHByb2plY3RfY2xpZW50IHJlbGF0aW9uIGFu ZCBvbmUgZm9yIHRoZQo+IHByb2plY3RfY29udHJhY3RvciByZWxhdGlvbi4gIEJ1dCwgdGhhdCBy ZXN1bHRlZCBpbiBhbGwgcGVyc29ucyBiZWluZyBhZGRlZAo+IGFzIGNsaWVudHMsIGV2ZW4gd2hl biBJIHRyaWVkIHRvIGFkZCB0aGVtIGFzIGNvbnRyYWN0b3JzLgo+Cj4gIFRpbSBCbGFjawo+IC0t LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0tLS0KPiBUaGlzIFNGLm5ldCBlbWFpbCBpcyBzcG9uc29yZWQgYnk6IFNwbHVu ayBJbmMuCj4gU3RpbGwgZ3JlcHBpbmcgdGhyb3VnaCBsb2cgZmlsZXMgdG8gZmluZCBwcm9ibGVt cz8gIFN0b3AuCj4gTm93IFNlYXJjaCBsb2cgZXZlbnRzIGFuZCBjb25maWd1cmF0aW9uIGZpbGVz IHVzaW5nIEFKQVggYW5kIGEgYnJvd3Nlci4KPiBEb3dubG9hZCB5b3VyIEZSRUUgY29weSBvZiBT cGx1bmsgbm93ID4+ICBodHRwOi8vZ2V0LnNwbHVuay5jb20vCj4gX19fX19fX19fX19fX19fX19f X19fX19fX19fX19fX19fX19fX19fX19fX19fX18KPiBzcWxvYmplY3QtZGlzY3VzcyBtYWlsaW5n IGxpc3QKPiBzcWxvYmplY3QtZGlzY3Vzc0BsaXN0cy5zb3VyY2Vmb3JnZS5uZXQKPiBodHRwczov L2xpc3RzLnNvdXJjZWZvcmdlLm5ldC9saXN0cy9saXN0aW5mby9zcWxvYmplY3QtZGlzY3Vzcwo+ Cj4K |
From: <pk...@gm...> - 2007-07-23 20:37:32
|
Tim Black schrieb: > I've tried to represent this simply as: > > class Person(SQLObject): > name = StringCol() > clientProjects = RelatedJoin("Project") > contractorProjects = RelatedJoin("Project") > > class Project(SQLObject): > name = StringCol() > clients = RelatedJoin("Person") > contractors = RelatedJoin("Person") > > But, when I add a client to a project in Catwalk, that client appears as > a contractor as well on the same project! Makes sense; for example, I > shouldn't expect SQLObject to distinguish between two identical > references to RelatedJoin("Project"). But how can I distinguish the two > references properly? Hi, this quite a shot in the dark but AFAIK you can specify the intermediate table manually in the foo = RelatedJoin() statement. Look at the docs, I guess it takes a intermediateTable keyword. This way if you add a contractor it would go in the. You might need to add those intermediate tables by hand. cheers Paul |
From: Oleg B. <ph...@ph...> - 2007-07-23 21:10:43
|
On Mon, Jul 23, 2007 at 12:53:41PM -0700, Tim Black wrote: > I'm trying to create more than one RelatedJoin between the same two > classes. I need to construct many-to-many relationships between Persons > and Projects > > class Person(SQLObject): > name = StringCol() > clientProjects = RelatedJoin("Project") > contractorProjects = RelatedJoin("Project") > > class Project(SQLObject): > name = StringCol() > clients = RelatedJoin("Person") > contractors = RelatedJoin("Person") You have to understand that SQLObject creates a hidden intermediate table that stores cross-pointers for an every pair of RelatedJoins. If you want to create more than one RelatedJoin you have to give the name for the tables yourself. Try this: class Person(SQLObject): name = StringCol() clientProjects = RelatedJoin("Project", intermediateTable="person_project_clients") contractorProjects = RelatedJoin("Project", intermediateTable="person_project_contractors") class Project(SQLObject): name = StringCol() clients = RelatedJoin("Person", intermediateTable="person_project_clients") contractors = RelatedJoin("Person", intermediateTable="person_project_contractors") And if you have crated your tables already you must create these intermediate tables yourself: Person.createJoinTables(ifNotExists=True) Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: Tim B. <ti...@al...> - 2007-07-24 16:47:13
|
Oleg Broytmann wrote: > You have to understand that SQLObject creates a hidden intermediate > table that stores cross-pointers for an every pair of RelatedJoins. If you > want to create more than one RelatedJoin you have to give the name for the > tables yourself. Could it be that I have to drop to manually writing the classes for the join tables as described here: http://sqlobject.org/FAQ.html#how-can-i-define-my-own-intermediate-table-in-my-many-to-many-relationship ? Tim |
From: jonathan <jon...@op...> - 2007-07-24 19:04:07
|
Tim Black escribió: > Oleg Broytmann wrote: >> You have to understand that SQLObject creates a hidden intermediate >> table that stores cross-pointers for an every pair of RelatedJoins. If you >> want to create more than one RelatedJoin you have to give the name for the >> tables yourself. > Could it be that I have to drop to manually writing the classes for > the join tables as described here: > http://sqlobject.org/FAQ.html#how-can-i-define-my-own-intermediate-table-in-my-many-to-many-relationship > ? It is useful if you want more info in the intermetiate class than just the two foreignkeys to the related classes. If not, you can go the explained way. > > Tim > ------------------------------------------------------------------------ > > ------------------------------------------------------------------------- > This SF.net email is sponsored by: Splunk Inc. > Still grepping through log files to find problems? Stop. > Now Search log events and configuration files using AJAX and a browser. > Download your FREE copy of Splunk now >> http://get.splunk.com/ > ------------------------------------------------------------------------ > > _______________________________________________ > sqlobject-discuss mailing list > sql...@li... > https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss > |
From: jonhattan <jon...@op...> - 2007-07-24 19:29:16
|
jonathan escribió: > Tim Black escribió: > >> Oleg Broytmann wrote: >> >>> You have to understand that SQLObject creates a hidden intermediate >>> table that stores cross-pointers for an every pair of RelatedJoins. If you >>> want to create more than one RelatedJoin you have to give the name for the >>> tables yourself. >>> >> Could it be that I have to drop to manually writing the classes for >> the join tables as described here: >> http://sqlobject.org/FAQ.html#how-can-i-define-my-own-intermediate-table-in-my-many-to-many-relationship >> ? >> > > It is useful if you want more info in the intermetiate class than just > the two foreignkeys to the related classes. If not, you can go the > explained way. > Oh, I'm sorry, I didn't read your previous mail. What I said is true, but does not resolve your problem. If you create the related classes by hand, afterwards you would do: PersonProject(person = Person.get(1), proyect = Project.get(1)) ContractorProyect(person = Person.get(1), proyect = Project.get(1)) > > > >> Tim >> ------------------------------------------------------------------------ >> >> ------------------------------------------------------------------------- >> This SF.net email is sponsored by: Splunk Inc. >> Still grepping through log files to find problems? Stop. >> Now Search log events and configuration files using AJAX and a browser. >> Download your FREE copy of Splunk now >> http://get.splunk.com/ >> ------------------------------------------------------------------------ >> >> _______________________________________________ >> sqlobject-discuss mailing list >> sql...@li... >> https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss >> >> > > > ------------------------------------------------------------------------- > This SF.net email is sponsored by: Splunk Inc. > Still grepping through log files to find problems? Stop. > Now Search log events and configuration files using AJAX and a browser. > Download your FREE copy of Splunk now >> http://get.splunk.com/ > _______________________________________________ > sqlobject-discuss mailing list > sql...@li... > https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss > > |
From: Tim B. <ti...@al...> - 2007-07-24 16:36:02
|
Oleg Broytmann wrote: > You have to understand that SQLObject creates a hidden intermediate > table that stores cross-pointers for an every pair of RelatedJoins. If you > want to create more than one RelatedJoin you have to give the name for the > tables yourself. Try this: > > class Person(SQLObject): > name = StringCol() > clientProjects = RelatedJoin("Project", intermediateTable="person_project_clients") > contractorProjects = RelatedJoin("Project", intermediateTable="person_project_contractors") > > class Project(SQLObject): > name = StringCol() > clients = RelatedJoin("Person", intermediateTable="person_project_clients") > contractors = RelatedJoin("Person", intermediateTable="person_project_contractors") > Thank you for your help. The above created the following tables: person person_project_clients person_project_contractors project And after running Project.get(1).addPerson(Person.get(2)), person_project_clients contains person_id=2, project_id=1 person_project_contractors is empty After running Project.get(1).addPerson(Person.get(1)), person_project_clients contains person_id=2, project_id=1; person_id=1, project_id=1 person_project_contractors is empty Then I try: >>> Project.get(1).addContractor(Person.get(1)) Traceback (most recent call last): File "<console>", line 1, in ? AttributeError: 'Project' object has no attribute 'addContractor' What is the right way to specify that I'm adding a contractor and not a client? Tim |
From: Oleg B. <ph...@ph...> - 2007-07-25 14:10:58
|
On Tue, Jul 24, 2007 at 09:35:34AM -0700, Tim Black wrote: > >>> Project.get(1).addContractor(Person.get(1)) > Traceback (most recent call last): > File "<console>", line 1, in ? > AttributeError: 'Project' object has no attribute 'addContractor' > > What is the right way to specify that I'm adding a contractor and not a > client? class Person(SQLObject): name = StringCol() clientProjects = RelatedJoin("Project", intermediateTable="person_project_clients", addRemoveName="Project") contractorProjects = RelatedJoin("Project", intermediateTable="person_project_contractors", addRemoveName="ContractorProject") class Project(SQLObject): name = StringCol() clients = RelatedJoin("Person", intermediateTable="person_project_clients", addRemoveName="Person") contractors = RelatedJoin("Person", intermediateTable="person_project_contractors", addRemoveName="Contractor") Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: Tim B. <ti...@al...> - 2007-07-25 18:57:54
|
Oleg Broytmann wrote: > On Tue, Jul 24, 2007 at 09:35:34AM -0700, Tim Black wrote: > >>>>> Project.get(1).addContractor(Person.get(1)) >>>>> >> Traceback (most recent call last): >> File "<console>", line 1, in ? >> AttributeError: 'Project' object has no attribute 'addContractor' >> >> What is the right way to specify that I'm adding a contractor and not a >> client? >> > > class Person(SQLObject): > name = StringCol() > clientProjects = RelatedJoin("Project", > intermediateTable="person_project_clients", > addRemoveName="Project") > contractorProjects = RelatedJoin("Project", > intermediateTable="person_project_contractors", > addRemoveName="ContractorProject") > > class Project(SQLObject): > name = StringCol() > clients = RelatedJoin("Person", > intermediateTable="person_project_clients", > addRemoveName="Person") > contractors = RelatedJoin("Person", > intermediateTable="person_project_contractors", > addRemoveName="Contractor") > Thank you! That solved my problem. The model works now in the tg-admin shell and in Catwalk. Tim |