class OTUSampleOTU(SchemaMixin, Base):
__table__ = create_materialized_view(
name='otu_sample_otu',
selectable=select(
[
SampleOTU.sample_id,
func.count(SampleOTU.otu_id).label("richness"),
func.sum(SampleOTU.count).label("count"),
func.count(SampleOTU.count_20k).label(
"richness_20k"), # Will be 0 if all null
func.sum(SampleOTU.count_20k).label(
"sum_count_20k"), # Will be null if all null
] + taxonomy_rank_id_attrs +
[Taxonomy.amplicon_id, Taxonomy.traits],
from_obj=(SampleOTU.__table__.join(OTU).join(taxonomy_otu).join(
Taxonomy))).group_by(SampleOTU.sample_id).group_by(
*taxonomy_rank_id_attrs).group_by(
Taxonomy.amplicon_id).group_by(Taxonomy.traits),
metadata=Base.metadata,
indexes=_sample_otu_indexes('otu_sample_otu_index_') + [
Index('otu_sample_otu_index_sample_id_idx', 'sample_id'),
Index('otu_sample_otu_index_amplicon_id_idx', 'amplicon_id'),
Index('otu_sample_otu_index_traits_idx',
'traits',
postgresql_using='gin'),
])
class ArticleMV(Base):
__table__ = create_materialized_view(
name='article_mv',
selectable=sa.select([
Article.id, Article.name,
User.id.label('author_id'),
User.name.label('author_name')
],
from_obj=(Article.__table__.join(
User, Article.author_id == User.id))),
metadata=Base.metadata,
indexes=[sa.Index('article_mv_id_idx', 'id')])
class OTUSampleOTU20K(SchemaMixin, Base):
__table__ = create_materialized_view(
name='otu_sample_otu_20k',
selectable=select(
[
SampleOTU20K.sample_id,
func.count(SampleOTU20K.otu_id).label("richness"),
func.sum(SampleOTU20K.count).label("count"),
Taxonomy.taxonomy_source_id,
Taxonomy.kingdom_id,
Taxonomy.phylum_id,
Taxonomy.class_id,
Taxonomy.order_id,
Taxonomy.family_id,
Taxonomy.genus_id,
Taxonomy.species_id,
OTU.amplicon_id,
Taxonomy.traits,
],
from_obj=(
SampleOTU20K.__table__
.join(OTU, OTU.id == SampleOTU20K.otu_id).join(
Taxonomy, OTU.id == Taxonomy.otu_id))
).group_by(SampleOTU20K.sample_id)
.group_by(Taxonomy.taxonomy_source_id)
.group_by(Taxonomy.kingdom_id)
.group_by(Taxonomy.phylum_id)
.group_by(Taxonomy.class_id)
.group_by(Taxonomy.order_id)
.group_by(Taxonomy.family_id)
.group_by(Taxonomy.genus_id)
.group_by(Taxonomy.species_id)
.group_by(OTU.amplicon_id)
.group_by(Taxonomy.traits),
metadata=Base.metadata,
indexes=[
Index('otu_sample_otu_20k_index_taxonomy_source_id_idx', 'taxonomy_source_id'),
Index('otu_sample_otu_20k_index_sample_id_idx', 'sample_id'),
Index('otu_sample_otu_20k_index_kingdom_id_idx', 'kingdom_id'),
Index('otu_sample_otu_20k_index_phylum_id_idx', 'phylum_id'),
Index('otu_sample_otu_20k_index_class_id_idx', 'class_id'),
Index('otu_sample_otu_20k_index_order_id_idx', 'order_id'),
Index('otu_sample_otu_20k_index_family_id_idx', 'family_id'),
Index('otu_sample_otu_20k_index_genus_id_idx', 'genus_id'),
Index('otu_sample_otu_20k_index_species_id_idx', 'species_id'),
Index('otu_sample_otu_20k_index_amplicon_id_idx', 'amplicon_id'),
Index('otu_sample_otu_20k_index_traits_idx', 'traits', postgresql_using='gin'),
]
)