Seems to work great. Thanks so much! On Wednesday, November 7, 2018 at 11:33:11 AM UTC-8, Mike Bayer wrote: > > On Wed, Nov 7, 2018 at 1:05 PM <[email protected] <javascript:>> > 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 <[email protected]> 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 [email protected]. > >> > To post to this group, send email to [email protected]. > >> > 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 [email protected] <javascript:>. > > To post to this group, send email to [email protected] > <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 [email protected]. To post to this group, send email to [email protected]. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
