[sqlalchemy] Re: Is there a simple way to let records have the same groups as it parents
this is sort of inheritance of data, right? the best i've made so far about this is to get (somehow) all the Rs each with it's groups, and then do the inheritance (union over the path towards root in your case) over the result rows by hand. if u find a better way let me know... traversing a (recursive) tree isn't an easy thing either, unless u save it as nonrecursive (keeping extra links etc) in a way or another. On Thursday 28 August 2008 15:11:42 Cecil Westerhof wrote: I was just wondering if the folowing possible. A record has severall groups connected through a N:M relation. But it also has a parent. What I would like is that all the groups from the parent (and its parent, etc.) also are seen as group for the record. When the parent has a group added or removed that should then be 'visible' at the child. Not a very good description I am afraid, so I'll try to give an example. Say I have the groups G1, G2, G3, G4 and G5. I have the records R1 and R2. R1 is the parent of R2 and does not have a parent itself. If R1 has the groups G1 and G2 and R2 has the groups G2, G4 and G5, then when asking the list of groups of R2 should give, G1, G2, G4 and G5. When quering for records that have group G1, both R1 and R2 should be returned. When adding G3 to R1 the list for R2 should be: G1, G2, G3, G4 and G5. When removing G1 from R1 the list for R2 should be: G2, G3, G4 and G5. When removing G2 from R1 the list for R2 should be: G2, G3, G4 and G5. (R2 has itself also group G2.) Is this possible with sqlalchemy or has this to be done by hand? --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Is there a simple way to let records have the same groups as it parents
2008/8/28 [EMAIL PROTECTED]:
this is sort of inheritance of data, right?
You could it call like that I suppose.
the best i've made so far about this is to get (somehow) all the Rs
each with it's groups, and then do the inheritance (union over the
path towards root in your case) over the result rows by hand.
I was afraid for that.
if u find a better way let me know...
Ofcourse, but I am afraid it is not likely. :-{
traversing a (recursive) tree isn't an easy thing either, unless u
save it as nonrecursive (keeping extra links etc) in a way or
another.
I'll have to think about that. But properly this is only going to work
with tables that are not to big I am afraid.
Maybe I want to fancy things.
--
Cecil Westerhof
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Is there a simple way to let records have the same groups as it parents
On Aug 28, 2008, at 8:11 AM, Cecil Westerhof wrote:
I was just wondering if the folowing possible.
A record has severall groups connected through a N:M relation.
But it also has a parent. What I would like is that all the groups
from the parent (and its parent, etc.) also are seen as group for the
record.
When the parent has a group added or removed that should then be
'visible' at the child.
Not a very good description I am afraid, so I'll try to give an
example.
Say I have the groups G1, G2, G3, G4 and G5.
I have the records R1 and R2. R1 is the parent of R2 and does not have
a parent itself.
If R1 has the groups G1 and G2 and R2 has the groups G2, G4 and G5,
then when asking the list of groups of R2 should give, G1, G2, G4 and
G5.
When quering for records that have group G1, both R1 and R2 should
be returned.
When adding G3 to R1 the list for R2 should be: G1, G2, G3, G4 and G5.
When removing G1 from R1 the list for R2 should be: G2, G3, G4 and G5.
When removing G2 from R1 the list for R2 should be: G2, G3, G4 and G5.
(R2 has itself also group G2.)
Is this possible with sqlalchemy or has this to be done by hand?
I wouldn't say by hand. An element's groups are basically its own
groups unioned with the groups of its parent.So a simple recursion
is the simplest way to form this group, but this only handles the
object graph side of the equation, not the Query side.
Suppose a mapping like:
mapper(MyObject, mytable, properties={
'groups':relation(Group, collection_class=set),
'parent':relation(MyObject, remote_side=mytable.c.id)
})
MyObject could return the full groups using:
class MyObject(object):
@property
def full_groups(self):
if self.parent:
return self.groups.union(self.parent.full_groups)
else:
return self.groups
On the Query side, the basic job is to formulate joins to the parent,
which has the inherent issue that each level of depth corresponds to
another join. For the R1/R2 example it looks like:
parent_alias = aliased(MyObject)
sess
.query
(MyObject
).outerjoin
(parent_alias.parent).filter(or_(MyObject.groups.contains(somegroup),
parent_alias.groups.contains(somegroup))
You can attempt to create a generalized function for the query above
which is given N, number of levels to be queried, and then creates the
joins N times as needed. An example of this technique is in examples/
elementtree/adjacency_list.py .
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Is there a simple way to let records have the same groups as it parents
2008/8/28 Michael Bayer [EMAIL PROTECTED]: On Aug 28, 2008, at 8:11 AM, Cecil Westerhof wrote: I was just wondering if the folowing possible. A record has severall groups connected through a N:M relation. But it also has a parent. What I would like is that all the groups from the parent (and its parent, etc.) also are seen as group for the record. When the parent has a group added or removed that should then be 'visible' at the child. Not a very good description I am afraid, so I'll try to give an example. Say I have the groups G1, G2, G3, G4 and G5. I have the records R1 and R2. R1 is the parent of R2 and does not have a parent itself. If R1 has the groups G1 and G2 and R2 has the groups G2, G4 and G5, then when asking the list of groups of R2 should give, G1, G2, G4 and G5. When quering for records that have group G1, both R1 and R2 should be returned. When adding G3 to R1 the list for R2 should be: G1, G2, G3, G4 and G5. When removing G1 from R1 the list for R2 should be: G2, G3, G4 and G5. When removing G2 from R1 the list for R2 should be: G2, G3, G4 and G5. (R2 has itself also group G2.) Is this possible with sqlalchemy or has this to be done by hand? I wouldn't say by hand. An element's groups are basically its own groups unioned with the groups of its parent.So a simple recursion is the simplest way to form this group, but this only handles the object graph side of the equation, not the Query side. Thanks for the info. I'll have to digest it, because I am just starting with sqlalchemy. But it is nice to know that I could implement this functionality. On the Query side, the basic job is to formulate joins to the parent, Would it no be better to the child? Otherwise you need to traverse all records, which would be inefficient -I think- when for example only 1% of the records are in the group. Or am I overlooking something? -- Cecil Westerhof --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Is there a simple way to let records have the same groups as it parents
On Aug 28, 2008, at 10:54 AM, Cecil Westerhof wrote: On the Query side, the basic job is to formulate joins to the parent, Would it no be better to the child? Otherwise you need to traverse all records, which would be inefficient -I think- when for example only 1% of the records are in the group. Or am I overlooking something? its all joins between X and Y so at that level its the same thing. The optimizations you'd be looking for here would involve additional tables that store information such as a full path for each node, or a materialized path view of some kind (or even, nested sets, something I should eventually create an ORM example for since it's tricky). --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Is there a simple way to let records have the same groups as it parents
On Thursday 28 August 2008 18:00:08 Michael Bayer wrote: On Aug 28, 2008, at 10:54 AM, Cecil Westerhof wrote: On the Query side, the basic job is to formulate joins to the parent, Would it no be better to the child? Otherwise you need to traverse all records, which would be inefficient -I think- when for example only 1% of the records are in the group. Or am I overlooking something? its all joins between X and Y so at that level its the same thing. The optimizations you'd be looking for here would involve additional tables that store information such as a full path for each node, or a materialized path view of some kind (or even, nested sets, something I should eventually create an ORM example for since it's tricky). cecil, think well about which point of view is more important in the means of speed vs number-of-records vs needed-queries, and just then apply specific technics to denormalize the tree. e.g. nested sets (where an item knows all its children recursively - like someclass.__subclass__() ) might be good for answering which Rs belong to a group, while path-to-the-root (like someclass.mro()) might be better in other cases - e.g. which groups some R belongs to (these are just guesses, dont rely on them being 100% bcorrect). as Mike said, there are two sides of the equatoin, one is the object graph as such, another is how u represent it in db/queries over that. the representation can be very twisted towards some sort of queries - e.g. one extreme may be to store/cache all inherited-result groups for each R - and refresh the cache at any change anywhere on the path-to-root.. (cache here is actualy some records inside db) in my case i've decided that getting all objects + their assoc.values in one go/query and then doing inheritance in python is better than all else, like above cache or walking the tree in python and making many small queries to get values, for two reasons: thousands of rows and the order/priority of inheritance is changeable. i have not decided myself how to derecurse the tree yet, i'm making an N-level generated query. The results so far are that for self-recursive query (A1-A2-A3), 4 levels are enough to kill postgres (2 for sqlite). for a non-self-recursive (A1-B1-C3), 3,4,5,6 levels makes no much difference. also, if u need count over above, query.count() may not be correct, use this instead (i dont know why but count(1) is not correct over a filtered cartesian product): def exact_count( query): from sqlalchemy.sql import func m = getattr( query, 'mapper', None) #0.4 if m is None: m = query._mapper_zero() #0.5 #see Query.count() return query._col_aggregate( m.primary_key[0], lambda x: func.count( func.distinct(x)) ) mike, why not have some query._count_expr() method that defaults to count(1) but one can easily subst something else, e.g. the above count(distinct(x.dbid)) ? now i have to subclass Query and replace the whole .count() with above which is partialy copypaste from original Query.count() (and probably has too much internal stuff) have fun svil --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
