On Wed, Nov 7, 2018 at 1:05 PM <phil.nac...@freenome.com> wrote:
>
> 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)
OK see below for a column_property example.
as far as not using a column_property, if these Sample objects have
relatively small amount of Dataset records each, you could just load
them into memory and provide a @property for the count and is_latest
features that just look at the length of the list and the latest
dates. There's a lot of very efficient eager loading schemes now
most particularly "select in" loading.
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.ext.declarative import declared_attr
import datetime
Base = declarative_base()
class Dataset(Base):
__tablename__ = 'datasets'
id = Column(Integer, primary_key=True)
creation_datetime = Column(DateTime(timezone=False), nullable=False)
sample_id = Column(Integer, ForeignKey('samples.id'),
nullable=False, index=True)
sample = relationship('Sample', uselist=False, innerjoin=True)
@classmethod
def __declare_last__(cls):
cls.is_latest = column_property(
exists().where(cls.creation_datetime == select(
[func.max(cls.creation_datetime)]).where(
cls.sample_id == Sample.id
)
).where(cls.sample_id == Sample.id)
)
class Sample(Base):
__tablename__ = 'samples'
id = Column(Integer, primary_key=True)
datasets = relationship('Dataset')
num_datasets = column_property(
select([func.count(Dataset.id)]).where(Dataset.sample_id ==
id).correlate_except(Dataset)
)
e = create_engine("sqlite://", echo=True)
Base.metadata.create_all(e)
s = Session(e)
s.add_all([
Sample(id=1, datasets=[
Dataset(creation_datetime=datetime.datetime(2018, 11, 7, 11, 52, 0)),
Dataset(creation_datetime=datetime.datetime(2018, 11, 7, 10, 52, 0)),
Dataset(creation_datetime=datetime.datetime(2018, 11, 7, 9, 52, 0))
]),
Sample(id=2, datasets=[
Dataset(creation_datetime=datetime.datetime(2018, 11, 7, 10, 52, 0)),
Dataset(creation_datetime=datetime.datetime(2018, 11, 7, 9, 52, 0))
]),
])
s.commit()
s.close()
s1 = s.query(Sample).get(1)
print("num datasets in sample 1: %s" % s1.num_datasets)
for ds in s1.datasets:
print("Dataset creation: %s is latest: %s" %
(ds.creation_datetime, ds.is_latest))
assert ds.is_latest == (ds.creation_datetime ==
datetime.datetime(2018, 11, 7, 11, 52, 0))
>
> 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> 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.
>> > To post to this group, send email to sqlal...@googlegroups.com.
>> > 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.
--
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.
