Dear All,
I have a series of tables which are used to track "Material" and
"Composite".
Material is associated with Composite via an association table
MaterialCompositeAssoc. Similarly, Composite can be comprised of Composite
itself and its association is via another table
CompositeCompositeAssociation.
What I am trying to achieve is a summary of material that has been added to
a composite from new Material and/or Composite by summing the masses.
Support tables include Unit which provides unit conversions to the stored
values. Below is my best guess at what I should be doing to get a mass
balance on the material flowing in and out of the composite. Needless to
say it is not producing the result that I want (btw it return nothing).
class Composite(Base)
#...
mass_magnitude = column_property(
select([func.sum(MaterialCompositeAssoc.mass_magnitude*Unit.factor)],\
MaterialCompositeAssoc.mass_unit_id==Unit.id).\
where(MaterialCompositeAssoc.material_id==id).as_scalar() +
\
select([func.sum(CompositeCompositeAssoc.mass_magnitude*Unit.factor)],\
CompositeCompositeAssoc.mass_unit_id==Unit.id).\
where(CompositeCompositeAssoc.composite_sink_id==id).as_scalar() -\
select([func.sum(CompositeCompositeAssoc.mass_magnitude*Unit.factor)],\
CompositeCompositeAssoc.mass_unit_id==Unit.id).\
where(CompositeCompositeAssoc.composite_source_id==id).as_scalar()
)
Now if I decompose the attempt above into its parts then they "do the right
thing". That is if I set the column_property to the definition below then
the result is as expected. So on and so forth for the remaining two selects.
mass_magnitude = column_property(
select([func.sum(MaterialCompositeAssoc.mass_magnitude*Unit.factor)],\
MaterialCompositeAssoc.mass_unit_id==Unit.id).\
where(MaterialCompositeAssoc.material_id==id).as_scalar()
)
I feel, independent of whether or not this is the correct way, that I am
quite close to achieving what I want but not quite there.
I have also tried to wrap the selects into another select (select(
[func.sum(...]) but the result is then a sum of all of the values and that
being used in each row of the column.
A cure for my problem would be appreciated.
Kurt
--
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.
