def create_view(metadata, molecule_design_pool_tbl, stock_sample_tbl, sample_tbl, container_tbl): """ stock_info_view factory. """ mdp = molecule_design_pool_tbl ss = stock_sample_tbl c = container_tbl s = sample_tbl stock = select([ (literal('mdp') + cast(mdp.c.molecule_design_set_id, String) + literal('c') + cast(coalesce(ss.c.concentration * 1e6, 0), String)).label('stock_info_id'), mdp.c.molecule_design_set_id, # We need to set the label explicitly here because # mdp.c.molecule_type_id is really mdp.c.molecule_type. mdp.c.molecule_type_id.label('molecule_type_id'), # pylint: disable=E1101 coalesce(ss.c.concentration, 0).label('concentration'), coalesce(func.count(c.c.container_id), 0).label('total_tubes'), coalesce(func.sum(s.c.volume), 0).label('total_volume'), coalesce(func.min(s.c.volume), 0).label('minimum_volume'), coalesce(func.max(s.c.volume), 0).label('maximum_volume') # pylint: enable=E1101 ], from_obj= mdp.outerjoin(ss, ss.c.molecule_design_set_id == mdp.c.molecule_design_set_id) \ .outerjoin(s, s.c.sample_id == ss.c.sample_id) \ .outerjoin(c, and_(c.c.container_id == s.c.container_id, c.c.item_status == _STOCK_CONTAINER_ITEM_STATUS)) ).group_by(mdp.c.molecule_design_set_id, ss.c.concentration).alias('ssi') fkey_mds = ForeignKey(mdp.c.molecule_design_set_id) fkey_mds.parent = stock.c.molecule_design_set_id stock.c.molecule_design_set_id.foreign_keys.add(fkey_mds) fkey_mt = ForeignKey(mdp.c.molecule_type_id) fkey_mt.parent = stock.c.molecule_type_id stock.c.molecule_type_id.foreign_keys.add(fkey_mt) return view_factory(VIEW_NAME, metadata, stock)
def create_view(metadata, molecule_design_pool_tbl, stock_sample_tbl, sample_tbl, container_tbl): """ stock_info_view factory. """ mdp = molecule_design_pool_tbl ss = stock_sample_tbl c = container_tbl s = sample_tbl stock = ( select( [ ( literal("mdp") + cast(mdp.c.molecule_design_set_id, String) + literal("c") + cast(coalesce(ss.c.concentration * 1e6, 0), String) ).label("stock_info_id"), mdp.c.molecule_design_set_id, # We need to set the label explicitly here because # mdp.c.molecule_type_id is really mdp.c.molecule_type. mdp.c.molecule_type_id.label("molecule_type_id"), # pylint: disable=E1101 coalesce(ss.c.concentration, 0).label("concentration"), coalesce(func.count(c.c.container_id), 0).label("total_tubes"), coalesce(func.sum(s.c.volume), 0).label("total_volume"), coalesce(func.min(s.c.volume), 0).label("minimum_volume"), coalesce(func.max(s.c.volume), 0).label("maximum_volume") # pylint: enable=E1101 ], from_obj=mdp.outerjoin(ss, ss.c.molecule_design_set_id == mdp.c.molecule_design_set_id) .outerjoin(s, s.c.sample_id == ss.c.sample_id) .outerjoin(c, and_(c.c.container_id == s.c.container_id, c.c.item_status == _STOCK_CONTAINER_ITEM_STATUS)), ) .group_by(mdp.c.molecule_design_set_id, ss.c.concentration) .alias("ssi") ) fkey_mds = ForeignKey(mdp.c.molecule_design_set_id) fkey_mds.parent = stock.c.molecule_design_set_id stock.c.molecule_design_set_id.foreign_keys.add(fkey_mds) fkey_mt = ForeignKey(mdp.c.molecule_type_id) fkey_mt.parent = stock.c.molecule_type_id stock.c.molecule_type_id.foreign_keys.add(fkey_mt) return view_factory(VIEW_NAME, metadata, stock)
def create_view(metadata, molecule_design_tbl, molecule_design_structure_tbl, chemical_structure_tbl): """ molecule_type_modification_view factory. """ md = molecule_design_tbl mds = molecule_design_structure_tbl chs = chemical_structure_tbl modification = \ select([md.c.molecule_type_id.label('molecule_type_id'), chs.c.representation.label('name'), chs.c.chemical_structure_id], from_obj=md \ .join(mds, mds.c.molecule_design_id == md.c.molecule_design_id) \ .join(chs, and_(chs.c.chemical_structure_id == mds.c.chemical_structure_id, chs.c.structure_type_id == CHEMICAL_STRUCTURE_TYPE_IDS.MODIFICATION)) ) return view_factory(VIEW_NAME, metadata, modification)
def create_view(metadata, molecule_tbl, single_supplier_molecule_design_tbl, supplier_molecule_design_tbl): """ molecule_type_modification_view factory. """ m = molecule_tbl ssmd = single_supplier_molecule_design_tbl smd = supplier_molecule_design_tbl msmd = \ select([m.c.molecule_id, smd.c.supplier_molecule_design_id], from_obj=m \ .join(ssmd, ssmd.c.molecule_design_id == m.c.molecule_design_id) \ .join(smd, and_(smd.c.supplier_molecule_design_id == ssmd.c.supplier_molecule_design_id, smd.c.supplier_id == m.c.supplier_id, smd.c.is_current)) ) fkey_m = ForeignKey(m.c.molecule_id) fkey_m.parent = msmd.c.molecule_id msmd.c.molecule_id.foreign_keys.add(fkey_m) return view_factory(VIEW_NAME, metadata, msmd)