[sqlalchemy] order_by column in adjoining table

2013-07-26 Thread Andrew S 
Given the following classes/tables:
class TagList(Base):
TagID = Column(Integer, primary_key=True)
trafficInputs = relationship('TrafficInput', secondary='TagCaseLink', 
order_by='Traffic.Time')

class TagCaseLink(Base):
TagID = Column(Integer, ForeignKey('TagList.TagID'), primary_key=True)
TrafficInputID = Column(Integer, 
ForeignKey('TrafficInput.TrafficInputID'))

class TrafficInput(Base):
TrafficInputID = Column(Integer, primary_key=True)
TrafficDeviceID = Column(Integer, 
ForeignKey('TrafficDevice.TrafficDeviceID'), nullable=False)
ConfigID = Column(Integer, ForeignKey('Config.ConfigID'), 
nullable=False)

class TrafficDevice(Base):
TrafficDeviceID = Column(Integer, primary_key=True)
TrafficID = Column(Integer, ForeignKey('Traffic.TrafficID'), 
nullable=False)
DeviceID = Column(Integer, ForeignKey('Device.DeviceID'), 
nullable=False)

class Traffic(Base):
TrafficID = Column(Integer, primary_key=True)
TrafficName = Column(String, nullable=False)
Time = Column(DateTime, nullable=False)

I get the following error: sqlalchemy.exc.ProgrammingError: 
(ProgrammingError) missing FROM-clause entry for table Traffic

So TagList contains a list of trafficInputs that I want ordered by 
Traffic.Time. Is there anyway of doing this?

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.

Re: [sqlalchemy] order_by column in adjoining table

2013-07-26 Thread Michael Bayer 
you'd need to make a relationship() that explicitly joins out among all four 
tables so that the .Time column is available.  To accomplish that you'd 
probably want to make a non primary mapper to join to, it would look something 
like this:

sel = select([TrafficInput, 
Traffic.Time]).select_from(join(TagCaseLink, 
TrafficInput).join(TrafficDevice).join(Traffic)).alias()
m = mapper(TrafficInput, sel)

TagList.trafficInputs = relationship(m, order_by=sel.c.Time)

It's probably not how I'd go as it's too complicated.  I'd either change the 
schema to work more naturally (that you need to join across four tables to get 
a certain ordering is a bit of a red flag) or possibly just order in memory, 
assuming those relationships all tend to get loaded.





On Jul 26, 2013, at 3:26 AM, Andrew S andrew.suce...@gmail.com wrote:

 Given the following classes/tables:
 class TagList(Base):
 TagID = Column(Integer, primary_key=True)
 trafficInputs = relationship('TrafficInput', secondary='TagCaseLink', 
 order_by='Traffic.Time')
 
 class TagCaseLink(Base):
 TagID = Column(Integer, ForeignKey('TagList.TagID'), primary_key=True)
 TrafficInputID = Column(Integer, 
 ForeignKey('TrafficInput.TrafficInputID'))
 
 class TrafficInput(Base):
 TrafficInputID = Column(Integer, primary_key=True)
 TrafficDeviceID = Column(Integer, 
 ForeignKey('TrafficDevice.TrafficDeviceID'), nullable=False)
 ConfigID = Column(Integer, ForeignKey('Config.ConfigID'), nullable=False)
 
 class TrafficDevice(Base):
 TrafficDeviceID = Column(Integer, primary_key=True)
 TrafficID = Column(Integer, ForeignKey('Traffic.TrafficID'), 
 nullable=False)
 DeviceID = Column(Integer, ForeignKey('Device.DeviceID'), nullable=False)
 
 class Traffic(Base):
 TrafficID = Column(Integer, primary_key=True)
 TrafficName = Column(String, nullable=False)
 Time = Column(DateTime, nullable=False)
 
 I get the following error: sqlalchemy.exc.ProgrammingError: 
 (ProgrammingError) missing FROM-clause entry for table Traffic
 
 So TagList contains a list of trafficInputs that I want ordered by 
 Traffic.Time. Is there anyway of doing this?
 
 
 -- 
 You received this message because you are subscribed to the Google Groups 
 sqlalchemy group.
 To unsubscribe from this group and stop receiving emails from it, send an 
 email to sqlalchemy+unsubscr...@googlegroups.com.
 To post to this group, send email to sqlalchemy@googlegroups.com.
 Visit this group at http://groups.google.com/group/sqlalchemy.
 For more options, visit https://groups.google.com/groups/opt_out.
  
  

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.

Re: [sqlalchemy] order_by column in adjoining table

2013-07-26 Thread Michael Bayer 
oh that should be mapper(TrafficInput, sel, non_primary=True)


On Jul 26, 2013, at 10:41 AM, Michael Bayer mike...@zzzcomputing.com wrote:

 you'd need to make a relationship() that explicitly joins out among all four 
 tables so that the .Time column is available.  To accomplish that you'd 
 probably want to make a non primary mapper to join to, it would look 
 something like this:
 
   sel = select([TrafficInput, 
 Traffic.Time]).select_from(join(TagCaseLink, 
 TrafficInput).join(TrafficDevice).join(Traffic)).alias()
   m = mapper(TrafficInput, sel)
 
   TagList.trafficInputs = relationship(m, order_by=sel.c.Time)
 
 It's probably not how I'd go as it's too complicated.  I'd either change the 
 schema to work more naturally (that you need to join across four tables to 
 get a certain ordering is a bit of a red flag) or possibly just order in 
 memory, assuming those relationships all tend to get loaded.
 
 
 
 
 
 On Jul 26, 2013, at 3:26 AM, Andrew S andrew.suce...@gmail.com wrote:
 
 Given the following classes/tables:
 class TagList(Base):
 TagID = Column(Integer, primary_key=True)
 trafficInputs = relationship('TrafficInput', secondary='TagCaseLink', 
 order_by='Traffic.Time')
 
 class TagCaseLink(Base):
 TagID = Column(Integer, ForeignKey('TagList.TagID'), primary_key=True)
 TrafficInputID = Column(Integer, 
 ForeignKey('TrafficInput.TrafficInputID'))
 
 class TrafficInput(Base):
 TrafficInputID = Column(Integer, primary_key=True)
 TrafficDeviceID = Column(Integer, 
 ForeignKey('TrafficDevice.TrafficDeviceID'), nullable=False)
 ConfigID = Column(Integer, ForeignKey('Config.ConfigID'), nullable=False)
 
 class TrafficDevice(Base):
 TrafficDeviceID = Column(Integer, primary_key=True)
 TrafficID = Column(Integer, ForeignKey('Traffic.TrafficID'), 
 nullable=False)
 DeviceID = Column(Integer, ForeignKey('Device.DeviceID'), nullable=False)
 
 class Traffic(Base):
 TrafficID = Column(Integer, primary_key=True)
 TrafficName = Column(String, nullable=False)
 Time = Column(DateTime, nullable=False)
 
 I get the following error: sqlalchemy.exc.ProgrammingError: 
 (ProgrammingError) missing FROM-clause entry for table Traffic
 
 So TagList contains a list of trafficInputs that I want ordered by 
 Traffic.Time. Is there anyway of doing this?
 
 
 -- 
 You received this message because you are subscribed to the Google Groups 
 sqlalchemy group.
 To unsubscribe from this group and stop receiving emails from it, send an 
 email to sqlalchemy+unsubscr...@googlegroups.com.
 To post to this group, send email to sqlalchemy@googlegroups.com.
 Visit this group at http://groups.google.com/group/sqlalchemy.
 For more options, visit https://groups.google.com/groups/opt_out.
  
  
 
 
 -- 
 You received this message because you are subscribed to the Google Groups 
 sqlalchemy group.
 To unsubscribe from this group and stop receiving emails from it, send an 
 email to sqlalchemy+unsubscr...@googlegroups.com.
 To post to this group, send email to sqlalchemy@googlegroups.com.
 Visit this group at http://groups.google.com/group/sqlalchemy.
 For more options, visit https://groups.google.com/groups/opt_out.
  
  

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.