oh, I should have even seen this in the compiled SQL, sorry. See that
:project_id_1 ? that's a bound parameter. It's coming out because when you
send the column_property() into the expression like that, it isn't recognized
as a SQL-producing object. If OTOH you passed in "JobItem.project_id" after
JobItem were mapped, then it would work because project_id would now be an
attribute with a SQL expression set up, but as you can see, you can't do that
right here because you're still in the class def; this is the improvement to
column_property I referred to. Actually this can work as is based on the patch
I just added to
https://bitbucket.org/zzzeek/sqlalchemy/issue/3050/support-lambdas-strings-in-column_property.
But don't even worry about that patch, workaround right now is just to refer to
.expression:
@declared_attr
def nested_quantity(cls):
return column_property(
select(
[func.sum(NestingSheetLayoutItem.quantity)],
(
(cls.project_id.expression==NestingSheetLayout.project_id) &
(cls.part_id == NestingSheetLayoutItem.part_id)
)
)
)
I'll try to get that patch in for 0.9.5.
On Jun 2, 2014, at 6:09 AM, Vladimir Iliev <[email protected]> wrote:
> sorry, i have copy-and-paste incomplete parts of my real model definitions.
>
> run the attached file and you will see the exception....
>
> sqlalchemy.exc.InterfaceError: (InterfaceError) Error binding parameter 0 -
> probably unsupported type. u'SELECT (SELECT
> sum(nestingsheetlayoutitem.quantity) AS sum_1 \nFROM nestingsheetlayoutitem,
> nestingsheetlayout \nWHERE nestingsheetlayout.project_id = ? AND
> jobitem.part_id = nestingsheetlayoutitem.part_id) AS anon_1, (SELECT
> job.project_id \nFROM job \nWHERE 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 \nFROM jobitem'
> (<ColumnProperty at 0x935c48c; no key>,)
>
>
> On Sunday, June 1, 2014 1:47:06 AM UTC+3, Michael Bayer wrote:
> 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.
>
>
> --
> 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 [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.
> <colprop.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.
