Hello,
Sqlalchemy Version : 0.9.4
Engine : firebird+fdb
RDBMS : Firebird 2.5
I am trying to selected limited rows from a mapper class which contains
column_property with limit. But number or rows returned is not what I
expected. When I turned on debugging I realized that, SqlAlchemy is giving
parameters in wrong order. Am I missing something or is there a bug in
SqlAlchemy?
Mapper Class :
~~~~~~~~~~~~
class Issue(Base, MkMixin):
id = Column(BigInteger, Sequence('gn_issue'), primary_key=True)
parent_id = Column(BigInteger, ForeignKey("issue.id",
ondelete="CASCADE",
onupdate="CASCADE",
name="fk_issue_parent"))
project_id = Column(BigInteger, ForeignKey("project.id",
ondelete="CASCADE",
onupdate="CASCADE",
name="fk_issue_project"),
nullable=False)
project_code = column_property(select(['project.code'],
from_obj='Project').where('Project.id=Issue.project_id'))
usr_id_from = Column(BigInteger, ForeignKey("usr.id",
ondelete="NO ACTION",
onupdate="CASCADE",
name="fk_issue_usr_from"),
nullable=False)
usr_code_from = column_property(select(['usr.code'],
from_obj='Usr').where('Usr.id=Issue.usr_id_from'))
usr_id_assigned = Column(BigInteger, ForeignKey("usr.id",
ondelete="NO ACTION",
onupdate="CASCADE",
name="fk_issue_usr_assigned"))
usr_code_assigned = column_property(select(['usr.code'],
from_obj='Usr').where('Usr.id=Issue.usr_id_assigned'))
category_id = Column(BigInteger, ForeignKey("dfissuecategory.id",
ondelete="No ACTION",
onupdate="CASCADE",
name="fk_issue_dfissuecategory"),
nullable=False)
category = column_property(select(['DfIssueCategory.code'],
from_obj='DfIssueCategory').where('DfIssueCategory.id=Issue.category_id'))
status_id = Column(BigInteger, ForeignKey("dfissuestatus.id",
ondelete="NO ACTION",
onupdate="CASCADE",
name="fk_issue_dfissuestatus"),
nullable=False)
status = column_property(select(['DfIssueStatus.code'],
from_obj='DfIssueStatus').where('DfIssueStatus.id=Issue.status_id'))
priority_id = Column(BigInteger, ForeignKey("dfissuepriority.id",
ondelete="NO ACTION",
onupdate="CASCADE",
name="fk_issue_dfissuepriority"),
nullable=False)
priority = column_property(select(['DfIssuePriority.code'],
from_obj='DfIssuePriority').where('DfIssuePriority.id=Issue.priority_id'))
dt_open = Column(Date)
dt_due = Column(Date)
dt_plan = Column(Date)
estimated_hours = Column(Numeric(9, 2))
last_update = column_property(select(['Issue_Changeset.zlins_dttm'],
from_obj='Issue_Changeset').\
where('Issue_Changeset.issue_id=Issue.id').\
order_by(desc('Issue_Changeset.zlins_dttm')).limit(1))
title = Column(String(200))
description = Column(TEXT)
is_private = Column(Boolean, nullable=False, default=False)
done_ratio = Column(Integer, default=0)
~~~~~~~~~~
>From this class I am trying to select first 10 rows as :
db.query(Issue).filter(Issue.usr_id_assigned ==
1).order_by(desc(Issue.dt_open), desc(Issue.id)).limit(10)
Generated Sql Is :
2014-04-30 22:23:53,961 INFO sqlalchemy.engine.base.Engine SELECT FIRST
?(SELECT project.code
FROM Project
WHERE Project.id=Issue.project_id) AS anon_1, (SELECT usr.code
FROM Usr
WHERE Usr.id=Issue.usr_id_from) AS anon_2, (SELECT usr.code
FROM Usr
WHERE Usr.id=Issue.usr_id_assigned) AS anon_3, (SELECT DfIssueCategory.code
FROM DfIssueCategory
WHERE DfIssueCategory.id=Issue.category_id) AS anon_4, (SELECT
DfIssueStatus.code
FROM DfIssueStatus
WHERE DfIssueStatus.id=Issue.status_id) AS anon_5, (SELECT
DfIssuePriority.code
FROM DfIssuePriority
WHERE DfIssuePriority.id=Issue.priority_id) AS anon_6, (SELECT FIRST ?
Issue_Changeset.zlins_dttm
FROM Issue_Changeset
WHERE Issue_Changeset.issue_id=Issue.id ORDER BY Issue_Changeset.zlins_dttm
DESC) AS anon_7, issue.id AS issue_id, issue.parent_id AS issue_parent_id,
issue.project_id AS issue_project_id, issue.usr_id_from AS
issue_usr_id_from, issue.usr_id_assigned AS issue_usr_id_assigned,
issue.category_id AS issue_category_id, issue.status_id AS issue_status_id,
issue.priority_id AS issue_priority_id, issue.dt_open AS issue_dt_open,
issue.dt_due AS issue_dt_due, issue.dt_plan AS issue_dt_plan,
issue.estimated_hours AS issue_estimated_hours, issue.title AS issue_title,
issue.description AS issue_description, issue.is_private AS
issue_is_private, issue.done_ratio AS issue_done_ratio, issue.zlins_dttm AS
issue_zlins_dttm, issue.zlins_usr AS issue_zlins_usr, issue.zlupd_dttm AS
issue_zlupd_dttm, issue.zlupd_usr AS issue_zlupd_usr
FROM issue
WHERE issue.usr_id_assigned = ? ORDER BY issue.dt_open DESC, issue.id DESC
2014-04-30 22:23:53,961 INFO sqlalchemy.engine.base.Engine (1, 10, 1)
I think correct order should be : (10, 1, 1)
Any help is greatly appreciated.
Best regards.
--
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.
