I need help with structuring the query too. I can implement a query with
raw SQL, but it involves subqueries, and I'm not sure how to translate it
to a column property (or if there's a better way which would avoid the need
for a subquery entirely)
On Wednesday, November 7, 2018 at 9:54:15 AM UTC-8, Mike Bayer wrote:
>
> On Wed, Nov 7, 2018 at 12:33 PM <phil....@freenome.com <javascript:>>
> wrote:
> >
> > I have the following two models
> >
> > class Dataset(db.Model):
> > __tablename__ = 'datasets'
> >
> > id = db.Column(db.Integer, primary_key=True)
> > creation_datetime = db.Column(db.DateTime(timezone=False),
> nullable=False)
> >
> > sample_id = db.Column(db.Integer, db.ForeignKey('samples.id'),
> nullable=False, index=True)
> > sample = db.relationship('Sample', uselist=False, innerjoin=True)
> >
> >
> > class Sample(db.Model):
> > __tablename__ = 'samples'
> >
> >
> > id = db.Column(db.Integer, primary_key=True)
> >
> > datasets = db.relationship('Dataset')
> >
> > num_datasets = column_property(
> > select([func.count(Dataset.id)]).where(Dataset.sample_id ==
> id).correlate_except(Dataset)
> > )
> >
> >
> >
> > A sample has many datasets. I would like to add a property "is_latest"
> to Dataset, probably as a column_property, which is true if the dataset has
> the latest creation_datetime of the datasets associated with its sample. In
> other words, if a sample has three datasets, the dataset whose
> creation_datetime is largest should have is_latest=True and the other two
> should have is_latest=False
> >
> > If ordering by creation_datetime isn't possible/easy, ordering by
> primary key is also acceptable. How can I construct this column_property?
> >
> > One of the issues I've encountered while trying to make this work is
> that there's a circular dependency between the two models. Since the Sample
> model uses Dataset in its num_datasets property, the Dataset class is
> currently defined first. But this means that it can't refer to Sample in
> its own properties
>
> do you need help with the structure of the SQL for is_latest or you're
> just struggling with the definitions for the two column_property
> objects in that they each require the other class? for the latter,
> you need to use a __declare_last__ :
>
> class MyClass(Base):
> # ...
>
> @classmethod
> def __declare_last__(cls):
> cls.is_latest = column_property(....)
>
>
>
> >
> > Thanks for the help
> >
> > This e-mail is private and confidential and is for the addressee only.
> If misdirected, please notify us by telephone, confirming that it has been
> deleted from your system and any hard copies destroyed. You are strictly
> prohibited from using, printing, distributing or disseminating it or any
> information contained in it save to the intended recipient.
> >
> > --
> > SQLAlchemy -
> > The Python SQL Toolkit and Object Relational Mapper
> >
> > http://www.sqlalchemy.org/
> >
> > To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> > ---
> > 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+...@googlegroups.com <javascript:>.
> > To post to this group, send email to sqlal...@googlegroups.com
> <javascript:>.
> > Visit this group at https://groups.google.com/group/sqlalchemy.
> > For more options, visit https://groups.google.com/d/optout.
>
--
This e-mail is private and confidential and is for the addressee only. If
misdirected, please notify us by telephone, confirming that it has been
deleted from your system and any hard copies destroyed. You are strictly
prohibited from using, printing, distributing or disseminating it or any
information contained in it save to the intended recipient.
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
