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'), ] )