Thread: [SQLObject] sqlbuilder and many-to-many, I still don't get it
SQLObject is a Python ORM.
Brought to you by:
ianbicking,
phd
From: Daniel H. <dan...@gm...> - 2006-01-04 13:12:13
|
Hi there! After searching ml-archives, google and some sourcecode, I'm still stuck. I have a simple many-to-many relationship and want to query using an sqlbuilder-expression without any handwritten SQL. Here's the model: class Movie(SQLObject): title =3D StringCol(length=3D50) actors =3D RelatedJoin("Actor") class Actor(SQLObject): name =3D StringCol(length=3D50) movies =3D RelatedJoin("Movie") Now, is there a more or less simple sqlbuilder expression to retrieve all m= ovies starring an actor which name.contains("Robert")? What would be the "officia= l" way to write such a query? Is there a place in the web that answers such questions beside the ml-archi= ve and the docs at sqlobject.org? Thanks in advance and sorry for asking, if this has already been answered. Daniel Haus |
From: Oleg B. <ph...@ma...> - 2006-01-04 15:27:39
|
On Wed, Jan 04, 2006 at 02:12:06PM +0100, Daniel Haus wrote: > class Movie(SQLObject): > title = StringCol(length=50) > actors = RelatedJoin("Actor") > > class Actor(SQLObject): > name = StringCol(length=50) > movies = RelatedJoin("Movie") > > Now, is there a more or less simple sqlbuilder expression to retrieve all movies > starring an actor which name.contains("Robert")? What would be the "official" > way to write such a query? The official way is def inactors(actors, name): for actor in actors: if actor.name == name: return True return False roberts_movies = [movie for movie in Movie.select() if inactors(movie.actors, "Robert") ] sqlbuilder expression will be much more complex 'cause you need to write a join for 3 tables including intermediate table. Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: Daniel H. <dan...@gm...> - 2006-01-16 19:21:00
|
Oleg Broytmann <phd <at> mail2.phd.pp.ru> writes: > sqlbuilder expression will be much more complex 'cause you > need to write a join for 3 tables including intermediate table. Hello Oleg, thanks for your reply. Now, for my movie database this would mean that if I wanted to get all movies starring some actor, I'd have to get _all_ movies and then filter those out not featuring the corresponding actor. Possibly this would fetch most (if not all) actors playing in any movie. Now for my personal DVD-collection, well, ok. But I'm thinking about switching a major website with 80+ connected subsites to TurboGears and SQLObject. At least I'm currently trying to find out if this is possible and reasonable. As there are some bigger tables and lots of many-to-many-relationships, fetching all rows and filtering manually with python would not be an option. Now, how would I do this in such a complex query? I can not find anything really helpful about this in this list, nor in the documentation or wikis, and this is not something too esoteric, right? Are there any examples anywhere around the internet, that I have not found yet? I've read somewhere in this list, that there's new features for ManyToMany and OneToMany in svn. Might this help me with my problems? Thanks a lot for your help, greetings Daniel |
From: Oleg B. <ph...@ma...> - 2006-01-16 19:28:18
|
On Mon, Jan 16, 2006 at 07:14:26PM +0000, Daniel Haus wrote: > Now, how would I do this in such a complex query? By manually declaring (but not creating) the intermediate table and constructing an sqlbuilder query. Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: Daniel H. <dan...@gm...> - 2006-01-16 19:50:20
|
2006/1/16, Oleg Broytmann <ph...@ma...>: > By manually declaring (but not creating) the intermediate table and > constructing an sqlbuilder query. Is this possible ("declaring (but not creating)") if I use "tg-admin sql create"? I could not find how to do this. Or do I need to call "ClassName.createTable()" manually or in my model.py? |
From: Oleg B. <ph...@ma...> - 2006-01-16 20:03:09
|
On Mon, Jan 16, 2006 at 08:50:11PM +0100, Daniel Haus wrote: > Is this possible ("declaring (but not creating)") if I use > "tg-admin sql create"? I don't know. I don't use TurboGears. Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |