[sqlalchemy] Re: Using order_by in an association many-to-many relationship with columns from the association object
When I do this... cpt_codes = ManyToMany( ... order_by = procedure_cpt_codes.c.cpt_codes_idx ) # procedure_cpt_codes is the JOIN table in between the parent (procedure) --- children (cpt_codes) # procedure_cpt_codes has 3 columns...procedure_id (foreign key), cpt_code_id (foreign key) and cpt_codes_idx that's sorted ...I get the following error: TypeError: 'Column' object is not iterable I had tried passing order_by several configurations including the column object as you suggested to no avail. It seemed when I looked through the documentation and source (which was a few days ago now so my memory may be fuzzy) for ManyToMany order_by was expecting a string that was the name of a column on the secondary table (CptCode in my example). I've since started to try to shoe horn the relationship with an association object representing the join table which seemed the only way to access a non-foreign key column on the join table to order the collection by. If there's a better way to do this with the order_by parameter I'd love to figure it out b/c association object syntax with ORM gets REALLY messy. Thanks, Scott On Mar 10, 12:03 pm, Michael Bayer mike...@zzzcomputing.com wrote: order_by accepts a Column object, i.e. table.c.whatever, so pass that in. Scott wrote: Is there a way with the current iteration of SQLAlchemy to add a column to the association table in a many-to-many relationship with that column used to order the join? I looked at the order_by attribute of the ManyToMany() relationship definition, but it seems that this is expecting a string naming the column in the related entity. I'm using Elixir on top of alchemy, but here are my relevant class and table definitions: procedure_cpt_codes = Table('procedure_cpt_codes', metadata, autoload=True) class CptCode(Entity): using_options(tablename='cpt_codes', autosetup=True) name = Field(Unicode) code = Field(Unicode) description= Field(Unicode) class Procedure(Entity): using_options(tablename='procedures', autosetup=True) complications = OneToMany('Complication') cpt_codes = ManyToMany( 'CptCode', table = procedure_cpt_codes, lazy=False, foreign_keys = lambda: [ procedure_cpt_codes.c.procedure_id, procedure_cpt_codes.c.cpt_code_id ], primaryjoin = lambda: Procedure.id == procedure_cpt_codes.c.procedure_id, secondaryjoin = lambda: CptCode.id == procedure_cpt_codes.c.cpt_code_id, order_by = procedure_cpt_codes.c.cpt_codes_idx ) procedure_date = Field(Date) I get the following exception when run as listed: Traceback (most recent call last): File /System/Library/Frameworks/Python.framework/Versions/Current/ Extras/lib/python/PyObjC/PyObjCTools/AppHelper.py, line 235, in runEventLoop main(argv) File /Users/tswall/Documents/workspace/Cocoa/python/Epdb/build/ Debug/Epdb.app/Contents/Resources/MyController.py, line 15, in buttonPushed_ for instance in Patient.query.all(): File /Library/Python/2.5/site-packages/Elixir-0.6.1-py2.5.egg/ elixir/entity.py, line 641, in __get__ elixir.setup_all() File /Library/Python/2.5/site-packages/Elixir-0.6.1-py2.5.egg/ elixir/__init__.py, line 145, in setup_all File /Library/Python/2.5/site-packages/Elixir-0.6.1-py2.5.egg/ elixir/entity.py, line 816, in setup_entities method() File /Library/Python/2.5/site-packages/Elixir-0.6.1-py2.5.egg/ elixir/entity.py, line 421, in setup_properties self.call_builders('create_properties') File /Library/Python/2.5/site-packages/Elixir-0.6.1-py2.5.egg/ elixir/entity.py, line 433, in call_builders getattr(builder, what)() File /Library/Python/2.5/site-packages/Elixir-0.6.1-py2.5.egg/ elixir/relationships.py, line 417, in create_properties self.target._descriptor.translate_order_by(kwargs['order_by']) File /Library/Python/2.5/site-packages/Elixir-0.6.1-py2.5.egg/ elixir/entity.py, line 322, in translate_order_by for colname in order_by: TypeError: 'Column' object is not iterable When I change the order_by above to order_by = 'procedure_cpt_codes.c.cpt_codes_idx' #or 'cpt_codes_idx' I get an error that it can't find column 'cpt_codes_idx' on relation table 'CptCode'. Any advice would be appreciated! Scott --~--~-~--~~~---~--~~ 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 sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Using order_by in an association many-to-many relationship with columns from the association object
On Mar 11, 2009, at 6:17 PM, Scott wrote: When I do this... cpt_codes = ManyToMany( ... order_by = procedure_cpt_codes.c.cpt_codes_idx ) # procedure_cpt_codes is the JOIN table in between the parent (procedure) --- children (cpt_codes) # procedure_cpt_codes has 3 columns...procedure_id (foreign key), cpt_code_id (foreign key) and cpt_codes_idx that's sorted ...I get the following error: TypeError: 'Column' object is not iterable this would suggest the order_by argument on Elixir's ManyToMany function is expecting a list. Try asking on the Elixir mailing list about this issue since this is not a SQLAlchemy issue. --~--~-~--~~~---~--~~ 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 sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Using order_by in an association many-to-many relationship with columns from the association object
order_by accepts a Column object, i.e. table.c.whatever, so pass that in. Scott wrote: Is there a way with the current iteration of SQLAlchemy to add a column to the association table in a many-to-many relationship with that column used to order the join? I looked at the order_by attribute of the ManyToMany() relationship definition, but it seems that this is expecting a string naming the column in the related entity. I'm using Elixir on top of alchemy, but here are my relevant class and table definitions: procedure_cpt_codes = Table('procedure_cpt_codes', metadata, autoload=True) class CptCode(Entity): using_options(tablename='cpt_codes', autosetup=True) name = Field(Unicode) code = Field(Unicode) description= Field(Unicode) class Procedure(Entity): using_options(tablename='procedures', autosetup=True) complications = OneToMany('Complication') cpt_codes = ManyToMany( 'CptCode', table = procedure_cpt_codes, lazy=False, foreign_keys = lambda: [ procedure_cpt_codes.c.procedure_id, procedure_cpt_codes.c.cpt_code_id ], primaryjoin = lambda: Procedure.id == procedure_cpt_codes.c.procedure_id, secondaryjoin = lambda: CptCode.id == procedure_cpt_codes.c.cpt_code_id, order_by = procedure_cpt_codes.c.cpt_codes_idx ) procedure_date = Field(Date) I get the following exception when run as listed: Traceback (most recent call last): File /System/Library/Frameworks/Python.framework/Versions/Current/ Extras/lib/python/PyObjC/PyObjCTools/AppHelper.py, line 235, in runEventLoop main(argv) File /Users/tswall/Documents/workspace/Cocoa/python/Epdb/build/ Debug/Epdb.app/Contents/Resources/MyController.py, line 15, in buttonPushed_ for instance in Patient.query.all(): File /Library/Python/2.5/site-packages/Elixir-0.6.1-py2.5.egg/ elixir/entity.py, line 641, in __get__ elixir.setup_all() File /Library/Python/2.5/site-packages/Elixir-0.6.1-py2.5.egg/ elixir/__init__.py, line 145, in setup_all File /Library/Python/2.5/site-packages/Elixir-0.6.1-py2.5.egg/ elixir/entity.py, line 816, in setup_entities method() File /Library/Python/2.5/site-packages/Elixir-0.6.1-py2.5.egg/ elixir/entity.py, line 421, in setup_properties self.call_builders('create_properties') File /Library/Python/2.5/site-packages/Elixir-0.6.1-py2.5.egg/ elixir/entity.py, line 433, in call_builders getattr(builder, what)() File /Library/Python/2.5/site-packages/Elixir-0.6.1-py2.5.egg/ elixir/relationships.py, line 417, in create_properties self.target._descriptor.translate_order_by(kwargs['order_by']) File /Library/Python/2.5/site-packages/Elixir-0.6.1-py2.5.egg/ elixir/entity.py, line 322, in translate_order_by for colname in order_by: TypeError: 'Column' object is not iterable When I change the order_by above to order_by = 'procedure_cpt_codes.c.cpt_codes_idx' #or 'cpt_codes_idx' I get an error that it can't find column 'cpt_codes_idx' on relation table 'CptCode'. Any advice would be appreciated! Scott --~--~-~--~~~---~--~~ 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 sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---