your example is incomplete in that it is missing named columns such as “part_id” and such, but when I fill those in I’m able to form a SELECT using the properties you refer to:
SELECT (SELECT sum(nestingsheetlayoutitem.quantity) AS sum_1 FROM nestingsheetlayoutitem, nestingsheetlayout WHERE nestingsheetlayout.project_id = :project_id_1 AND jobitem.part_id = nestingsheetlayoutitem.part_id) AS anon_1, (SELECT job.project_id FROM job WHERE job.id = jobitem.job_id) AS anon_2, jobitem.id AS jobitem_id, jobitem.job_id AS jobitem_job_id, jobitem.quantity AS jobitem_quantity, jobitem.part_id AS jobitem_part_id FROM jobitem I can see that this query has problems, such as that your column property isn’t equating nestingsheetlayoutitem and nestingsheetlayout to each other and you might be getting too many rows back, but the query is representing as close as I can tell what you’re asking it to render. The subqueries correlate to job_item on the outside, and sometimes there’s extra things needed to make these correlations work out OK but here they seem to be unambiguous. please work with the attached to show more specifically the part that is not working. I will note that column_property does have some configurational limitations within declarative, in that if you wanted to refer to a column_property attached to JobItem directly you have to jump through some extra hoops at the moment, there’s plans to get column_props to be more flexible within declarative. But the mapping here doesn’t seem to be running into those. On May 31, 2014, at 10:54 AM, Vladimir Iliev <[email protected]> wrote: > Hi, > > i'm trying to add a column property with where clause that depends on other 2 > column properties (see JobItem.nested_quantity) in the attached file) but > looks like it's not possible. > > Any ideas if it's possible to achieve same result one way or another ? > > Thanks > > -- > 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 [email protected]. > To post to this group, send email to [email protected]. > Visit this group at http://groups.google.com/group/sqlalchemy. > For more options, visit https://groups.google.com/d/optout. > <example.py>
-- 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 [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
On May 31, 2014, at 10:54 AM, Vladimir Iliev <[email protected]> wrote:
|
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base, declared_attr
Base = declarative_base()
class Project(Base):
__tablename__ = 'project'
id = Column(Integer, autoincrement=True, primary_key=True)
class NestingSheetLayout(Base):
__tablename__ = 'nestingsheetlayout'
id = Column(Integer, autoincrement=True, primary_key=True)
project_id = Column(None, ForeignKey(Project.id, ondelete='CASCADE'))
project = relationship(Project,
backref=backref('nesting_sheet_layouts',
cascade='all,delete-orphan', passive_deletes=True))
class NestingSheetLayoutItem(Base):
__tablename__ = 'nestingsheetlayoutitem'
id = Column(Integer, autoincrement=True, primary_key=True)
part_id = Column(Integer)
quantity = Column(Integer)
nesting_sheet_layout_id = Column(None, ForeignKey(NestingSheetLayout.id, ondelete='CASCADE'))
nesting_sheet_layout = relationship(NestingSheetLayout,
backref=backref('items', cascade='all,delete-orphan', passive_deletes=True))
@declared_attr
def project_id(cls):
return column_property(
select(
[NestingSheetLayout.project_id],
NestingSheetLayout.id==cls.nesting_sheet_layout_id
),
)
class Job(Base):
__tablename__ = 'job'
id = Column(Integer, autoincrement=True, primary_key=True)
project_id = Column(None, ForeignKey(Project.id))
project = relationship(Project,
backref=backref('jobs', cascade='all, delete-orphan'))
class JobItem(Base):
__tablename__ = 'jobitem'
id = Column(Integer, autoincrement=True, primary_key=True)
job_id = Column(None, ForeignKey(Job.id, ondelete='CASCADE'))
job = relationship(Job, lazy=True,
backref=backref('items', cascade='all', passive_deletes=True))
quantity = Column(Integer)
part_id = Column(Integer)
@declared_attr
def project_id(cls):
return column_property(
select([Job.project_id], Job.id==cls.job_id),
)
@declared_attr
def nested_quantity(cls):
return column_property(
select(
[func.sum(NestingSheetLayoutItem.quantity)],
(
(cls.project_id==NestingSheetLayout.project_id) &
(cls.part_id == NestingSheetLayoutItem.part_id)
)
)
)
s = Session()
print s.query(JobItem)
