[sqlalchemy] How to define a column property for whether model is the latest of a one-to-many relationship

Post by p***@freenome.com
I have the following two models
__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)
__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(....)

Post by p***@freenome.com
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.
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+***@googlegroups.com.
To post to this group, send email to ***@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

p***@freenome.com

2018-11-07 18:05:16 UTC

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)

Post by p***@freenome.com
I have the following two models
__tablename__ = 'datasets'
id = db.Column(db.Integer, primary_key=True)
creation_datetime = db.Column(db.DateTime(timezone=False),

nullable=False)

Post by p***@freenome.com
sample_id = db.Column(db.Integer, db.ForeignKey('samples.id'),

nullable=False, index=True)

Post by p***@freenome.com
sample = db.relationship('Sample', uselist=False, innerjoin=True)
__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)

Post by p***@freenome.com
)
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

Post by p***@freenome.com
If ordering by creation_datetime isn't possible/easy, ordering by

primary key is also acceptable. How can I construct this column_property?

Post by p***@freenome.com
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,
# ...
@classmethod
cls.is_latest = column_property(....)

Post by p***@freenome.com
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.

Post by p***@freenome.com
--
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.

Post by p***@freenome.com
---
You received this message because you are subscribed to the Google

Groups "sqlalchemy" group.

Post by p***@freenome.com
To unsubscribe from this group and stop receiving emails from it, send

<javascript:>.

Post by p***@freenome.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+***@googlegroups.com.
To post to this group, send email to ***@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Mike Bayer

2018-11-07 19:32:52 UTC

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))

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,
# ...
@classmethod
cls.is_latest = column_property(....)

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.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

p***@freenome.com

2018-11-07 19:43:58 UTC

Seems to work great. Thanks so much!

Post by p***@freenome.com
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
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()
__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
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)
)
__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)
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))

Post by p***@freenome.com
I have the following two models
__tablename__ = 'datasets'
id = db.Column(db.Integer, primary_key=True)
creation_datetime = db.Column(db.DateTime(timezone=False),

nullable=False)

Post by p***@freenome.com
sample_id = db.Column(db.Integer, db.ForeignKey('samples.id'),

nullable=False, index=True)

id).correlate_except(Dataset)

Post by p***@freenome.com
)
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

Post by p***@freenome.com
If ordering by creation_datetime isn't possible/easy, ordering by

primary key is also acceptable. How can I construct this column_property?

Post by p***@freenome.com
One of the issues I've encountered while trying to make this work is

Post by Mike Bayer
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,
# ...
@classmethod
cls.is_latest = column_property(....)

Post by p***@freenome.com
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.

Post by p***@freenome.com
--
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.

Post by p***@freenome.com
---
You received this message because you are subscribed to the Google

Groups "sqlalchemy" group.