[sqlalchemy] Re: Many to many self referential relationship.
Thank you Michael - it's always good to get some validation when pushing your own knowledge of a system. I'll check out the trunk tomorrow, give it a whirl and report any findings then. Thanks again for the quick response, Martin On Feb 20, 6:31 pm, Michael Bayer [EMAIL PROTECTED] wrote: On Feb 20, 2008, at 1:01 PM, Martin Pengelly-Phillips wrote: I have attempted to solve this by using the concept of 'forwardAssociations' with a backref of 'backwardAssociations' and then a custom property 'associations' that retrieves and sets the real attributes accordingly (let me know if you would like to see this code as well). However, even with this set up I find it hard to figure out how to then perform a query such as: # Get all entities that are in some way associated with entity 2 (without using entity2.associations property) session.query(Entity).filter(or_( Entity.forwardAssociations.any(Entity.id==2), Entity.backwardAssociations.any(Entity.id==2) )).all() The query this generates though uses the same id for both sides of the association table which cannot therefore result in a match: entities.id = entitiesEntities.entity1_id AND entitiesEntities.entity2_id = entities.id AND entities.id = ? well i think everything you're doing is on the right track. The any() operator and its friends have just been repaired in the latest trunk to recognize a self-referential relation and apply the appropriate aliasing to half of the join condition. I haven't tested it with a many-to-many self referential yet but I think it should work. give it a try and let me know what you get; I can add some m2m tests for it and ensure that its working if its not already. we also are working on getting better support for user-defined custom properties going such that they can be seamlessly used within Queries, so that you could also construct your query using your associations property, if you can define how comparison operations should be done. There is a way to do this right now using a slight bit of non-public API, where you can see an example of such inhttp://www.sqlalchemy.org/trac/browser/sqlalchemy/trunk/examples/vert... ; the comparable_property allows the injection of a Comparator object from where you can define things like __eq__(), any(), etc. --~--~-~--~~~---~--~~ 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: Many to many self referential relationship.
[EMAIL PROTECTED] wrote: slightly OT... http://www.sqlalchemy.org/trac/browser/sqlalchemy/trunk/examples/ve rtical/dictlike-polymorphic.py hmmm, figuratively speaking, what u describe above is a sort of single-table-inheritance approach over a single-value classes, one per value-type, right? so u have objects, which have attributes of any name and a value of one of the listed types. same-name attributes may have different type-of-values allright. if a class/value has more than just the value (e.g. measurement, units, scale, etc), and there are lots and lots of them, single-table approach would be an overkill, i suppose. Any specific recipe to follow here? That example is more of a mapping recipe than a scratch design recipe: I've seen that properties table over and over in all sorts of legacy schemas. Sometimes it's just like that, sometimes it's shared storage for lots of entities and the setup is more like vertical.py. Sometimes it's worse and has foreign keys. If you *are* doing a scratch design of a triple store, then I'd personally look outside of the RDBMS. Perhaps the RDFAlchemy project, which I've been meaning to check out... --~--~-~--~~~---~--~~ 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] new polymorphic criterion operators (was: Problem with Query)
hey there - in addition to the querying strategies I emailed to you privately, the latest trunk also has a new operator of_type which allows this pattern (as a review, Person joins to Role via the roles relation, Employee is a subclass of Role): session .query (Person ).filter (Person .roles.of_type(Employee).any(and_(Role.type=='ResearchAssociate', Employee.visible==True))).all() of_type(class_or_mapper) will cause the any() and has() operators to join to the subclass' mapped table instead of the base class of the relation(). It also works with join(): session .query (Person ).join (Person .roles.of_type(Employee)).filter(and_(Role.type=='ResearchAssociate', Employee.visible==True)).all() --~--~-~--~~~---~--~~ 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] ordering by number of objects in a many-to-many relationship
Hi group, Is there an easy way of ordering objects by the number of objects that stand in a ManyToMany relationship with it? My use case is the following: I have two types of entities, tags and articles, that stand in a ManyToMany relation. I want to order the tags by the number of articles they label and then assign them different CSS classes depending on their popularity. I know how to do it in a lengthy and rather ugly way, but I suspect there should be an elegant way (I know a simple solution in SQL). Cheers and thanks in advance, -- Richard --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---