Thread: [SQLObject] Select over RelatedJoin
SQLObject is a Python ORM.
Brought to you by:
ianbicking,
phd
From: Julien S. <jul...@di...> - 2006-04-22 10:30:48
|
Hi all, I'm a beginner in SQLObject and I can't figure out how to perform a simple query over a RelatedJoin... I have the two following SQLObjects Food(SQLObject): name =3D StringCol() brand =3D StringCol(default=3DNone) categories =3D RelatedJoin("Category") Category(SQLObject): name =3D StringCol() description =3D StringCol() foods =3D RelatedJoin("Food") I would like to query all foods in a given category with a given brand ? I can see the related sql query clearly in my head but I can't find any way to do it in a sqlobject way.... Many thanks in advance for your help ! Julien |
From: Oleg B. <ph...@ma...> - 2006-04-22 10:37:49
|
On Sat, Apr 22, 2006 at 12:30:40PM +0200, Julien Seiler wrote: > Food(SQLObject): > name = StringCol() > brand = StringCol(default=None) > categories = RelatedJoin("Category") > > Category(SQLObject): > name = StringCol() > description = StringCol() > foods = RelatedJoin("Food") > I would like to query all foods in a given category with a given brand cat = Category.get(id) for food in cat.foods: # This fetches ALL foods! if food.brand = "Gourme": print food Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: Max I. <isc...@gm...> - 2006-04-25 09:34:59
|
> On Sat, Apr 22, 2006 at 12:30:40PM +0200, Julien Seiler wrote: > > Food(SQLObject): > > name =3D StringCol() > > brand =3D StringCol(default=3DNone) > > categories =3D RelatedJoin("Category") > > > > Category(SQLObject): > > name =3D StringCol() > > description =3D StringCol() > > foods =3D RelatedJoin("Food") >=20 > > I would like to query all foods in a given category with a given = brand >=20 > cat =3D Category.get(id) > for food in cat.foods: # This fetches ALL foods! > if food.brand =3D "Gourme": > print food If this is not feasible you can write a query by hand (almost) using = SQLBuilder. Something like this: class MemberJoin(sqlbuilder.SQLExpression): def __init__(self, cls, table): self.cls =3D cls self.table =3D table def __sqlrepr__(self, db): name =3D str(self.cls.q) return "%s.%s_id =3D %s.id" % (self.table, name, name) sr =3D Food.select(sqlbuilder.AND( MemberJoin(Category, 'category_food_members')), Food.q.brand =3D=3D 'Gourme', clauseTables=3D['category_food_members']) Name 'category_food_members' can be set with intermediateTable arg to = RelatedJoin ctor. You can also try to use various *JOIN* helper classes defined in = sqlbuilder.py. HTH, Max Ischenko. http://maxischenko.in.ua=20 Custom software development services |
From: michelts <mic...@gm...> - 2006-04-25 11:59:13
|
Hi! You can use SQLRelatedJoin, it will return a sqlresult object to you, then you can use the filter method to do what you want: Food(SQLObject): name =3D StringCol() brand =3D StringCol(default=3DNone) categories =3D SQLRelatedJoin("Category") Category(SQLObject): name =3D StringCol() description =3D StringCol() foods =3D SQLRelatedJoin("Food") category =3D Category.get(id) foods =3D category.foods.filter(Food.q.brand=3D=3D"Gourme") for food in foods: print food -- Michel Thadeu Sabchuk Curitiba - Brasil |
From: Steve Z. <sl...@gm...> - 2006-04-25 12:13:48
|
> You can use SQLRelatedJoin, it will return a sqlresult object to you, > then you can use the filter method to do what you want: Unfortunately, I can't test this at the moment but is filtering an SQLRelatedJoin now working? Previously there was a problem having something to do with the fact that an SQLRelatedJoin was creating a text query that was interfering with filtering the result. Is this fixed? |
From: michelts <mic...@gm...> - 2006-04-25 14:19:17
|
Eureka! > Unfortunately, I can't test this at the moment but is filtering an > SQLRelatedJoin now working? Previously there was a problem having > something to do with the fact that an SQLRelatedJoin was creating a > text query that was interfering with filtering the result. Is this > fixed? No it didn't work, so I make another method to do the SQLRelatedJoin, using SQLBuilder, now I kill 2 habbits with one hit, you can use filter and you can orderBy too. Thanks to Max to point me in the right way, I never used SQLBuilder so I do the SQLRelatedJoin that way. I will do more tests but at the case of this thread it word, I will do a diff soon... Anyway, you can test it too, I will attach the joins.py file, the file is from SQLObject0.8. You must use NewRelatedJoin (this is a temporary name, I will replace SQLRelatedJoin after I know it is ok). Sorry about the time taken to do this solution :) Regards, -- Michel Thadeu Sabchuk Curitiba - Brasil |
From: michelts <mic...@gm...> - 2006-04-25 14:47:21
Attachments:
joins.py.diff
|
Oleg, I done the SQLRelatedJoin using SQLBuilder but I saw that there is a OneToMany and ManyToMany joins now (I saw this in svn). Will *Join deprecated? I tested the filter and orderBy with ManyToMany using the example of this thread, it is working except by a mistake in joins.py file: in the _ManyToManySelectWrapper class, in the __getattr__ method, it should be getattr(self.select, attr) reather than getattr(self, select, attr)... I'm sending the diff of this change, but without SQLRelatedJoin fixed, I want to know if I should send it too. Thanks -- Michel Thadeu Sabchuk Curitiba - Brasil |
From: Oleg B. <ph...@ph...> - 2006-04-25 15:50:47
|
On Tue, Apr 25, 2006 at 11:47:17AM -0300, michelts wrote: > I done the SQLRelatedJoin using SQLBuilder but I saw that there is a > OneToMany and ManyToMany joins now (I saw this in svn). Will *Join > deprecated? Not in the nearest future. ManyToMany wasn't even backported to 0.7.1. Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: michelts <mic...@gm...> - 2006-04-25 16:13:38
|
Ok, I done the tests over the new structure of SQLRelatedJoin, it seems to be working even if I use a non standard table name or column name, I sending two diffs: joins.py-0.7.1dev_r1728-py2.3.diff joins.py-0.8dev_r1727-py2.3.diff The first one have the SQLRelatedJoin fixed, the second one have the fix of ManyToMany and the fix of SQLRelatedJoin. I remove the old code of SQLRelatedJoin because it still returning a select result, in true, there is no difference on the object returned, except that now you can use filter or orderBy without problems... Thanks for attention 2006/4/25, Oleg Broytmann <ph...@ph...>: > On Tue, Apr 25, 2006 at 11:47:17AM -0300, michelts wrote: > > I done the SQLRelatedJoin using SQLBuilder but I saw that there is a > > OneToMany and ManyToMany joins now (I saw this in svn). Will *Join > > deprecated? > > Not in the nearest future. ManyToMany wasn't even backported to 0.7.1. > > Oleg. > -- > Oleg Broytmann http://phd.pp.ru/ ph...@ph... > Programmers don't die, they just GOSUB without RETURN. > > > ------------------------------------------------------- > Using Tomcat but need to do more? Need to support web services, security? > Get stuff done quickly with pre-integrated technology to make your job ea= sier > Download IBM WebSphere Application Server v.1.0.1 based on Apache Geronim= o > http://sel.as-us.falkag.net/sel?cmd=3Dlnk&kid=3D120709&bid=3D263057&dat= =3D121642 > _______________________________________________ > sqlobject-discuss mailing list > sql...@li... > https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss > -- Michel Thadeu Sabchuk Curitiba - Brasil |
From: Oleg B. <ph...@ph...> - 2006-05-09 14:12:10
|
On Tue, Apr 25, 2006 at 01:06:49PM -0300, michelts wrote: > joins.py-0.7.1dev_r1728-py2.3.diff > joins.py-0.8dev_r1727-py2.3.diff Applied at the revisions 1755, 1756. Thank you! Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: Chaz. <epr...@gm...> - 2006-05-09 14:35:12
|
I am writing a multi-thread application and would love to use SQLObject in it. To complicate matters, each of the threads use a different database (though all of them use Sqlite). What I am trying to understand is how to make sure each table uses the right database. My current thinking is to use a unique SqlHub object for each group of tables (that use the particular database). So I was thinking something like this (and please tell me how wrong I am !!!! It is better to find out now than to spend endless time debugging something that won't work and rewriting it!): foo = DBConnection(...) bar = DBConnection(...) class FoosTable(SQLObject) : _connection = foo ... class BarsTable(SQLObject) : _connection = bar .. Did I get it right or is there something else? BTW, is SQLObject thread safe (so long as we are dealing with different database files?) Peace, and thanks in advance Charles. |