async def get_sample_subs(self, db_connection, user_id, limit, offset): """ SELECT pr.name, pr.url, pr.reference, pr.parameters, sh.name as shop_label, json_agg(prs.parameters) as selected_options FROM sub_user as sub INNER JOIN sub_user_stock_ix as subi ON subi.sub_id = sub.id INNER JOIN product_stock as prs ON prs.id = subi.stock_id INNER JOIN product as pr ON pr.id = prs.product_id INNER JOIN shop as sh ON sh.id = pr.shop_id WHERE user_id = 1 GROUP BY sub.id, pr.id, sh.id ORDER BY sub.dt_created DESC """ query = sa.select((sub_user.c.id, product.c.name, product.c.url, product.c.reference, product.c.parameters, shop.c.label.label('shop_label'), func.json_agg(product_stock.c.parameters).label('selected_options')) )\ .select_from(sub_user .join(sub_user_stock_ix, sub_user_stock_ix.c.sub_id == sub_user.c.id) .join(product_stock, product_stock.c.id == sub_user_stock_ix.c.stock_id) .join(product, product.c.id == product_stock.c.product_id) .join(shop, shop.c.id == product.c.shop_id) )\ .where(sub_user.c.user_id == user_id)\ .group_by(sub_user.c.id, product.c.id, shop.c.id)\ .order_by(sub_user.c.dt_created.desc())\ .limit(limit).offset(offset) res = await db_connection.execute(query) records = await res.fetchall() return [Subscription.from_row(row) for row in records]
def get_suppliers_with_expiring_documents(self, days): today = datetime.now(pytz.timezone('Australia/Sydney')) # Find out which of the supplier's documents have expired or are expiring soon liability = (select([Supplier.code, Supplier.name, literal('liability').label('type'), Supplier.data['documents']['liability']['expiry'].astext.label('expiry')]) .where(and_(Supplier.data['documents']['liability']['expiry'].isnot(None), func.to_date(Supplier.data['documents']['liability']['expiry'].astext, 'YYYY-MM-DD') == (today.date() + timedelta(days=days))))) workers = (select([Supplier.code, Supplier.name, literal('workers').label('type'), Supplier.data['documents']['workers']['expiry'].astext.label('expiry')]) .where(and_(Supplier.data['documents']['workers']['expiry'].isnot(None), func.to_date(Supplier.data['documents']['workers']['expiry'].astext, 'YYYY-MM-DD') == (today.date() + timedelta(days=days))))) expiry_dates = union(liability, workers).alias('expiry_dates') # Aggregate the document details so they can be returned with the results documents = (db.session.query(expiry_dates.columns.code, expiry_dates.columns.name, func.json_agg( func.json_build_object( 'type', expiry_dates.columns.type, 'expiry', expiry_dates.columns.expiry)).label('documents')) .group_by(expiry_dates.columns.code, expiry_dates.columns.name) .subquery('expired_documents')) # Find email addresses associated with the supplier email_addresses = self.get_supplier_contacts_union() # Aggregate the email addresses so they can be returned with the results aggregated_emails = (db.session.query(email_addresses.columns.code, func.json_agg( email_addresses.columns.email_address ).label('email_addresses')) .group_by(email_addresses.columns.code) .subquery()) # Combine the list of email addresses and documents results = (db.session.query(documents.columns.code, documents.columns.name, documents.columns.documents, aggregated_emails.columns.email_addresses) .join(aggregated_emails, documents.columns.code == aggregated_emails.columns.code) .order_by(documents.columns.code) .all()) return [r._asdict() for r in results]
def _add_relations(query, catalog_name, collection_name): gob_model = GOBModel() collection = gob_model.get_collection(catalog_name, collection_name) has_states = collection.get('has_states', False) src_table, _ = get_table_and_model(catalog_name, collection_name) for reference in collection['references']: relation_name = get_relation_name(gob_model, catalog_name, collection_name, reference) if not relation_name: continue rel_table, _ = get_table_and_model('rel', relation_name) select_attrs = [ getattr(rel_table, 'src_id'), getattr(rel_table, 'src_volgnummer'), ] if has_states else [ getattr(rel_table, 'src_id'), ] subselect = session \ .query( *select_attrs, func.json_agg( func.json_build_object( FIELD.SOURCE_VALUE, getattr(rel_table, FIELD.SOURCE_VALUE), FIELD.REFERENCE_ID, getattr(rel_table, 'dst_id') ) ).label('source_values') ).filter( and_( getattr(rel_table, FIELD.DATE_DELETED).is_(None), or_( getattr(rel_table, FIELD.EXPIRATION_DATE).is_(None), getattr(rel_table, FIELD.EXPIRATION_DATE) > func.now() ) ) ).group_by( *select_attrs ).subquery() join_clause = [ getattr(src_table, FIELD.ID) == getattr(subselect.c, 'src_id'), getattr(src_table, FIELD.SEQNR) == getattr(subselect.c, 'src_volgnummer') ] if has_states else [ getattr(src_table, FIELD.ID) == getattr(subselect.c, 'src_id'), ] query = query.join(subselect, and_(*join_clause), isouter=True) \ .add_columns( getattr(subselect.c, 'source_values').label(f"ref:{reference}") ) return query
def _get_permissions_query(self, session, identifier): """ select domain, json_agg(parts) as permissions from (select domain, row_to_json(r) as parts from (select domain, action, array_agg(distinct target) as target from (select (case when domain is null then '*' else domain end) as domain, (case when target is null then '*' else target end) as target, array_agg(distinct (case when action is null then '*' else action end)) as action from permission group by domain, target ) x group by domain, action) r) parts group by domain; """ thedomain = case([(Domain.name == None, "*")], else_=Domain.name) theaction = case([(Action.name == None, "*")], else_=Action.name) theresource = case([(Resource.name == None, "*")], else_=Resource.name) action_agg = func.array_agg(theaction.distinct()) stmt1 = ( session.query( Permission.domain_id, thedomain.label("domain"), Permission.resource_id, theresource.label("resource"), action_agg.label("action"), ) .select_from(User) .join(role_membership_table, User.pk_id == role_membership_table.c.user_id) .join(role_permission_table, role_membership_table.c.role_id == role_permission_table.c.role_id) .join(Permission, role_permission_table.c.permission_id == Permission.pk_id) .outerjoin(Domain, Permission.domain_id == Domain.pk_id) .outerjoin(Action, Permission.action_id == Action.pk_id) .outerjoin(Resource, Permission.resource_id == Resource.pk_id) .filter(User.identifier == identifier) .group_by(Permission.domain_id, Domain.name, Permission.resource_id, Resource.name) ).subquery() stmt2 = ( session.query(stmt1.c.domain, stmt1.c.action, func.array_agg(stmt1.c.resource.distinct()).label("resource")) .select_from(stmt1) .group_by(stmt1.c.domain, stmt1.c.action) ).subquery() stmt3 = ( session.query(stmt2.c.domain, func.row_to_json(as_row(stmt2)).label("parts")).select_from(stmt2) ).subquery() final = ( session.query(stmt3.c.domain, cast(func.json_agg(stmt3.c.parts), Text)) .select_from(stmt3) .group_by(stmt3.c.domain) ) return final
async def get_units(conn): j = join(unit_table, room_table, unit_table.c.id == room_table.c.unit_id) rooms_agg = func.json_agg(text(room_table.name)).label('rooms') query = (select(( unit_table, rooms_agg, )).select_from(j).group_by(unit_table.c.id).order_by( unit_table.c.created_at)) res = await conn.execute(query) return list(map(dict, await res.fetchall()))
def daily_statistics_agg(session): """ Aggregate statistics for each day in each year. Result columns: - day_of_year, year - bid - post_count, edit_count, posts_length, threads_created, active_users - active_threads: list of dicts of the most active threads (w.r.t. post count) of the day. Each dict consists of json_thread_columns (tid, [sub]title) plus "thread_post_count". """ year = func.extract('year', Post.timestamp).label('year') cte = aggregate_stats_segregated_by_time(session, func.extract('doy', Post.timestamp), 'day_of_year').subquery() json_thread_columns = (Thread.tid, Thread.title, Thread.subtitle) threads_active_during_time = ( session .query(*json_thread_columns, func.count(Post.pid).label('thread_post_count'), func.extract('doy', Post.timestamp).label('doy'), year, Thread.bid, func.row_number().over( partition_by=tuple_(year, Thread.bid, func.extract('doy', Post.timestamp)), order_by=tuple_(desc(func.count(Post.pid)), Thread.tid) ).label('rank')) .join(Post.thread) .group_by(*json_thread_columns, 'doy', Thread.bid, year) ).subquery('tadt') active_threads = ( session .query(threads_active_during_time.c.doy, threads_active_during_time.c.year, threads_active_during_time.c.bid, func.json_agg(column('tadt')).label('active_threads')) .select_from(threads_active_during_time) .filter(threads_active_during_time.c.rank <= 5) .group_by('doy', 'bid', 'year') .subquery() ) return ( session .query( *cte.c, active_threads.c.active_threads) .join(active_threads, and_(active_threads.c.doy == cte.c.day_of_year, active_threads.c.year == cte.c.year, active_threads.c.bid == cte.c.bid)) )
async def get_unit_with_rooms(conn, unit_id): j = join( unit_table, room_table, and_(unit_table.c.id == room_table.c.unit_id, unit_table.c.id == unit_id)) rooms_agg = func.json_agg(text(room_table.name)).label('rooms') query = (select(( unit_table, rooms_agg, )).select_from(j).group_by(unit_table.c.id).order_by( unit_table.c.created_at)) res = await conn.execute(query) return dict((await res.first()))
def search_from_user_query(self, query_string): ar = self.metadata.tables["article"] re = self.metadata.tables["reference"] wr = self.metadata.tables["writes"] au = self.metadata.tables["author"] da = self.metadata.tables["dataset"] # Every dataset referenced in a paper for a given author intermediate_join = (re.join( ar, re.c.article_id == ar.c.article_id).join( wr, wr.c.article_id == re.c.article_id).join( au, au.c.author_id == wr.c.author_id).join( da, da.c.dataset_id == re.c.dataset_id)) text_search_match_records = (select( [ar.c.article_id, au.c.author_id, da.c.dataset_id]).select_from(intermediate_join).where( func.concat(au.c.author_name, ar.c.article_title, da.c.dataset_name).ilike( f"""%{ query_string.replace(" ", "%") }%""")) ).cte("text_search_match_records") # Join in search result data from IDs in the CTE resultset_join = (text_search_match_records.join( ar, ar.c.article_id == text_search_match_records.c.article_id).join( wr, wr.c.article_id == ar.c.article_id).join( au, au.c.author_id == wr.c.author_id).join( re, re.c.article_id == ar.c.article_id).join( da, da.c.dataset_id == re.c.dataset_id)) return self.conn.execute( select([ ar.c.article_title, func.json_agg(au.c.author_name), func.json_agg(da.c.dataset_name), ]).group_by( ar.c.article_title).select_from(resultset_join)).fetchall()
def get_agencies(self): subquery = (db.session.query( AgencyDomain.agency_id, func.json_agg( func.json_build_object( 'id', AgencyDomain.id, 'domain', AgencyDomain.domain, 'active', AgencyDomain.active)).label('domains')).group_by( AgencyDomain.agency_id).subquery()) result = (db.session.query( Agency.id, Agency.name, Agency.domain, Agency.category, Agency.state, Agency.body_type, Agency.whitelisted, Agency.reports, subquery.c.domains).join( subquery, subquery.c.agency_id == Agency.id).order_by(Agency.name).all()) return [r._asdict() for r in result]
async def get_notice_product_iter(self): """ SELECT sub.user_id, p.id as product_id, p.name as product_name, p.url as product_url, s.label as shop_label, p.parameters as product_parameters, json_agg(json_build_object('data', ns.data, 'parameters', ps.parameters, 'discount', ps.discount)) FROM sub_user_stock_ix as subi INNER JOIN notice_stock as ns ON ns.stock_id = subi.stock_id INNER JOIN sub_user as sub ON sub.id = subi.sub_id INNER JOIN product_stock as ps ON ps.id = subi.stock_id INNER JOIN product as p ON p.id = ps.product_id INNER JOIN shop as s ON s.id = p.shop_id GROUP BY sub.user_id, p.id, s.id ORDER BY sub.user_id, p.id """ async with db.engine.acquire() as db_connection: select_exists = sa.select((sa.exists(sa.select((notice_stock.c.stock_id, ))),)) res = await db_connection.execute(select_exists) exists = await res.scalar() if not exists: return query = sa.select(( sub_user.c.user_id, product.c.id.label('product_id'), product.c.name.label('product_name'), product.c.reference.label('product_reference'), product.c.url.label('product_url'), product.c.parameters.label('product_parameters'), func.json_agg(func.json_build_object( 'data', notice_stock.c.data, 'parameters', product_stock.c.parameters, 'discount', product_stock.c.discount, )).label('notice_data'), shop.c.label.label('shop_label') ))\ .select_from( sub_user_stock_ix .join(notice_stock, notice_stock.c.stock_id == sub_user_stock_ix.c.stock_id) .join(sub_user, sub_user.c.id == sub_user_stock_ix.c.sub_id) .join(product_stock, product_stock.c.id == sub_user_stock_ix.c.stock_id) .join(product, product.c.id == product_stock.c.product_id) .join(shop, shop.c.id == product.c.shop_id) )\ .group_by(sub_user.c.user_id, product.c.id, shop.c.id)\ .order_by(sub_user.c.user_id, product.c.id) async for row in db_connection.execute(query): yield row
def get_agency(self, agency_id): subquery = (db.session.query( AgencyDomain.agency_id, func.json_agg( func.json_build_object( 'id', AgencyDomain.id, 'domain', AgencyDomain.domain, 'active', AgencyDomain.active)).label('domains')).group_by( AgencyDomain.agency_id).subquery()) result = (db.session.query( Agency.id, Agency.name, Agency.domain, Agency.category, Agency.state, Agency.body_type, Agency.whitelisted, Agency.reports, Agency.must_join_team, subquery.c.domains).join(subquery, subquery.c.agency_id == Agency.id).filter( Agency.id == agency_id).one_or_none()) return result._asdict()
def get_suppliers_with_unassessed_domains_and_all_case_studies_rejected(self): case_study_query = ( db.session.query( CaseStudy.supplier_code.label('supplier_code'), CaseStudy.data['service'].astext.label('domain'), func.count(CaseStudy.id).label('count') ) .group_by(CaseStudy.supplier_code, CaseStudy.data['service'].astext) ) subquery = ( case_study_query .intersect( case_study_query .filter(CaseStudy.status == 'rejected') ) .subquery() ) results = ( db .session .query( Supplier.id, Supplier.code, Supplier.name, func.json_agg(aggregate_order_by(Domain.name, Domain.name)).label('domains') ) .join(SupplierDomain, Domain) .join(subquery, and_( Supplier.code == subquery.columns.supplier_code, Domain.name == subquery.columns.domain )) .filter( Supplier.status != 'deleted', Supplier.data['recruiter'].astext.in_(['no', 'both']), SupplierDomain.status == 'unassessed' ) .group_by(Supplier.id, Supplier.code, Supplier.name) .all() ) return [r._asdict() for r in results]
def venues(): # TODO: replace with real venues data. # num_shows should be aggregated based on number of upcoming shows per venue. # data=[{ # "city": "San Francisco", # "state": "CA", # "venues": [{ # "id": 1, # "name": "The Musical Hop", # "num_upcoming_shows": 0, # }, { # "id": 3, # "name": "Park Square Live Music & Coffee", # "num_upcoming_shows": 1, # }] # }, { # "city": "New York", # "state": "NY", # "venues": [{ # "id": 2, # "name": "The Dueling Pianos Bar", # "num_upcoming_shows": 0, # }] # }] query = db.session.query(func.json_build_object( 'city', Venue.city, 'state', Venue.state, 'venues', func.json_agg(func.json_build_object( 'id', Venue.id, 'name', Venue.name, 'num_upcoming_shows', len(get_upcoming_or_past_shows(Venue, 'venue_id')['upcoming_shows']) ) ))).group_by(Venue.city, Venue.state).all() # Remove inner tuples data = [q[0] for q in query] return render_template('pages/venues.html', areas=data)
def get_approved_case_studies_by_supplier_code(self, supplier_code, domain_id): subquery = (db.session.query( CaseStudy.id.label('cs_id'), CaseStudy.data.label('case_study_data'), Domain.name.label('category_name')).join( Domain, Domain.name == CaseStudy.data['service'].astext).filter( CaseStudy.supplier_code == supplier_code, CaseStudy.status == 'approved', Domain.id == domain_id).subquery()) result = (db.session.query( subquery.c.category_name, func.json_agg( func.json_build_object( 'id', subquery.c.cs_id, 'data', subquery.c.case_study_data)).label('cs_data')).group_by( subquery.c.category_name)) results = result.one_or_none() return results._asdict() if results else {}
def get_mapped_tasks_by_user(project_id: int): """ Gets all mapped tasks for supplied project grouped by user""" results = ( db.session.query( User.username, User.mapping_level, func.count(distinct(Task.id)), func.json_agg(distinct(Task.id)), func.max(TaskHistory.action_date), User.date_registered, User.last_validation_date, ) .filter(Task.project_id == TaskHistory.project_id) .filter(Task.id == TaskHistory.task_id) .filter(Task.mapped_by == User.id) .filter(Task.project_id == project_id) .filter(Task.task_status == 2) .filter(TaskHistory.action_text == "MAPPED") .group_by( User.username, User.mapping_level, User.date_registered, User.last_validation_date, ) ) mapped_tasks_dto = MappedTasks() for row in results: user_mapped = MappedTasksByUser() user_mapped.username = row[0] user_mapped.mapping_level = MappingLevel(row[1]).name user_mapped.mapped_task_count = row[2] user_mapped.tasks_mapped = row[3] user_mapped.last_seen = row[4] user_mapped.date_registered = row[5] user_mapped.last_validation_date = row[6] mapped_tasks_dto.mapped_tasks.append(user_mapped) return mapped_tasks_dto
def get_approved_evidence(self, evidence_id): category_name_max_daily_rate = (db.session.query( Domain.name.label('category'), Evidence.data['maxDailyRate'].label('maxDailyRate')).join( Evidence, Evidence.domain_id == Domain.id).filter( Evidence.id == evidence_id).subquery()) evidence_domain_criteria = (db.session.query( Evidence.id.label('evidence_id'), func.json_array_elements_text( Evidence.data['criteria']).label('domain_criteria_id')).filter( Evidence.id == evidence_id).subquery()) subquery = (db.session.query( evidence_domain_criteria.c.domain_criteria_id, DomainCriteria.name.label('dc_name'), Evidence.data['evidence'][ evidence_domain_criteria.c.domain_criteria_id].label( 'evidence_data')).join( DomainCriteria, DomainCriteria.id == evidence_domain_criteria.c. domain_criteria_id.cast(Integer)).filter( Evidence.id == evidence_id).subquery()) evidence_data = (db.session.query( category_name_max_daily_rate.c.category, func.json_agg( func.json_build_object( 'dc_id', subquery.c.domain_criteria_id, 'domain_criteria_name', subquery.c.dc_name, 'evidence_data', subquery.c.evidence_data)).label('evidence')).group_by( category_name_max_daily_rate.c.category).subquery()) result = (db.session.query(category_name_max_daily_rate.c.category, category_name_max_daily_rate.c.maxDailyRate, evidence_data.c.evidence)) results = result.one_or_none() return results._asdict() if results else {}
async def get_sub_by_id(self, db_connection, sub_id, user_id): query = sa.select((sub_user.c.id, sub_user.c.user_id, product.c.name, product.c.url, product.c.reference, product.c.parameters, shop.c.label.label('shop_label'), func.json_agg(product_stock.c.parameters).label('selected_options')) )\ .select_from(sub_user .join(sub_user_stock_ix, sub_user_stock_ix.c.sub_id == sub_user.c.id) .join(product_stock, product_stock.c.id == sub_user_stock_ix.c.stock_id) .join(product, product.c.id == product_stock.c.product_id) .join(shop, shop.c.id == product.c.shop_id) )\ .where(sub_user.c.id == sub_id)\ .group_by(sub_user.c.id, product.c.id, shop.c.id) res = await db_connection.execute(query) row = await res.first() if not row: raise ObjectDoesNotExist if row.user_id != user_id: raise PermissionDenied return Subscription.from_row(row)
def get_suppliers(self): subquery = ( db .session .query( SupplierDomain.supplier_id, func.json_agg( func.json_build_object( 'category', Domain.name, 'status', SupplierDomain.status, 'price_status', SupplierDomain.price_status ) ).label('categories') ) .join(Domain) .group_by(SupplierDomain.supplier_id) .subquery() ) result = ( db .session .query( Supplier.code, Supplier.name, Supplier.abn, Supplier.status, Supplier.creation_time, Supplier.data['seller_type']['sme'].astext.label('sme'), subquery.columns.categories ) .join(subquery, Supplier.id == subquery.columns.supplier_id) .order_by(Supplier.code) .all() ) return [r._asdict() for r in result]
def get_observations_by_user_id(user_id): try: observations = (db.session.query( ObservationModel, ProgramsModel, UserModel.username, func.json_agg( func.json_build_array(MediaModel.filename, MediaModel.id_media)).label("images"), LAreas.area_name, LAreas.area_code, ).filter(ObservationModel.id_role == user_id).join( LAreas, LAreas.id_area == ObservationModel.municipality, isouter=True).join( ProgramsModel, ProgramsModel.id_program == ObservationModel.id_program, isouter=True, full=True, ).join( ObservationMediaModel, ObservationMediaModel.id_data_source == ObservationModel.id_observation, isouter=True, ).join( MediaModel, ObservationMediaModel.id_media == MediaModel.id_media, isouter=True, ).join(UserModel, ObservationModel.id_role == UserModel.id_user, full=True).group_by( ObservationModel.id_observation, ProgramsModel.id_program, UserModel.username, LAreas.area_name, LAreas.area_code, )) observations = observations.order_by( desc(ObservationModel.timestamp_create)) # current_app.logger.debug(str(observations)) observations = observations.all() try: if current_app.config.get("API_TAXHUB") is not None: taxon_repository = [] taxhub_list_id = [] for observation in observations: if observation.ProgramsModel.taxonomy_list not in taxhub_list_id: taxhub_list_id.append( observation.ProgramsModel.taxonomy_list) for tax_list in taxhub_list_id: taxon_repository.append(mkTaxonRepository(tax_list)) features = [] except Exception as e: return {"message": str(e)}, 500 for observation in observations: feature = get_geojson_feature(observation.ObservationModel.geom) feature["properties"]["municipality"] = { "name": observation.area_name, "code": observation.area_code, } # Observer feature["properties"]["observer"] = { "username": observation.username } # Observer submitted media feature["properties"]["image"] = ("/".join([ "/api", current_app.config["MEDIA_FOLDER"], observation.images[0][0], ]) if observation.images and observation.images != [[None, None]] else None) # Photos feature["properties"]["photos"] = [{ "url": "/media/{}".format(filename), "id_media": id_media } for filename, id_media in observation.images if id_media is not None] # Municipality observation_dict = observation.ObservationModel.as_dict(True) for k in observation_dict: if k in obs_keys and k != "municipality": feature["properties"][k] = observation_dict[k] # Program program_dict = observation.ProgramsModel.as_dict(True) for program in program_dict: if program == "title": feature["properties"]["program_title"] = program_dict[ program] # TaxRef if current_app.config.get("API_TAXHUB") is None: taxref = Taxref.query.filter(Taxref.cd_nom == observation. ObservationModel.cd_nom).first() if taxref: feature["properties"]["taxref"] = taxref.as_dict(True) medias = TMedias.query.filter(TMedias.cd_ref == observation. ObservationModel.cd_nom).all() if medias: feature["properties"]["medias"] = [ media.as_dict(True) for media in medias ] else: try: for taxon_rep in taxon_repository: for taxon in taxon_rep: if (taxon["taxref"]["cd_nom"] == observation.ObservationModel.cd_nom): feature["properties"]["nom_francais"] = taxon[ "nom_francais"] feature["properties"]["taxref"] = taxon[ "taxref"] feature["properties"]["medias"] = taxon[ "medias"] except StopIteration: pass features.append(feature) return FeatureCollection(features), 200 except Exception as e: raise e current_app.logger.critical("[get_program_observations] Error: %s", str(e)) return {"message": str(e)}, 400
def json_agg(table): return func.json_agg(literal_column('"' + table.name + '"'))
def listing(self, text_query=None, type=None, start_date=None, end_date=None, contributor_person_ids=None, contributor_group_ids=None, affiliation_group_ids=None, related_work_ids=None, offset=0, limit=100, order_by=None, principals=None): selected_work_ids = None if contributor_person_ids: query = self.session.query(Contributor.work_id.label('id')) query = query.filter(sql.or_(*[Contributor.person_id == pid for pid in contributor_person_ids])) query = query.group_by(Contributor.work_id) selected_work_ids = query.cte('selected_work_ids') elif contributor_group_ids: query = self.session.query(Contributor.work_id.label('id')) query = query.filter(sql.or_(*[Contributor.group_id == gid for gid in contributor_group_ids])) query = query.group_by(Contributor.work_id) selected_work_ids = query.cte('selected_work_ids') elif affiliation_group_ids: query = self.session.query(Affiliation.work_id.label('id')) query = query.filter(sql.or_(*[Affiliation.group_id == gid for gid in affiliation_group_ids])) query = query.group_by(Affiliation.work_id) selected_work_ids = query.cte('selected_work_ids') elif related_work_ids: query = self.session.query(Relation.work_id.label('id')) query = query.filter(sql.or_(*[Relation.target_id == wid for wid in related_work_ids])) query = query.group_by(Relation.work_id) selected_work_ids = query.cte('selected_work_ids') work_query = self.session.query(Work.id) if selected_work_ids is not None: work_query = work_query.join( selected_work_ids, selected_work_ids.c.id == Work.id) acl_filters = self.acl_filters(principals) if acl_filters: group_filters = [f for f in acl_filters if f.left.table.name == 'affiliations'] person_filters = [f for f in acl_filters if f.left.table.name == 'contributors'] if group_filters: query = self.session.query(Affiliation.work_id.label('id')) query = query.filter(sql.or_(*group_filters)) query = query.group_by(Affiliation.work_id) allowed_work_ids = query.cte('allowed_work_ids') allowed_group_query = query if person_filters: query = self.session.query(Contributor.work_id.label('id')) query = query.filter(sql.or_(*person_filters)) query = query.group_by(Contributor.work_id) allowed_work_ids = query.cte('allowed_work_ids') allowed_person_query = query if group_filters and person_filters: query = allowed_group_query.union( allowed_person_query).group_by('id') allowed_work_ids = query.cte('allowed_work_ids') work_query = work_query.join( allowed_work_ids, allowed_work_ids.c.id == Work.id) if start_date or end_date: duration = DateInterval([start_date, end_date]) work_query = work_query.filter(Work.during.op('&&')(duration)) if text_query: work_query = work_query.filter( Work.title.ilike('%%%s%%' % text_query)) if type: work_query = work_query.filter(Work.type == type) total = work_query.count() work_query = work_query.order_by(order_by or Work.issued.desc()) work_query = work_query.limit(limit).offset(offset) filtered_work_ids = work_query.cte('filtered_work_ids') listed_works = self.session.query( Work.id.label('id'), Work.type.label('type'), Work.issued.label('issued'), Work.title).join( filtered_work_ids, filtered_work_ids.c.id == Work.id).cte('listed_works') Target = aliased(Work) full_listing = self.session.query( listed_works, func.json_agg( func.json_build_object('id', Contributor.id, 'position', Contributor.position, 'name', Person.name, 'person_id', Person.id, 'initials', Person.initials, 'prefix', Person.family_name_prefix, 'given_name', Person.given_name, 'family_name', Person.family_name, 'description', Contributor.description, 'group_id', Contributor.group_id, 'role', Contributor.role) ).label('contributors'), func.json_agg( func.json_build_object('id', Relation.id, 'relation_type', Relation.type, 'type', Target.type, 'location', Relation.location, 'starting', Relation.starting, 'ending', Relation.ending, 'volume', Relation.volume, 'issue', Relation.issue, 'number', Relation.number, 'title', Target.title) ).label('relations'), func.array_agg( sql.distinct( func.concat(Group.id, ':', Group.name))).label('affiliations') ) full_listing = full_listing.outerjoin( Contributor, listed_works.c.id == Contributor.work_id).outerjoin( Person, Person.id == Contributor.person_id) full_listing = full_listing.outerjoin( Affiliation, Contributor.id == Affiliation.contributor_id).outerjoin( Group, Group.id == Affiliation.group_id) full_listing = full_listing.outerjoin( Relation, listed_works.c.id == Relation.work_id).outerjoin( Target, Target.id == Relation.target_id) full_listing = full_listing.group_by(listed_works).order_by( listed_works.c.issued.desc()) hits = [] contributor_role_ids = set(contributor_person_ids or []) for hit in full_listing.all(): aff_labels = dict([tuple(a.split(':', 1)) for a in hit.affiliations]) contributors = [] roles = set() # filter out contributor rows with a null id. # this happens with course relations contributor_rows = [c for c in hit.contributors if c.get('id')] contributor_rows.sort(key=itemgetter('position')) for contributor in contributor_rows: if contributor['person_id'] in contributor_role_ids: roles.add(contributor['role']) if (contributors and contributors[-1]['position'] == contributor['position']): # same contributor as previous one, (but other affiliation # it's hard to remove this with a distinct clause # in the json agg, so we remove it here continue cg_id = contributor['group_id'] if cg_id and str(cg_id) in aff_labels: # a group contributor is always added as an affiliation contributor['group_name'] = aff_labels[str(cg_id)] contributors.append(contributor) affiliations = [] for affiliation in hit.affiliations: id, name = affiliation.split(':', 1) affiliations.append(dict(id=id, name=name)) hits.append({'id': hit.id, 'title': hit.title, 'type': hit.type, 'roles': list(roles), 'issued': hit.issued.strftime('%Y-%m-%d'), 'relations': hit.relations, 'affiliations': affiliations, 'contributors': contributors}) return {'total': total, 'hits': hits, 'limit': limit, 'offset': offset}