[sqlalchemy] Re: Using order_by in an association many-to-many relationship with columns from the association object

2009-03-11 Thread Scott 

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

2009-03-11 Thread Michael Bayer 


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

2009-03-10 Thread Michael Bayer 

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
-~--~~~~--~~--~--~---