def rbacsForRightSQL(self, rightId, sql=None): ''' @see: IRbacService.rbacsForRightSQL ''' child, parent = aliased(RoleNode), aliased(RoleNode) subq = sql or self.session().query(RbacMapped) subq = subq.join(RbacRole, RbacRole.rbac == RbacMapped.Id) subq = subq.join(parent, parent.role == RbacRole.role) subq = subq.join( child, and_(child.left >= parent.left, child.right <= parent.right)) subq = subq.join(RbacRight, RbacRight.rbac == child.role) subq = subq.join( RightMapped, and_(RightMapped.Id == RbacRight.right, RightMapped.Id == rightId)) sql = sql or self.session().query(RbacMapped) sql = sql.join(RbacRight, RbacRight.rbac == RbacMapped.Id) sql = sql.join( RightMapped, and_(RightMapped.Id == RbacRight.right, RightMapped.Id == rightId)) sql = sql.union(subq).distinct(RbacMapped.Id).order_by(RbacMapped.Id) return sql
def get_data(self): logging.debug("These queries will take a few mins to run.") budget_query = self.session.query( self.models.MovieInfo.movie_id, func.max( cast( func.replace( func.replace(self.models.MovieInfo.info, ",", ""), "$", ""), types.Numeric)).label('budget')).filter( self.models.MovieInfo.movie_id.in_(self.movie_ids), self.models.MovieInfo.info_type_id.in_( [BUDGET_TYPE_ID]), self.models.MovieInfo.info.like('$%'), ).group_by(self.models.MovieInfo.movie_id).subquery() year_query = self.session.query( self.models.MovieInfo.movie_id, func.min(func.substr( self.models.MovieInfo.info, -4)).label('release_year')).filter( self.models.MovieInfo.movie_id.in_(self.movie_ids), self.models.MovieInfo.info_type_id.in_([RELEASE_DATES_ID]), self.models.MovieInfo.info.like('USA:%')).group_by( self.models.MovieInfo.movie_id).subquery() budget_alias = aliased(self.models.MovieInfo, budget_query) year_alias = aliased(self.models.MovieInfo, year_query) return self.session.query( budget_query.columns.movie_id, budget_query.columns.budget, year_query.columns.release_year).join( year_alias, year_alias.movie_id == budget_alias.movie_id).distinct( self.models.MovieInfo.movie_id).filter( self.models.MovieInfo.movie_id.in_(self.movie_ids), )
def search_user_checkpoints(user_obj, search_term): """ Searches both my UserCheckpoints and my friends' with a literal search term. Searches a Checkpoint's name, description, and User's (Facebook) name (union) """ from db import UserCheckpoint, db, FacebookUser, User, FriendConnection, Checkpoint search_term_with_wildcard = "%"+search_term+"%" #query that contain my own and my friends' usercheckpoints FriendUserCheckpoint, FriendFacebookUser, FriendUser = aliased(UserCheckpoint), aliased(FacebookUser), aliased(User) ucp = (db.session.query(UserCheckpoint). join(Checkpoint, Checkpoint.id == UserCheckpoint.checkpoint_id). join(FriendUser, FriendUser.id == UserCheckpoint.user_id). join(FriendFacebookUser, FriendFacebookUser.id == FriendUser.facebook_user_id). join(FriendConnection, FriendConnection.fb_user_to == FriendFacebookUser.id). join(FacebookUser, FacebookUser.id == FriendConnection.fb_user_from). join(User, User.facebook_user_id == FacebookUser.id). filter(or_(User.id == user_obj.id, FriendUser.id == user_obj.id)). filter(Checkpoint.demo == False) ) name_search = ucp.filter(Checkpoint.name.ilike(search_term_with_wildcard)) desc_search = ucp.filter(Checkpoint.description.ilike(search_term_with_wildcard)) fb_name_search = ucp.filter(FriendFacebookUser.name.ilike(search_term_with_wildcard)) result = name_search.union(desc_search).union(fb_name_search) return result.all()
def get_for_kontkurs_id(self, kontkurs_id, sem=1, subgroup_number=0): Kontgrp2 = aliased(Kontgrp) KontgrpCommon = aliased(Kontgrp) KontgrpParent = aliased(Kontgrp) if not isinstance(kontkurs_id, list): kontkurs_id = [kontkurs_id, ] if int(subgroup_number): filter_ = and_(or_( Raspnagr.kontkurs_id.in_(kontkurs_id), Kontlist.kontkurs_id.in_(kontkurs_id), KontgrpCommon.kont_id.in_(kontkurs_id), ), or_( KontgrpCommon.id == None, and_(KontgrpParent.id == None, KontgrpCommon.ngroup == subgroup_number), KontgrpParent.ngroup == subgroup_number ), ) else: filter_ = or_( Raspnagr.kontkurs_id.in_(kontkurs_id), KontgrpCommon.kont_id.in_(kontkurs_id), Kontlist.kontkurs_id.in_(kontkurs_id), ) raspnagrs = Raspnagr.query.filter( Raspnagr.id.in_(Raspnagr.query .outerjoin(Kontgrp, Raspnagr.kontgrp_id == Kontgrp.id) .outerjoin(Kontgrplist, Kontgrplist.op == Raspnagr.op) .outerjoin(Kontlist, Kontlist.op == Raspnagr.op) .outerjoin(Kontgrp2, Kontgrp2.id == Kontgrplist.kontgrp_id) .outerjoin(KontgrpCommon, func.coalesce(Kontgrp2.id, Kontgrp.id) == KontgrpCommon.id) .outerjoin(KontgrpParent, KontgrpCommon.parent_id == KontgrpParent.id) .filter(filter_).filter(Raspnagr.sem == sem).with_entities(Raspnagr.id))) return raspnagrs
def unwanted_list(request): who = aliased(User, name="who") by = aliased(User, name="by") a = DBSession.query(UnWanted, who, by). \ join(who, UnWanted.user).join(by, UnWanted.by_user). \ all() a = obj_list_join(a, ['', 'user_', 'by_']) return {'unwanted': a}
def my_members(self): """Returns a dictionary from ("User"|"Group", "name") tuples to records.""" parent = aliased(Group) group_member = aliased(Group) user_member = aliased(User) now = datetime.utcnow() users = (self.session.query( label("id", user_member.id), label("type", literal("User")), label("name", user_member.username), label("role", GroupEdge._role), label("edge_id", GroupEdge.id), label("expiration", GroupEdge.expiration), ).filter( parent.id == self.id, parent.id == GroupEdge.group_id, user_member.id == GroupEdge.member_pk, GroupEdge.active == True, parent.enabled == True, user_member.enabled == True, or_(GroupEdge.expiration > now, GroupEdge.expiration == None), GroupEdge.member_type == 0, ).group_by("type", "name").subquery()) groups = (self.session.query( label("id", group_member.id), label("type", literal("Group")), label("name", group_member.groupname), label("role", GroupEdge._role), label("edge_id", GroupEdge.id), label("expiration", GroupEdge.expiration), ).filter( parent.id == self.id, parent.id == GroupEdge.group_id, group_member.id == GroupEdge.member_pk, GroupEdge.active == True, parent.enabled == True, group_member.enabled == True, or_(GroupEdge.expiration > now, GroupEdge.expiration == None), GroupEdge.member_type == 1, ).subquery()) query = (self.session.query("id", "type", "name", "role", "edge_id", "expiration").select_entity_from( union_all(users.select(), groups.select())).order_by( desc("role"), desc("type"))) return OrderedDict( ((record.type, record.name), record) for record in query.all())
def runs_cst(session, time_since=None, run_ids=None, run_status=None): """ :param sqlalchemy.orm.Session session: :param datetime time_since: date cutoff for a run to be retrieved :param list run_ids: filter by specific run ids :param list run_status: filter by specific run statuses """ r = None s = None if run_ids: r = session.query(t.Process.processid) \ .join(t.Process.type) \ .join(t.Process.udfs) \ .filter(t.ProcessUdfView.udfname == 'RunID') \ .filter(t.ProcessUdfView.udfvalue.in_(run_ids)) \ .filter(t.ProcessType.displayname == 'AUTOMATED - Sequence').subquery('r') if run_status: s = session.query(t.Process.processid) \ .join(t.Process.type) \ .join(t.Process.udfs) \ .filter(t.ProcessUdfView.udfname == 'Run Status') \ .filter(t.ProcessUdfView.udfvalue.in_(run_status)) \ .filter(t.ProcessType.displayname == 'AUTOMATED - Sequence').subquery('s') parent_process_io_tracker = aliased(t.ProcessIOTracker) gparent_output_mapping = aliased(t.OutputMapping) gparent_process_io_tracker = aliased(t.ProcessIOTracker) gparent_process = aliased(t.Process) gparent_process_type = aliased(t.ProcessType) q = session.query(t.Process.processid, gparent_process.processid, gparent_process.daterun) \ .distinct(gparent_process.processid) \ .join(t.Process.type) \ .join(t.Process.processiotrackers) \ .join(t.ProcessIOTracker.artifact) \ .join(t.OutputMapping, t.OutputMapping.outputartifactid == t.Artifact.artifactid) \ .join(parent_process_io_tracker, t.OutputMapping.trackerid == parent_process_io_tracker.trackerid) \ .join(gparent_output_mapping, gparent_output_mapping.outputartifactid == parent_process_io_tracker.inputartifactid) \ .join(gparent_process_io_tracker, gparent_output_mapping.trackerid == gparent_process_io_tracker.trackerid) \ .join(gparent_process, gparent_process.processid == gparent_process_io_tracker.processid) \ .join(gparent_process_type, gparent_process.typeid == gparent_process_type.typeid) \ .filter(t.ProcessType.displayname == 'AUTOMATED - Sequence') if time_since: q = q.filter(func.date(t.Process.createddate) > func.date(time_since)) if r is not None: q = q.filter(t.Process.processid.in_(r)) if s is not None: q = q.filter(t.Process.processid.in_(s)) results = q.all() return results
def rolesForRbacSQL(self, rbacId, sql=None): """ @see: IRbacService.rolesForRbacSQL """ child, parent = aliased(RoleNode), aliased(RoleNode) sql = sql or self.session().query(RoleMapped) sql = sql.join(child, child.role == RoleMapped.Id) sql = sql.join(parent, and_(child.left >= parent.left, child.right <= parent.right)) sql = sql.join(RbacRole, and_(RbacRole.role == parent.role, RbacRole.rbac == rbacId)) return sql
def rolesForRbacSQL(self, rbacId, sql=None): ''' @see: IRbacService.rolesForRbacSQL ''' child, parent = aliased(RoleNode), aliased(RoleNode) sql = sql or self.session().query(RoleMapped) sql = sql.join(child, child.role == RoleMapped.Id) sql = sql.join(parent, and_(child.left >= parent.left, child.right <= parent.right)) sql = sql.join(RbacRole, and_(RbacRole.role == parent.role, RbacRole.rbac == rbacId)) sql = sql.order_by(child.left) return sql
def get_for_kontkurs_id(cls, kontkurs_id, sem, subgroup_numbers=None): if subgroup_numbers is None: subgroup_numbers = [] Kontgrp2 = aliased(Kontgrp) KontgrpCommon = aliased(Kontgrp) KontgrpParent = aliased(Kontgrp) KontgrpChild = aliased(Kontgrp) if len(subgroup_numbers) and subgroup_numbers[0] != '0': conditions_common = [] conditions_parent = [] for item in subgroup_numbers: c = item.split('-') if len(c) > 1: conditions_common.append(and_(KontgrpCommon.ngroup == c[0], KontgrpChild.ngroup == c[1])) conditions_parent.append(and_(KontgrpParent.ngroup == c[0], KontgrpCommon.ngroup == c[1])) else: conditions_common.append(KontgrpCommon.ngroup == c[0]) conditions_parent.append(KontgrpParent.ngroup == c[0]) filter_ = and_(or_( Raspnagr.kontkurs_id == kontkurs_id, Kontlist.kontkurs_id == kontkurs_id, KontgrpCommon.kont_id == kontkurs_id, ), or_( KontgrpCommon.id == None, and_(KontgrpParent.id == None, or_(*conditions_common)), or_(*conditions_parent) )) else: filter_ = func.coalesce(Kontlist.kontkurs_id, KontgrpCommon.kont_id, Raspnagr.kontkurs_id) == kontkurs_id zaoch = RaspisZaoch.query.with_entities(RaspisZaoch.id) \ .outerjoin(Raspnagr, Raspnagr.id == RaspisZaoch.raspnagr_id) \ .outerjoin(Kontlist, Kontlist.op == Raspnagr.op) \ .outerjoin(Kontgrplist, Kontgrplist.op == Raspnagr.op) \ .outerjoin(Kontgrp, Kontgrp.id == Kontgrplist.kontgrp_id) \ .outerjoin(Kontgrp2, Raspnagr.kontgrp_id == Kontgrp2.id) \ .outerjoin(KontgrpCommon, func.coalesce(Kontgrp.id, Kontgrp2.id) == KontgrpCommon.id) \ .outerjoin(KontgrpParent, KontgrpCommon.parent_id == KontgrpParent.id) \ .outerjoin(KontgrpChild, KontgrpChild.parent_id == KontgrpCommon.id) \ .filter(filter_) \ .filter(Raspnagr.sem == sem) result = RaspisZaoch.query.filter( RaspisZaoch.id.in_(zaoch) ) return result
def set_recent_outings(waypoint, lang): """Set last 10 outings on routes associated to the given waypoint. """ t_outing_route = aliased(Association, name='a1') t_route_wp = aliased(Association, name='a2') with_query_waypoints = _get_select_children(waypoint) recent_outing_ids = get_first_column( DBSession.query(Outing.document_id). filter(Outing.redirects_to.is_(None)). join( t_outing_route, Outing.document_id == t_outing_route.child_document_id). join( t_route_wp, and_( t_route_wp.child_document_id == t_outing_route.parent_document_id, t_route_wp.child_document_type == ROUTE_TYPE, )). join( with_query_waypoints, with_query_waypoints.c.document_id == t_route_wp.parent_document_id ). order_by(Outing.date_end.desc()). limit(NUM_RECENT_OUTINGS). all()) total = DBSession.query(Outing.document_id). \ filter(Outing.redirects_to.is_(None)). \ join( t_outing_route, Outing.document_id == t_outing_route.child_document_id). \ join( t_route_wp, and_( t_route_wp.child_document_id == t_outing_route.parent_document_id, t_route_wp.child_document_type == ROUTE_TYPE, )). \ join( with_query_waypoints, with_query_waypoints.c.document_id == t_route_wp.parent_document_id ). \ count() waypoint.associations['recent_outings'] = get_documents_for_ids( recent_outing_ids, lang, outing_documents_config, total)
def my_members(self): # type: () -> Mapping[Tuple[str, str], Any] """Returns a dictionary from ("User"|"Group", "name") tuples to records.""" parent = aliased(Group) group_member = aliased(Group) user_member = aliased(User) now = datetime.utcnow() users = (self.session.query( label("id", user_member.id), label("type", literal("User")), label("name", user_member.username), label("role", GroupEdge._role), label("edge_id", GroupEdge.id), label("expiration", GroupEdge.expiration), ).filter( parent.id == self.id, parent.id == GroupEdge.group_id, user_member.id == GroupEdge.member_pk, GroupEdge.active == True, parent.enabled == True, user_member.enabled == True, or_(GroupEdge.expiration > now, GroupEdge.expiration == None), GroupEdge.member_type == 0, ).order_by(desc("role"), "name")) groups = (self.session.query( label("id", group_member.id), label("type", literal("Group")), label("name", group_member.groupname), label("role", GroupEdge._role), label("edge_id", GroupEdge.id), label("expiration", GroupEdge.expiration), ).filter( parent.id == self.id, parent.id == GroupEdge.group_id, group_member.id == GroupEdge.member_pk, GroupEdge.active == True, parent.enabled == True, group_member.enabled == True, or_(GroupEdge.expiration > now, GroupEdge.expiration == None), GroupEdge.member_type == 1, ).order_by(desc("role"), "name")) return OrderedDict( ((r.type, r.name), r) for r in itertools.chain(users, groups))
def getContacts(displayName='Krishna', phoneNum='8427434777'): ''' Fetch list of contacts for a user { "displayName": "Manmohan", "phoneNum": "9023051078", "user_id": "4" } ''' req_json = request.get_json() #displayName = req_json['displayName'] #phoneNum = req_json['phoneNum'] sender_id = req_json['sender_id'] auth_header = request.headers.get('Authorization') sub = jwtUtil.parse_decode_auth_token(auth_header, SECRETKEY) if ('INVALID' == sub): return json.dumps({'status': 'Logged Out'}) session = Session() u = aliased(Users) contact_objects = session.query(u.user_id, u.display_name, u.callback_url, u.country_phone_code, u.create_date, u.phone_number).\ filter(u.contact_id == sender_id).all() schema = UserSchema(many=True) contactList = schema.dump(contact_objects) session.close() return json.dumps(contactList.data)
def date(date, latest=False): try: if isinstance(date, (str, unicode)): date = datetime.strptime(date, "%Y-%m-%d") if isinstance(date, datetime): date = date.date() except: abort(404) pilot_alias = aliased(User, name='pilot') columns = { 0: (Flight, 'index_score'), 1: (pilot_alias, 'name'), 2: (Flight, 'olc_classic_distance'), 3: (Airport, 'name'), 4: (Club, 'name'), 5: (AircraftModel, 'name'), 6: (Flight, 'takeoff_time'), 7: (Flight, 'id'), 8: (Flight, 'num_comments'), } return _create_list('latest' if latest else 'date', request.args, date=date, columns=columns)
def __init__(self, Acl, AclAccess, Compensate, signatures=None): ''' Construct the compensate service alchemy. @param AclAccess: class of WithAclAccess The ACL access relation mapped class. @param Compensate: class of WithCompensate The compensate relation mapped class. @param signatures: dictionary{string: string|callable(identifier) -> string} A dictionary containing as keys the signatures that will be injected and as a value either the marker to be injected or a callable that takes the identifier as a parameter and provides the marker string value. ''' assert isinstance(Acl, MappedSupport), 'Invalid mapped class %s' % Acl assert issubclass(AclAccess, WithAclAccess), 'Invalid acl access class %s' % AclAccess assert issubclass(Compensate, WithCompensate), 'Invalid compensate class %s' % Compensate if __debug__: if signatures is not None: assert isinstance(signatures, dict), 'Invalid fill in signatures %s' % signatures for signature, marker in signatures.items(): assert isinstance(signature, str), 'Invalid signature %s' % signature assert isinstance(marker, str) or callable(marker), 'Invalid marker %s' % marker self.Acl = Acl self.AclIdentifier = modelId(Acl) self.AclAccess = AclAccess self.AliasAclAccess = aliased(AclAccess) self.Compensate = Compensate self.signatures = signatures
def date(date, latest=False): try: if isinstance(date, (str, unicode)): date = datetime.strptime(date, "%Y-%m-%d") if isinstance(date, datetime): date = date.date() except: abort(404) pilot_alias = aliased(User, name='pilot') columns = { 0: (Flight, 'index_score'), 1: (pilot_alias, 'name'), 2: (Flight, 'olc_classic_distance'), 3: (Airport, 'name'), 4: (Club, 'name'), 5: (AircraftModel, 'name'), 6: (Flight, 'takeoff_time'), 7: (Flight, 'id'), 8: (Flight, 'num_comments'), } return _create_list( 'latest' if latest else 'date', request.args, date=date, columns=columns)
def date(self, date, **kw): try: if isinstance(date, str): date = datetime.strptime(date, "%Y-%m-%d") if isinstance(date, datetime): date = date.date() except: raise HTTPNotFound pilot_alias = aliased(User, name='pilot') columns = { 0: (Flight, 'index_score'), 1: (pilot_alias, 'display_name'), 2: (Flight, 'olc_classic_distance'), 3: (Airport, 'name'), 4: (Club, 'name'), 5: (AircraftModel, 'name'), 6: (Flight, 'takeoff_time'), 7: (Flight, 'id'), 8: (Flight, 'num_comments'), } if kw.get('today', False): return self.__do_list('today', kw, date=date, columns=columns) else: return self.__do_list('date', kw, date=date, columns=columns)
def rank(self): inner_team = aliased(Team) return (DBSession.query(func.count('*') + 1). select_from(inner_team). filter(inner_team.score > Team.score). correlate(Team). label('rank'))
def synchronize_stocks() -> None: # Alias for provider table, and explicit "on" clause for the "join", are mandatory because # VenueProvider model already has a "select" on the provider table for its polymorphic query provider_alias = aliased(Provider) venue_providers = ( VenueProvider.query.join(provider_alias, provider_alias.id == VenueProvider.providerId) .filter(provider_alias.apiUrl.isnot(None)) .filter(provider_alias.isActive.is_(True)) .filter(VenueProvider.isActive.is_(True)) .all() ) for venue_provider in venue_providers: # We need to stock these values inside variables to prevent a crash # if the session is broken and we need to log them venue_id = venue_provider.venueId venue_provider_id = venue_provider.id venue_id_at_offer_provider = venue_provider.venueIdAtOfferProvider provider_name = venue_provider.provider.name try: synchronize_provider_api.synchronize_venue_provider(venue_provider) except Exception as exc: # pylint: disable=broad-except logger.exception("Could not synchronize venue_provider=%s: %s", venue_provider_id, exc) notion_connector.add_to_synchronization_error_database( exception=exc, provider_name=provider_name, venue_id=venue_id, venue_id_at_offer_provider=venue_id_at_offer_provider, ) db.session.rollback()
def getOriginalSource(self, source): originalSource = aliased(SourceMapped, name="original_source") sql = self.session().query(originalSource) sql = sql.join(SourceMapped, originalSource.URI == SourceMapped.OriginURI) sql = sql.filter(SourceMapped.Id == source) return sql.one().Id
def get_data(self): logging.debug("These queries will take a few mins to run.") budget_query = self.session.query( self.models.MovieInfo.movie_id, func.max( cast( func.replace( func.replace(self.models.MovieInfo.info, ",", ""), "$", ""), types.Numeric) ).label('budget') ).filter( self.models.MovieInfo.movie_id.in_(self.movie_ids), self.models.MovieInfo.info_type_id.in_([BUDGET_TYPE_ID]), self.models.MovieInfo.info.like('$%'), ).group_by(self.models.MovieInfo.movie_id ).subquery() year_query = self.session.query( self.models.MovieInfo.movie_id, func.min( func.substr(self.models.MovieInfo.info, -4) ).label('release_year') ).filter( self.models.MovieInfo.movie_id.in_(self.movie_ids), self.models.MovieInfo.info_type_id.in_([RELEASE_DATES_ID]), self.models.MovieInfo.info.like('USA:%') ).group_by(self.models.MovieInfo.movie_id ).subquery() budget_alias = aliased(self.models.MovieInfo, budget_query) year_alias = aliased(self.models.MovieInfo, year_query) return self.session.query( budget_query.columns.movie_id, budget_query.columns.budget, year_query.columns.release_year ).join( year_alias, year_alias.movie_id == budget_alias.movie_id ).distinct( self.models.MovieInfo.movie_id ).filter( self.models.MovieInfo.movie_id.in_(self.movie_ids), )
def getUnpublishedBySource(self, sourceId, thumbSize=None, offset=None, limit=None, detailed=False, q=None): ''' @see: IBlogPostService.getUnpublished ''' assert q is None or isinstance(q, QBlogPostUnpublished), 'Invalid query %s' % q postVerification = aliased(PostVerificationMapped, name='post_verification_filter') sql = self.session().query(BlogPostMapped) sql = sql.filter(BlogPostMapped.Feed == sourceId) deleted = False if q: if QBlogPostUnpublished.isDeleted in q: deleted = q.isDeleted.value sql = buildQuery(sql, q, BlogPostMapped) if q: if QWithCId.search in q: all = self._processLike(q.search.ilike) if q.search.ilike is not None else self._processLike(q.search.like) sql = sql.filter(or_(BlogPostMapped.Meta.ilike(all), BlogPostMapped.CreatedOn.ilike(all), \ BlogPostMapped.Content.ilike(all), BlogPostMapped.ContentPlain.ilike(all), \ )) if QWithCId.status in q or QWithCId.checker in q: sql = sql.join(postVerification, postVerification.Id == BlogPostMapped.Id) sql = sql.join(VerificationStatusMapped, VerificationStatusMapped.id == postVerification.statusId) if QWithCId.status in q: sql = sql.filter(VerificationStatusMapped.Key == q.status.equal) if QWithCId.checker in q: sql = sql.filter(postVerification.Checker == q.checker.equal) if (QWithCId.cId not in q) or (QWithCId.cId in q and QWithCId.cId.start not in q \ and QWithCId.cId.end not in q and QWithCId.cId.since not in q and QWithCId.cId.until not in q): sql = sql.filter(BlogPostMapped.PublishedOn == None) if deleted: sql = sql.filter(BlogPostMapped.DeletedOn != None) else: sql = sql.filter(BlogPostMapped.DeletedOn == None) else: sql = sql.filter((BlogPostMapped.PublishedOn == None) & (BlogPostMapped.DeletedOn == None)) sql = sql.order_by(desc_op(BlogPostMapped.Order)) sqlLimit = buildLimits(sql, offset, limit) posts = self._addImages(self._trimPosts(sqlLimit.distinct(), deleted= not deleted, unpublished=False, published=True), thumbSize) if detailed: posts = IterPost(posts, sql.distinct().count(), offset, limit) lastCidSql = self.session().query(func.MAX(BlogPostMapped.CId)) lastCidSql = lastCidSql.join(CollaboratorMapped, BlogPostMapped.Creator == CollaboratorMapped.User) lastCidSql = lastCidSql.filter(CollaboratorMapped.Source == sourceId) posts.lastCId = lastCidSql.scalar() return posts
def __init__(self, db="", sql_logging=False, schema=None): if db == "" or db is None: # In-memory SQLite connect_url = "sqlite:///" if "://" in db: # User has provided a full path connect_url = db else: # Assume a SQLite file connect_url = "sqlite:///%s" % db engine = sqlalchemy.create_engine(connect_url, echo=sql_logging) self._orm = _Orm(engine, schema=schema) Session = sessionmaker(bind=engine) self._session = Session() self._stoptime1 = aliased(StopTime, name="first_stop_time") self._stoptime2 = aliased(StopTime, name="second_stop_time") self._transfer_fromstop = aliased(Stop, name="tr_from_stop") self._transfer_tostop = aliased(Stop, name="tr_to_stop")
def getOriginalSource(self, source): originalSource = aliased(SourceMapped, name='original_source') sql = self.session().query(originalSource) sql = sql.join(SourceMapped, originalSource.URI == SourceMapped.OriginURI) sql = sql.filter(SourceMapped.Id == source) return sql.one().Id
def __init__(self, db="", sql_logging=False, schema=None): if db == "" or db is None: # In-memory SQLite connect_url = "sqlite:///" if "://" in db: # User has provided a full path connect_url = db else: # Assume a SQLite file connect_url = "sqlite:///%s" % db engine = sqlalchemy.create_engine(connect_url, echo=sql_logging) self._orm = _Orm(engine, schema=schema) Session = sessionmaker(bind=engine) self._session = Session() self._stoptime1 = aliased(StopTime, name="first_stop_time") self._stoptime2 = aliased(StopTime, name="second_stop_time") self._transfer_fromstop = aliased(Stop, name="tr_from_stop") self._transfer_tostop = aliased(Stop, name="tr_to_stop")
def rightsForRbacSQL(self, rbacId, sql=None): """ @see: IRbacService.rightsForRbacSQL """ child, parent = aliased(RoleNode), aliased(RoleNode) subq = sql or self.session().query(RightMapped) subq = subq.join(RbacRight, RbacRight.right == RightMapped.Id) subq = subq.join(child, child.role == RbacRight.rbac) subq = subq.join(parent, and_(child.left >= parent.left, child.right <= parent.right)) subq = subq.join(RbacRole, and_(RbacRole.role == parent.role, RbacRole.rbac == rbacId)) sql = sql or self.session().query(RightMapped) sql = sql.join(RbacRight, and_(RbacRight.right == RightMapped.Id, RbacRight.rbac == rbacId)) sql = sql.union(subq).distinct(RightMapped.Id).order_by(RightMapped.Id) return sql
def set_recent_outings(waypoint, lang): """Set last 10 outings on routes associated to the given waypoint. """ t_outing_route = aliased(Association, name="a1") t_route_wp = aliased(Association, name="a2") with_query_waypoints = _get_select_children(waypoint) recent_outing_ids = get_first_column( DBSession.query(Outing.document_id) .filter(Outing.redirects_to.is_(None)) .join(t_outing_route, Outing.document_id == t_outing_route.child_document_id) .join( t_route_wp, and_( t_route_wp.child_document_id == t_outing_route.parent_document_id, t_route_wp.child_document_type == ROUTE_TYPE, ), ) .join(with_query_waypoints, with_query_waypoints.c.document_id == t_route_wp.parent_document_id) .order_by(Outing.date_end.desc()) .limit(NUM_RECENT_OUTINGS) .all() ) total = ( DBSession.query(Outing.document_id) .filter(Outing.redirects_to.is_(None)) .join(t_outing_route, Outing.document_id == t_outing_route.child_document_id) .join( t_route_wp, and_( t_route_wp.child_document_id == t_outing_route.parent_document_id, t_route_wp.child_document_type == ROUTE_TYPE, ), ) .join(with_query_waypoints, with_query_waypoints.c.document_id == t_route_wp.parent_document_id) .count() ) waypoint.associations["recent_outings"] = get_documents_for_ids( recent_outing_ids, lang, outing_documents_config, total )
def filter_query(query): t_outing_route = aliased(Association, name='a1') t_route_wp = aliased(Association, name='a2') t_route = aliased(Document, name='r') return query. \ join( t_outing_route, Outing.document_id == t_outing_route.child_document_id). \ join( t_route, and_( t_outing_route.parent_document_id == t_route.document_id, t_route.type == ROUTE_TYPE)). \ join( t_route_wp, and_( t_route_wp.parent_document_id == waypoint_id, t_route_wp.child_document_id == t_route.document_id))
def filter_query(query): t_outing_route = aliased(Association, name='a1') t_route_wp = aliased(Association, name='a2') t_route = aliased(Document, name='r') return query. \ join( t_outing_route, Outing.document_id == t_outing_route.child_document_id). \ join( t_route, and_( t_outing_route.parent_document_id == t_route.document_id, t_route.type == ROUTE_TYPE)). \ join( t_route_wp, and_( t_route_wp.parent_document_id == waypoint_id, t_route_wp.child_document_id == t_route.document_id))
def artifact_reagent_labels(session, artifacts): """ Retrieve all artifacts' ancestors with their samples and reagent labels. Only the one that occurs once are an accurate representation of the sample/reagent label relationship. """ # second artifact table to join artifacts with their ancestors ancestors_artifact = aliased(t.Artifact) q = session.query(t.Artifact.artifactid, ancestors_artifact.luid, t.ReagentLabel.name, t.Sample.name) \ .join(ancestors_artifact, t.Artifact.ancestors) \ .join(ancestors_artifact.reagentlabels) \ .join(ancestors_artifact.samples) q = q.filter(t.Artifact.artifactid.in_(artifacts)) return q.all()
def get_recent_friend_user_checkpoints(user_obj, limit = None): """ (faux notification) returns Checkpoints that were recently created by friends """ if limit == None: limit = 8 from db import UserCheckpoint, db, FacebookUser, User, FriendConnection, Checkpoint FriendUserCheckpoint, FriendFacebookUser, FriendUser = aliased(UserCheckpoint), aliased(FacebookUser), aliased(User) q = (db.session.query(UserCheckpoint). join(Checkpoint, Checkpoint.id == UserCheckpoint.checkpoint_id). join(FriendUser, FriendUser.id == UserCheckpoint.user_id). join(FriendFacebookUser, FriendFacebookUser.id == FriendUser.facebook_user_id). join(FriendConnection, FriendConnection.fb_user_to == FriendFacebookUser.id). join(FacebookUser, FacebookUser.id == FriendConnection.fb_user_from). join(User, User.facebook_user_id == FacebookUser.id). filter(User.id == user_obj.id). filter(Checkpoint.creator == FriendUser.id). filter(Checkpoint.demo == False) ) return q.order_by(desc(Checkpoint.date_created)).limit(limit).all()
def get_for_kontkurs(cls, kontkurs, subgroup_number, *args, **kwargs): # Kontgrp2 = aliased(Kontgrp) # Kontkurs2 = aliased(Kontkurs) Kontgrp2 = aliased(Kontgrp) KontgrpCommon = aliased(Kontgrp) KontgrpParent = aliased(Kontgrp) if int(subgroup_number): filter_ = and_(or_( Raspnagr.kontkurs_id == kontkurs.id, Kontlist.kontkurs_id == kontkurs.id, KontgrpCommon.kont_id == kontkurs.id, ), or_( KontgrpCommon.id == None, and_(KontgrpParent.id == None, KontgrpCommon.ngroup == subgroup_number), KontgrpParent.ngroup == subgroup_number ), ) else: filter_ = or_( Raspnagr.kontkurs_id == kontkurs.id, KontgrpCommon.kont_id == kontkurs.id, Kontkurs2.id == kontkurs.id, ) raspis = Raspis.query \ .join(Raspnagr) \ .outerjoin(Kontgrp, Raspnagr.kontgrp_id == Kontgrp.id) \ .outerjoin(Kontgrplist, Kontgrplist.op == Raspnagr.op) \ .outerjoin(Kontlist, Kontlist.op == Raspnagr.op) \ .outerjoin(Kontkurs2, Kontkurs2.id == Kontlist.kontkurs_id) \ .outerjoin(Kontgrp2, Kontgrp2.id == Kontgrplist.kontgrp_id) \ .outerjoin(KontgrpCommon, func.coalesce(Kontgrp2.id, Kontgrp.id) == KontgrpCommon.id) \ .outerjoin(KontgrpParent, KontgrpCommon.parent_id == KontgrpParent.id) \ .filter(filter_) return cls._get_table(raspis, *args, **kwargs)
def get_friends(user_obj, exclude_self=None): """ returns a list of <<User>> objects, if exclude_self is True, it will not consider the current user as a friend. """ if exclude_self == None: exclude_self = False from db import db, User, FriendConnection, FacebookUser FriendFacebookUser, FriendUser = aliased(FacebookUser), aliased(User) friends_q = (db.session.query(User, FriendUser). join(FacebookUser, FacebookUser.id == User.facebook_user_id). join(FriendConnection, FriendConnection.fb_user_from == FacebookUser.id). join(FriendFacebookUser, FriendConnection.fb_user_to == FriendFacebookUser.id). join(FriendUser, FriendUser.facebook_user_id == FriendFacebookUser.id) ) friends_q = friends_q.filter(User.id == user_obj.id) friends = [f[1] for f in friends_q.all()] if not exclude_self: friends += [user_obj] return friends
def getCountries(): ''' fetch all country data ''' # fetching from the database session = Session() c = aliased(Country) country_objects = session.query(c.iso, c.name, c.phonecode).all() # transforming into JSON-serializable objects schema = CountrySchema(many=True) countryList = schema.dump(country_objects) # serializing as JSON session.close() return json.dumps(countryList.data)
def club(id): club = get_requested_record(Club, id) pilot_alias = aliased(User, name='pilot') columns = { 0: (Flight, 'date_local'), 1: (Flight, 'index_score'), 2: (pilot_alias, 'name'), 3: (Flight, 'olc_classic_distance'), 4: (Airport, 'name'), 5: (AircraftModel, 'name'), 6: (Flight, 'takeoff_time'), 7: (Flight, 'id'), 8: (Flight, 'num_comments'), } return _create_list('club', request.args, club=club, columns=columns)
def airport(self, id, **kw): airport = get_requested_record(Airport, id) pilot_alias = aliased(User, name='pilot') columns = { 0: (Flight, 'date_local'), 1: (Flight, 'index_score'), 2: (pilot_alias, 'display_name'), 3: (Flight, 'olc_classic_distance'), 4: (Club, 'name'), 5: (AircraftModel, 'name'), 6: (Flight, 'takeoff_time'), 7: (Flight, 'id'), 8: (Flight, 'num_comments'), } return self.__do_list('airport', kw, airport=airport, columns=columns)
def club(id): club = get_requested_record(Club, id) pilot_alias = aliased(User, name='pilot') columns = { 0: (Flight, 'date_local'), 1: (Flight, 'index_score'), 2: (pilot_alias, 'name'), 3: (Flight, 'olc_classic_distance'), 4: (Airport, 'name'), 5: (AircraftModel, 'name'), 6: (Flight, 'takeoff_time'), 7: (Flight, 'id'), 8: (Flight, 'num_comments'), } return _create_list('club', request.args, club=club, columns=columns)
def getUnpublished(self, blogId, typeId=None, creatorId=None, authorId=None, thumbSize=None, offset=None, limit=None, detailed=False, q=None): ''' @see: IBlogPostService.getUnpublished ''' assert q is None or isinstance(q, QBlogPostUnpublished), 'Invalid query %s' % q postVerification = aliased(PostVerificationMapped, name='post_verification_filter') sql = self._filterQuery(blogId, typeId, creatorId, authorId, q) deleted = False if q: if QBlogPostUnpublished.isDeleted in q: deleted = q.isDeleted.value if QWithCId.cId in q and q.cId: sql = sql.filter(BlogPostMapped.CId != None) sql = buildQuery(sql, q, BlogPostMapped) if QWithCId.status in q or QWithCId.checker in q: sql = sql.join(postVerification, postVerification.Id == BlogPostMapped.Id) sql = sql.join(VerificationStatusMapped, VerificationStatusMapped.id == postVerification.statusId) if QWithCId.status in q: sql = sql.filter(VerificationStatusMapped.Key == q.status.equal) if QWithCId.checker in q: sql = sql.filter(postVerification.Checker == q.checker.equal) if QWithCId.cId not in q: sql = sql.filter(BlogPostMapped.PublishedOn == None) if deleted: sql = sql.filter(BlogPostMapped.DeletedOn != None) else: sql = sql.filter(BlogPostMapped.DeletedOn == None) else: sql = sql.filter((BlogPostMapped.PublishedOn == None) & (BlogPostMapped.DeletedOn == None)) sql = sql.order_by(desc_op(BlogPostMapped.Order)) sqlLimit = buildLimits(sql, offset, limit) posts = self._addImages(self._trimPosts(sqlLimit.distinct(), unpublished=False, published=True), thumbSize) if detailed: posts = IterPost(posts, sql.distinct().count(), offset, limit) posts.lastCId = self.session().query(func.MAX(BlogPostMapped.CId)).filter(BlogPostMapped.Blog == blogId).scalar() return posts
def pilot(id): pilot = get_requested_record(User, id) pilot_alias = aliased(User, name='pilot') mark_flight_notifications_read(pilot) columns = { 0: (Flight, 'date_local'), 1: (Flight, 'index_score'), 2: (pilot_alias, 'name'), 3: (Flight, 'olc_classic_distance'), 4: (Airport, 'name'), 5: (AircraftModel, 'name'), 6: (Flight, 'takeoff_time'), 7: (Flight, 'id'), 8: (Flight, 'num_comments'), } return _create_list('pilot', request.args, pilot=pilot, columns=columns)
def step_info_with_output(session, step_name, project_name=None, sample_name=None, time_from=None, time_to=None, container_name=None, artifact_udfs=None): """ Get a join of output artifact UDFs, for all step of the provided name - filterable to a project, sample name, container name or date range. """ q = session.query(t.Process.luid, t.Process.daterun, t.ProcessIOTracker.inputartifactid, t.Sample.name, t.Project.name, t.Artifact.luid)\ .join(t.Process.type)\ .join(t.Process.processiotrackers) \ .join(t.ProcessIOTracker.artifact) \ .join(t.Artifact.samples)\ .join(t.Sample.project)\ .filter(t.ProcessType.displayname == step_name)\ if artifact_udfs: output_artifact = aliased(t.Artifact) q = q.join(t.ProcessIOTracker.output)\ .join(output_artifact, t.OutputMapping.outputartifactid == output_artifact.artifactid) \ .join(output_artifact.udfs) \ .filter(t.ArtifactUdfView.udfname.in_(artifact_udfs)) \ .filter(t.ArtifactUdfView.udfvalue != None) q = q.add_columns(t.ArtifactUdfView.udfname, t.ArtifactUdfView.udfvalue) if container_name: q = q.filter(t.Container.name == container_name) if time_from: q = q.filter(t.Process.daterun > func.date(time_from)) if time_to: q = q.filter(t.Process.daterun < func.date(time_to)) q = add_filters(q, project_name=project_name, sample_name=sample_name) return q.all()
def pilot(id): pilot = get_requested_record(User, id) pilot_alias = aliased(User, name='pilot') mark_flight_notifications_read(pilot) columns = { 0: (Flight, 'date_local'), 1: (Flight, 'index_score'), 2: (pilot_alias, 'name'), 3: (Flight, 'olc_classic_distance'), 4: (Airport, 'name'), 5: (AircraftModel, 'name'), 6: (Flight, 'takeoff_time'), 7: (Flight, 'id'), 8: (Flight, 'num_comments'), } return _create_list('pilot', request.args, pilot=pilot, columns=columns)
async def find_topics(self, page: int): lastReplyAlias = aliased(UserTable) query = await self.connection.execute( select([ TopicTable, UserTable.c.username, lastReplyAlias.c.username.label("last_replied_by_username"), ], TopicTable.c.deleted_at == None).select_from( TopicTable.outerjoin( UserTable, TopicTable.c.author_id == UserTable.c.id).outerjoin( lastReplyAlias, TopicTable.c.last_replied_by_user_id == lastReplyAlias. c.id)).order_by(desc( TopicTable.c.last_replied_at)).limit(50).offset( (page - 1) * 50)) topics = await query.fetchall() return topics
def add(money): # sql = select([select([func.coalesce(func.sum(Transaction.money), 0) + money]).alias('tmp')]) # sql = select([func.sum(Transaction.money).label('g')]) Tr = aliased(Transaction, name="tr") sql = select([func.coalesce(func.sum(Tr.balance), 0) + money]) # sql = sa_session.query(func.sum(Transaction.money)) # .as_scalar() # .label('aa') # print dir(sql) # print 'sql', sql tr = Transaction(money=money) tr.balance = sql print "tr.created_at0", tr.created_at # tr.moeny = 4294967295 # tr.balance = -1 sa_session.add(tr) print "tr.created_at1", tr.created_at sa_session.commit() print "tr.created_at2", tr.created_at
def get_common_data(self): Auditory2 = aliased(Auditory) return RaspisZaoch.query \ .outerjoin(Raspnagr, Raspnagr.id == RaspisZaoch.raspnagr_id) \ .outerjoin(Auditory, RaspisZaoch.aud == Auditory.id) \ .outerjoin(Auditory2, RaspisZaoch.aud2 == Auditory2.id) \ .outerjoin(Teacher, Teacher.id == Raspnagr.prep_id) \ .outerjoin(Discipline) \ .outerjoin(Normtime) \ .outerjoin(Kontgrp, Raspnagr.kontgrp_id == Kontgrp.id) \ .outerjoin(Kontkurs, Raspnagr.kontkurs_id == Kontkurs.id) \ .outerjoin(Potoklist, Raspnagr.op == Potoklist.op) \ .order_by(RaspisZaoch.dt, RaspisZaoch.para) \ .with_entities( RaspisZaoch.id, RaspisZaoch.raspnagr_id, RaspisZaoch.dt, RaspisZaoch.para, RaspisZaoch.aud, RaspisZaoch.aud2, RaspisZaoch.kont_id, RaspisZaoch.kontgrp_id, RaspisZaoch.op_id, RaspisZaoch.type, RaspisZaoch.hours, Auditory.maxstud, Auditory2.maxstud.label('maxstud2'), Raspnagr.prep_id, Raspnagr.stud, Raspnagr.nt, func.rtrim(Normtime.title).label('normtime'), func.rtrim(Teacher.name).label("teacher"), func.rtrim(Auditory.title).label("auditory"), func.rtrim(Auditory2.title).label("auditory2"), func.rtrim(Discipline.title).label("discipline"), func.coalesce(Potoklist.title, Kontgrp.title, Kontkurs.title).label('konts') )
def add_allocine_internal_ids() -> None: logger.info("Starting to update allocine pivots internal id") # Approx 900 rows so no performance issue to select all allocine_pivots: list[AllocinePivot] = AllocinePivot.query.all() logger.info("%d allocine pivots found", len(allocine_pivots)) for allocine_pivot in allocine_pivots: decoded_id = base64.b64decode(allocine_pivot.theaterId).decode("ascii") internal_id = decoded_id.split("Theater:")[1] allocine_pivot.internalId = internal_id db.session.bulk_save_objects(allocine_pivots) db.session.commit() logger.info("Allocine pivots have been updated") logger.info("Starting to update allocine venue providers internal id") # Approx 48 rows so no performance issue to select all venue_alias = aliased(Venue) allocine_venue_providers_with_pivot: list[tuple[ AllocineVenueProvider, AllocinePivot]] = (AllocineVenueProvider.query.join( venue_alias, venue_alias.id == AllocineVenueProvider.venueId).join( AllocinePivot, AllocinePivot.siret == venue_alias.siret).with_entities( AllocineVenueProvider, AllocinePivot).all()) logger.info("%d allocine venue providers found", len(allocine_venue_providers_with_pivot)) for allocine_venue_provider, allocine_pivot in allocine_venue_providers_with_pivot: allocine_venue_provider.internalId = allocine_pivot.internalId db.session.bulk_save_objects([ allocine_venue_provider for allocine_venue_provider, _unused in allocine_venue_providers_with_pivot ]) db.session.commit() logger.info("Allocine venue providers have been updated")
def __init__(self, Acl, AclAccess, Compensate, signatures=None): ''' Construct the compensate service alchemy. @param AclAccess: class of WithAclAccess The ACL access relation mapped class. @param Compensate: class of WithCompensate The compensate relation mapped class. @param signatures: dictionary{string: string|callable(identifier) -> string} A dictionary containing as keys the signatures that will be injected and as a value either the marker to be injected or a callable that takes the identifier as a parameter and provides the marker string value. ''' assert isinstance(Acl, MappedSupport), 'Invalid mapped class %s' % Acl assert issubclass( AclAccess, WithAclAccess), 'Invalid acl access class %s' % AclAccess assert issubclass( Compensate, WithCompensate), 'Invalid compensate class %s' % Compensate if __debug__: if signatures is not None: assert isinstance( signatures, dict), 'Invalid fill in signatures %s' % signatures for signature, marker in signatures.items(): assert isinstance(signature, str), 'Invalid signature %s' % signature assert isinstance( marker, str) or callable(marker), 'Invalid marker %s' % marker self.Acl = Acl self.AclIdentifier = modelId(Acl) self.AclAccess = AclAccess self.AliasAclAccess = aliased(AclAccess) self.Compensate = Compensate self.signatures = signatures
def get_target_server(self, sess, stickyvalues, servertype, server_address, endpoint_name, endpoint_key): ''' This method finds the target server who will handle this request based on stickyvalues or minimum load Logic: -This method returns the server which handles the stickyvalue if found. -If not found , tries to find the minimum loaded server. -If current server is the minimum loaded servers, adds the corresponding sticky values instantly - At the end returns the dict server as a target server and stickymappings if found already existing stickyness. If return dict contains the server and existing_mapping_list ==> already sticked server found If existing_mapping_list is empty means this is newly found minimum loaded server (either self or some other server) :param stickyvalue: the sticky value string. :param servertype: the type of the server :param server_address: he address of the server which is the unique key :param endpoint_key: unique key of the endpoint :param endpoint_name: name of the endpoint ''' sess.flush() # logging.getLogger().debug("Servertype passed : %s", servertype) server = None existing_mapping_list = None sticky_found = False with self.__class__.internal_lock: #logging.getLogger().debug("stickyvalues : %s ", stickyvalues) if stickyvalues: try: existing_mapping_list = sess.query(self.StickyMapping).filter(self.StickyMapping.sticky_value.in_(stickyvalues)).all() sticky_found = True except Exception as e: logging.getLogger().debug("Exception occurred in the stickymapping query : %s ", e) if not existing_mapping_list: min_loaded_server = None #Following query finds the minimum loaded server of the given type, with load less than 100% ParentServer = aliased(self.Server, name='parent_server') min_loaded_server = sess.query(self.Server, ParentServer).\ outerjoin(ParentServer, and_(self.Server.server_type==ParentServer.server_type, ParentServer.load < self.Server.load)).\ filter(and_(self.Server.server_type==servertype, ParentServer.load==None, self.Server.load < 100)).all() if min_loaded_server: #logging.getLogger().debug("min loaded server found type : %s ", type(min_loaded_server)) s = None for m in min_loaded_server: if server_address == m[0].unique_key: s=m[0] server = s if s else min_loaded_server[0][0] unique_key = server.unique_key if unique_key == server_address: try: for s in stickyvalues: sticky_record = self.StickyMapping(server.server_id, endpoint_key, endpoint_name, s) sess.add(sticky_record) sess.commit() except Exception as e: logging.getLogger().debug("Exception occurred while adding sticky values : %s ", e) sess.rollback() else: #TODO : Indicate System is totally full all extractors are 100% pass #session closing need to be taken care in case raising exception #raise Exception else: pass #logging.getLogger().debug("found existing mapping") if server is None: server_ids = set([e.server_id for e in existing_mapping_list]) #logging.getLogger().debug("Already Sticky value found for: %s - servertype: %s. Sticky list is : %s", server_ids, servertype, existing_mapping_list) #The server_ids list above will be at most one server of each type even if sticky values for different type of servers are same server = sess.query(self.Server).filter(and_(self.Server.server_id.in_(server_ids), self.Server.server_type==servertype)).one() return_dict = {'target_server' : server, 'stickymapping' : existing_mapping_list, 'sticky_found' : sticky_found} ''' If return dict contains the server and existing_mapping_list ==> already sticked server found If existing_mapping_list is empty means this is newly found minimum loaded server (either self or some other server) ''' # logging.getLogger().debug("returning the redirect server : %s", return_dict) return return_dict
def get_time_series_analytics(request): import isodate from datetime import datetime start = request.GET.get("start", None) end = request.GET.get("end", None) interval = request.GET.get("interval", None) discussion = request.context._instance user_id = authenticated_userid(request) or Everyone try: if start: start = parse_datetime(start) if end: end = parse_datetime(end) if interval: interval = isodate.parse_duration(interval) except isodate.ISO8601Error as e: raise HTTPBadRequest(e) if interval and not start: raise HTTPBadRequest("You cannot define an interval and no start") if interval and not end: end = datetime.now() results = [] from sqlalchemy import Table, MetaData, and_, case, cast, Float from sqlalchemy.exc import ProgrammingError import pprint import transaction with transaction.manager: metadata = MetaData(discussion.db.get_bind() ) # make sure we are using the same connexion intervals_table = Table( 'temp_table_intervals_' + str(user_id), metadata, Column('interval_id', Integer, primary_key=True), Column('interval_start', DateTime, nullable=False), Column('interval_end', DateTime, nullable=False), prefixes=None if discussion.using_virtuoso else ['TEMPORARY']) try: intervals_table.drop( ) # In case there is a leftover from a previous crash except ProgrammingError: pass intervals_table.create() interval_start = start intervals = [] if interval: while interval_start < end: interval_end = min(interval_start + interval, end) intervals.append({ 'interval_start': interval_start, 'interval_end': interval_end }) interval_start = interval_start + interval #pprint.pprint(intervals) discussion.db.execute(intervals_table.insert(), intervals) else: raise HTTPBadRequest("Please specify an interval") from assembl.models import Post, AgentProfile, AgentStatusInDiscussion, ViewPost # The posters post_subquery = discussion.db.query( intervals_table.c.interval_id, func.count(distinct(Post.id)).label('count_posts'), func.count(distinct(Post.creator_id)).label('count_post_authors'), # func.DB.DBA.BAG_AGG(Post.creator_id).label('post_authors'), # func.DB.DBA.BAG_AGG(Post.id).label('post_ids'), ) post_subquery = post_subquery.outerjoin( Post, and_(Post.creation_date >= intervals_table.c.interval_start, Post.creation_date < intervals_table.c.interval_end, Post.discussion_id == discussion.id)) post_subquery = post_subquery.group_by(intervals_table.c.interval_id) post_subquery = post_subquery.subquery() # The cumulative posters cumulative_posts_aliased = aliased(Post) cumulative_posts_subquery = discussion.db.query( intervals_table.c.interval_id, func.count(distinct( cumulative_posts_aliased.id)).label('count_cumulative_posts'), func.count(distinct(cumulative_posts_aliased.creator_id)).label( 'count_cumulative_post_authors') # func.DB.DBA.BAG_AGG(cumulative_posts_aliased.id).label('cumulative_post_ids') ) cumulative_posts_subquery = cumulative_posts_subquery.outerjoin( cumulative_posts_aliased, and_( cumulative_posts_aliased.creation_date < intervals_table.c.interval_end, cumulative_posts_aliased.discussion_id == discussion.id)) cumulative_posts_subquery = cumulative_posts_subquery.group_by( intervals_table.c.interval_id) cumulative_posts_subquery = cumulative_posts_subquery.subquery() # The post viewers postViewers = aliased(ViewPost) viewedPosts = aliased(Post) post_viewers_subquery = discussion.db.query( intervals_table.c.interval_id, func.count(distinct( postViewers.actor_id)).label('UNRELIABLE_count_post_viewers')) post_viewers_subquery = post_viewers_subquery.outerjoin(postViewers, and_(postViewers.creation_date >= intervals_table.c.interval_start, postViewers.creation_date < intervals_table.c.interval_end)).\ join(viewedPosts, and_(postViewers.post_id == viewedPosts.id, viewedPosts.discussion_id == discussion.id)) post_viewers_subquery = post_viewers_subquery.group_by( intervals_table.c.interval_id) post_viewers_subquery = post_viewers_subquery.subquery() # The visitors firstTimeVisitorAgent = aliased(AgentStatusInDiscussion) visitors_subquery = discussion.db.query( intervals_table.c.interval_id, func.count(firstTimeVisitorAgent.id).label( 'count_first_time_logged_in_visitors'), # func.DB.DBA.BAG_AGG(firstTimeVisitorAgent.id).label('first_time_visitors') ) visitors_subquery = visitors_subquery.outerjoin( firstTimeVisitorAgent, and_( firstTimeVisitorAgent.first_visit >= intervals_table.c.interval_start, firstTimeVisitorAgent.first_visit < intervals_table.c.interval_end, firstTimeVisitorAgent.discussion_id == discussion.id)) visitors_subquery = visitors_subquery.group_by( intervals_table.c.interval_id) visitors_subquery = visitors_subquery.subquery() # The cumulative visitors cumulativeVisitorAgent = aliased(AgentStatusInDiscussion) cumulative_visitors_query = discussion.db.query( intervals_table.c.interval_id, func.count(distinct(cumulativeVisitorAgent.id)).label( 'count_cumulative_logged_in_visitors'), # func.DB.DBA.BAG_AGG(cumulativeVisitorAgent.id).label('first_time_visitors') ) cumulative_visitors_query = cumulative_visitors_query.outerjoin( cumulativeVisitorAgent, and_( cumulativeVisitorAgent.first_visit < intervals_table.c.interval_end, cumulativeVisitorAgent.discussion_id == discussion.id)) cumulative_visitors_query = cumulative_visitors_query.group_by( intervals_table.c.interval_id) cumulative_visitors_subquery = cumulative_visitors_query.subquery() # query = cumulative_visitors_query # The members (can go up and down...) Assumes that first_subscribed is available commented_out = """ first_subscribed isn't yet filled in by assembl memberAgentStatus = aliased(AgentStatusInDiscussion) members_subquery = discussion.db.query(intervals_table.c.interval_id, func.count(memberAgentStatus.id).label('count_approximate_members') ) members_subquery = members_subquery.outerjoin(memberAgentStatus, ((memberAgentStatus.last_unsubscribed >= intervals_table.c.interval_end) | (memberAgentStatus.last_unsubscribed.is_(None))) & ((memberAgentStatus.first_subscribed < intervals_table.c.interval_end) | (memberAgentStatus.first_subscribed.is_(None))) & (memberAgentStatus.discussion_id==discussion.id)) members_subquery = members_subquery.group_by(intervals_table.c.interval_id) query = members_subquery members_subquery = members_subquery.subquery() """ subscribersAgentStatus = aliased(AgentStatusInDiscussion) subscribers_query = discussion.db.query( intervals_table.c.interval_id, func.sum( case([(subscribersAgentStatus.last_visit == None, 0), (and_( subscribersAgentStatus.last_visit < intervals_table.c.interval_end, subscribersAgentStatus.last_visit >= intervals_table.c.interval_start), 1)], else_=0)).label('retention_count_last_visit_in_period'), func.sum( case( [(subscribersAgentStatus.first_visit == None, 0), (and_( subscribersAgentStatus.first_visit < intervals_table.c.interval_end, subscribersAgentStatus.first_visit >= intervals_table.c.interval_start), 1)], else_=0)).label('recruitment_count_first_visit_in_period'), func.sum( case([(subscribersAgentStatus.first_subscribed == None, 0), (and_( subscribersAgentStatus.first_subscribed < intervals_table.c.interval_end, subscribersAgentStatus.first_subscribed >= intervals_table.c.interval_start), 1)], else_=0)). label('UNRELIABLE_recruitment_count_first_subscribed_in_period'), func.sum( case([(subscribersAgentStatus.last_unsubscribed == None, 0), (and_( subscribersAgentStatus.last_unsubscribed < intervals_table.c.interval_end, subscribersAgentStatus.last_unsubscribed >= intervals_table.c.interval_start), 1)], else_=0) ).label('UNRELIABLE_retention_count_first_subscribed_in_period'), ) subscribers_query = subscribers_query.outerjoin( subscribersAgentStatus, subscribersAgentStatus.discussion_id == discussion.id) subscribers_query = subscribers_query.group_by( intervals_table.c.interval_id) subscribers_subquery = subscribers_query.subquery() #query = subscribers_query combined_query = discussion.db.query( intervals_table, post_subquery, cumulative_posts_subquery, post_viewers_subquery, visitors_subquery, cumulative_visitors_subquery, case([ (cumulative_posts_subquery.c.count_cumulative_post_authors == 0, None), (cumulative_posts_subquery.c.count_cumulative_post_authors != 0, (cast(post_subquery.c.count_post_authors, Float) / cast( cumulative_posts_subquery.c.count_cumulative_post_authors, Float))) ]).label('fraction_cumulative_authors_who_posted_in_period'), case([ (cumulative_visitors_subquery.c. count_cumulative_logged_in_visitors == 0, None), (cumulative_visitors_subquery.c. count_cumulative_logged_in_visitors != 0, (cast(post_subquery.c.count_post_authors, Float) / cast( cumulative_visitors_subquery.c. count_cumulative_logged_in_visitors, Float))) ]).label( 'fraction_cumulative_logged_in_visitors_who_posted_in_period'), subscribers_subquery, ) combined_query = combined_query.join( post_subquery, post_subquery.c.interval_id == intervals_table.c.interval_id) combined_query = combined_query.join( post_viewers_subquery, post_viewers_subquery.c.interval_id == intervals_table.c.interval_id) combined_query = combined_query.join( visitors_subquery, visitors_subquery.c.interval_id == intervals_table.c.interval_id) combined_query = combined_query.join( cumulative_visitors_subquery, cumulative_visitors_subquery.c.interval_id == intervals_table.c.interval_id) # combined_query = combined_query.join(members_subquery, members_subquery.c.interval_id==intervals_table.c.interval_id) combined_query = combined_query.join( subscribers_subquery, subscribers_subquery.c.interval_id == intervals_table.c.interval_id) combined_query = combined_query.join( cumulative_posts_subquery, cumulative_posts_subquery.c.interval_id == intervals_table.c.interval_id) query = combined_query query = query.order_by(intervals_table.c.interval_id) results = query.all() # pprint.pprint(results) # end of transaction intervals_table.drop() if not (request.GET.get('format', None) == 'csv' or request.accept == 'text/csv'): # json default from assembl.lib.json import DateJSONEncoder return Response(json.dumps(results, cls=DateJSONEncoder), content_type='application/json') fieldnames = [ "interval_id", "interval_start", "interval_end", "count_first_time_logged_in_visitors", "count_cumulative_logged_in_visitors", "fraction_cumulative_logged_in_visitors_who_posted_in_period", "count_post_authors", "count_cumulative_post_authors", "fraction_cumulative_authors_who_posted_in_period", "count_posts", "count_cumulative_posts", "recruitment_count_first_visit_in_period", "UNRELIABLE_recruitment_count_first_subscribed_in_period", "retention_count_last_visit_in_period", "UNRELIABLE_retention_count_first_subscribed_in_period", "UNRELIABLE_count_post_viewers", ] # otherwise assume csv return csv_response(fieldnames, [r._asdict() for r in results])
from ally.support.sqlalchemy.util_service import buildQuery, buildLimits from ally.container.support import setup from sqlalchemy.orm.exc import NoResultFound from sqlalchemy.orm.util import aliased from sqlalchemy.sql import functions as fn from superdesk.person.meta.person import PersonMapped from superdesk.post.api.post import IPostService, Post from superdesk.post.meta.type import PostTypeMapped from sqlalchemy.sql.operators import desc_op from livedesk.api.blog_type_post import IBlogTypePostService, BlogTypePost, \ QBlogTypePost, BlogTypePostPersist from livedesk.meta.blog_type_post import BlogTypePostMapped, BlogTypePostEntry # -------------------------------------------------------------------- UserPerson = aliased(PersonMapped) @injected @setup(IBlogTypePostService, name='blogTypePostService') class BlogTypePostServiceAlchemy(SessionSupport, IBlogTypePostService): ''' Implementation for @see: IBlogPostService ''' postService = IPostService wire.entity('postService') def __init__(self): ''' Construct the blog post service.
def _create_list( date=None, pilot=None, club=None, airport=None, pinned=None, filter=None, default_sorting_column="score", default_sorting_order="desc", ): pilot_alias = aliased(User, name="pilot") owner_alias = aliased(User, name="owner") subq = (db.session.query(FlightComment.flight_id, func.count("*").label("count")).group_by( FlightComment.flight_id).subquery()) current_user = User.get(request.user_id) if request.user_id else None flights = (db.session.query(Flight, subq.c.count).filter( Flight.is_listable(current_user)).join(Flight.igc_file).options( contains_eager(Flight.igc_file)).join( owner_alias, IGCFile.owner).options( contains_eager(Flight.igc_file, IGCFile.owner, alias=owner_alias)).outerjoin( pilot_alias, Flight.pilot).options( contains_eager(Flight.pilot, alias=pilot_alias)). options(joinedload(Flight.co_pilot)).outerjoin( Flight.club).options(contains_eager(Flight.club)).outerjoin( Flight.takeoff_airport).options( contains_eager(Flight.takeoff_airport)).outerjoin( Flight.model).options( contains_eager(Flight.model)).outerjoin( (subq, Flight.comments))) if date: flights = flights.filter(Flight.date_local == date) if pilot: flights = flights.filter( or_(Flight.pilot == pilot, Flight.co_pilot == pilot)) if club: flights = flights.filter(Flight.club == club) if airport: flights = flights.filter(Flight.takeoff_airport == airport) if pinned: flights = flights.filter(Flight.id.in_(pinned)) if filter is not None: flights = flights.filter(filter) valid_columns = { "date": getattr(Flight, "date_local"), "score": getattr(Flight, "index_score"), "pilot": getattr(pilot_alias, "name"), "distance": getattr(Flight, "olc_classic_distance"), "airport": getattr(Airport, "name"), "club": getattr(Club, "name"), "aircraft": getattr(AircraftModel, "name"), "time": getattr(Flight, "takeoff_time"), } flights_count = flights.count() flights = Sorter.sort( flights, "flights", default_sorting_column, valid_columns=valid_columns, default_order=default_sorting_order, ) flights = flights.order_by(Flight.index_score.desc()) flights = Pager.paginate( flights, "flights", items_per_page=int( current_app.config.get("SKYLINES_LISTS_DISPLAY_LENGTH", 50)), ) flight_schema = FlightSchema() flights_json = [] for f, num_comments in flights: flight = flight_schema.dump(f).data flight["private"] = not f.is_rankable() flight["numComments"] = num_comments flights_json.append(flight) json = dict(flights=flights_json, count=flights_count) if date: json["date"] = date.isoformat() if pilot: user_schema = UserSchema(only=("id", "name")) json["pilot"] = user_schema.dump(pilot).data if club: club_schema = ClubSchema(only=("id", "name")) json["club"] = club_schema.dump(club).data if airport: airport_schema = AirportSchema(only=("id", "name", "countryCode")) json["airport"] = airport_schema.dump(airport).data return jsonify(json)
def _create_list( date=None, pilot=None, club=None, airport=None, pinned=None, filter=None, default_sorting_column="score", default_sorting_order="desc", ): pilot_alias = aliased(User, name="pilot") owner_alias = aliased(User, name="owner") subq = ( db.session.query(FlightComment.flight_id, func.count("*").label("count")) .group_by(FlightComment.flight_id) .subquery() ) current_user = User.get(request.user_id) if request.user_id else None flights = ( db.session.query(Flight, subq.c.count) .filter(Flight.is_listable(current_user)) .join(Flight.igc_file) .options(contains_eager(Flight.igc_file)) .join(owner_alias, IGCFile.owner) .options(contains_eager(Flight.igc_file, IGCFile.owner, alias=owner_alias)) .outerjoin(pilot_alias, Flight.pilot) .options(contains_eager(Flight.pilot, alias=pilot_alias)) .options(joinedload(Flight.co_pilot)) .outerjoin(Flight.club) .options(contains_eager(Flight.club)) .outerjoin(Flight.takeoff_airport) .options(contains_eager(Flight.takeoff_airport)) .outerjoin(Flight.model) .options(contains_eager(Flight.model)) .outerjoin((subq, Flight.comments)) ) if date: flights = flights.filter(Flight.date_local == date) if pilot: flights = flights.filter(or_(Flight.pilot == pilot, Flight.co_pilot == pilot)) if club: flights = flights.filter(Flight.club == club) if airport: flights = flights.filter(Flight.takeoff_airport == airport) if pinned: flights = flights.filter(Flight.id.in_(pinned)) if filter is not None: flights = flights.filter(filter) valid_columns = { "date": getattr(Flight, "date_local"), "score": getattr(Flight, "index_score"), "pilot": getattr(pilot_alias, "name"), "distance": getattr(Flight, "olc_classic_distance"), "airport": getattr(Airport, "name"), "club": getattr(Club, "name"), "aircraft": getattr(AircraftModel, "name"), "time": getattr(Flight, "takeoff_time"), } flights_count = flights.count() flights = Sorter.sort( flights, "flights", default_sorting_column, valid_columns=valid_columns, default_order=default_sorting_order ) flights = flights.order_by(Flight.index_score.desc()) flights = Pager.paginate( flights, "flights", items_per_page=int(current_app.config.get("SKYLINES_LISTS_DISPLAY_LENGTH", 50)) ) flight_schema = FlightSchema() flights_json = [] for f, num_comments in flights: flight = flight_schema.dump(f).data flight["private"] = not f.is_rankable() flight["numComments"] = num_comments flights_json.append(flight) json = dict(flights=flights_json, count=flights_count) if date: json["date"] = date.isoformat() if pilot: user_schema = UserSchema(only=("id", "name")) json["pilot"] = user_schema.dump(pilot).data if club: club_schema = ClubSchema(only=("id", "name")) json["club"] = club_schema.dump(club).data if airport: airport_schema = AirportSchema(only=("id", "name", "countryCode")) json["airport"] = airport_schema.dump(airport).data return jsonify(json)
def _create_list(date=None, pilot=None, club=None, airport=None, pinned=None, filter=None, default_sorting_column='score', default_sorting_order='desc'): pilot_alias = aliased(User, name='pilot') owner_alias = aliased(User, name='owner') subq = db.session \ .query(FlightComment.flight_id, func.count('*').label('count')) \ .group_by(FlightComment.flight_id).subquery() current_user = User.get(request.user_id) if request.user_id else None flights = db.session.query(Flight, subq.c.count) \ .filter(Flight.is_listable(current_user)) \ .join(Flight.igc_file) \ .options(contains_eager(Flight.igc_file)) \ .join(owner_alias, IGCFile.owner) \ .options(contains_eager(Flight.igc_file, IGCFile.owner, alias=owner_alias)) \ .outerjoin(pilot_alias, Flight.pilot) \ .options(contains_eager(Flight.pilot, alias=pilot_alias)) \ .options(joinedload(Flight.co_pilot)) \ .outerjoin(Flight.club) \ .options(contains_eager(Flight.club)) \ .outerjoin(Flight.takeoff_airport) \ .options(contains_eager(Flight.takeoff_airport)) \ .outerjoin(Flight.model) \ .options(contains_eager(Flight.model)) \ .outerjoin((subq, Flight.comments)) if date: flights = flights.filter(Flight.date_local == date) if pilot: flights = flights.filter( or_(Flight.pilot == pilot, Flight.co_pilot == pilot)) if club: flights = flights.filter(Flight.club == club) if airport: flights = flights.filter(Flight.takeoff_airport == airport) if pinned: flights = flights.filter(Flight.id.in_(pinned)) if filter is not None: flights = flights.filter(filter) valid_columns = { 'date': getattr(Flight, 'date_local'), 'score': getattr(Flight, 'index_score'), 'pilot': getattr(pilot_alias, 'name'), 'distance': getattr(Flight, 'olc_classic_distance'), 'airport': getattr(Airport, 'name'), 'club': getattr(Club, 'name'), 'aircraft': getattr(AircraftModel, 'name'), 'time': getattr(Flight, 'takeoff_time'), } flights_count = flights.count() flights = Sorter.sort(flights, 'flights', default_sorting_column, valid_columns=valid_columns, default_order=default_sorting_order) flights = flights.order_by(Flight.index_score.desc()) flights = Pager.paginate(flights, 'flights', items_per_page=int( current_app.config.get( 'SKYLINES_LISTS_DISPLAY_LENGTH', 50))) flight_schema = FlightSchema() flights_json = [] for f, num_comments in flights: flight = flight_schema.dump(f).data flight['private'] = not f.is_rankable() flight['numComments'] = num_comments flights_json.append(flight) json = dict(flights=flights_json, count=flights_count) if date: json['date'] = date.isoformat() if pilot: user_schema = UserSchema(only=('id', 'name')) json['pilot'] = user_schema.dump(pilot).data if club: club_schema = ClubSchema(only=('id', 'name')) json['club'] = club_schema.dump(club).data if airport: airport_schema = AirportSchema(only=('id', 'name', 'countryCode')) json['airport'] = airport_schema.dump(airport).data return jsonify(json)
def _create_list(tab, kw, date=None, pilot=None, club=None, airport=None, pinned=None, filter=None, default_sorting_column='score', default_sorting_order='desc'): pilot_alias = aliased(User, name='pilot') owner_alias = aliased(User, name='owner') subq = db.session \ .query(FlightComment.flight_id, func.count('*').label('count')) \ .group_by(FlightComment.flight_id).subquery() flights = db.session.query(Flight, subq.c.count) \ .filter(Flight.is_listable(g.current_user)) \ .join(Flight.igc_file) \ .options(contains_eager(Flight.igc_file)) \ .join(owner_alias, IGCFile.owner) \ .options(contains_eager(Flight.igc_file, IGCFile.owner, alias=owner_alias)) \ .outerjoin(pilot_alias, Flight.pilot) \ .options(contains_eager(Flight.pilot, alias=pilot_alias)) \ .options(joinedload(Flight.co_pilot)) \ .outerjoin(Flight.club) \ .options(contains_eager(Flight.club)) \ .outerjoin(Flight.takeoff_airport) \ .options(contains_eager(Flight.takeoff_airport)) \ .outerjoin(Flight.model) \ .options(contains_eager(Flight.model)) \ .outerjoin((subq, Flight.comments)) if date: flights = flights.filter(Flight.date_local == date) if pilot: flights = flights.filter(or_(Flight.pilot == pilot, Flight.co_pilot == pilot)) if club: flights = flights.filter(Flight.club == club) if airport: flights = flights.filter(Flight.takeoff_airport == airport) if pinned: flights = flights.filter(Flight.id.in_(pinned)) if filter is not None: flights = flights.filter(filter) valid_columns = { 'date': getattr(Flight, 'date_local'), 'score': getattr(Flight, 'index_score'), 'pilot': getattr(pilot_alias, 'name'), 'distance': getattr(Flight, 'olc_classic_distance'), 'airport': getattr(Airport, 'name'), 'club': getattr(Club, 'name'), 'aircraft': getattr(AircraftModel, 'name'), 'time': getattr(Flight, 'takeoff_time'), } flights_count = flights.count() flights = Sorter.sort(flights, 'flights', default_sorting_column, valid_columns=valid_columns, default_order=default_sorting_order) flights = flights.order_by(Flight.index_score.desc()) flights = Pager.paginate(flights, 'flights', items_per_page=int(current_app.config.get('SKYLINES_LISTS_DISPLAY_LENGTH', 50))) return render_template('flights/list.jinja', tab=tab, date=date, pilot=pilot, club=club, airport=airport, flights=flights, flights_count=flights_count)
def __do_list(self, tab, kw, date=None, pilot=None, club=None, airport=None, \ pinned=None, filter=None, columns=None): pilot_alias = aliased(User, name='pilot') owner_alias = aliased(User, name='owner') subq = DBSession.query(FlightComment.flight_id, func.count('*').label('count')) \ .group_by(FlightComment.flight_id).subquery() flights = DBSession.query(Flight, subq.c.count) \ .outerjoin(Flight.igc_file) \ .options(contains_eager(Flight.igc_file)) \ .outerjoin(owner_alias, IGCFile.owner) \ .options(contains_eager(Flight.igc_file, IGCFile.owner, alias=owner_alias)) \ .outerjoin(pilot_alias, Flight.pilot) \ .options(contains_eager(Flight.pilot, alias=pilot_alias)) \ .options(joinedload(Flight.co_pilot)) \ .outerjoin(Flight.club) \ .options(contains_eager(Flight.club)) \ .outerjoin(Flight.takeoff_airport) \ .options(contains_eager(Flight.takeoff_airport)) \ .outerjoin(Flight.model) \ .options(contains_eager(Flight.model)) \ .outerjoin((subq, Flight.comments)) if date: flights = flights.filter(Flight.date_local == date) if pilot: flights = flights.filter(or_(Flight.pilot == pilot, Flight.co_pilot == pilot)) if club: flights = flights.filter(Flight.club == club) if airport: flights = flights.filter(Flight.takeoff_airport == airport) if pinned: flights = flights.filter(Flight.id.in_(pinned)) if filter is not None: flights = flights.filter(filter) if request.response_type == 'application/json': if not columns: columns = { 0: (Flight, 'date_local'), 1: (Flight, 'index_score'), 2: (pilot_alias, 'display_name'), 3: (Flight, 'olc_classic_distance'), 4: (Airport, 'name'), 5: (Club, 'name'), 6: (AircraftModel, 'name'), 7: (Flight, 'takeoff_time'), 8: (Flight, 'id'), 9: (Flight, 'num_comments'), } flights, response_dict = GetDatatableRecords(kw, flights, columns) aaData = [] for flight, num_comments in flights: aaData.append(dict(takeoff_time=flight.takeoff_time.strftime('%H:%M'), landing_time=flight.landing_time.strftime('%H:%M'), date=flight.date_local.strftime('%d.%m.%Y'), date_formatted=format_date(flight.date_local), index_score=flight.index_score, olc_classic_distance=flight.olc_classic_distance, pilot_id=flight.pilot_id, pilot=flight.pilot and flight.pilot.display_name, co_pilot_id=flight.co_pilot_id, co_pilot=flight.co_pilot and flight.co_pilot.display_name, club_id=flight.club_id, club=flight.club and truncate(flight.club.name, 25), owner=flight.igc_file.owner.display_name, takeoff_airport=flight.takeoff_airport and flight.takeoff_airport.name, takeoff_airport_id=flight.takeoff_airport and flight.takeoff_airport.id, takeoff_airport_country_code=flight.takeoff_airport and flight.takeoff_airport.country_code.lower(), takeoff_airport_country_name=flight.takeoff_airport and country_name(flight.takeoff_airport.country_code), aircraft=(flight.model and flight.model.name) or (flight.igc_file.model and '[' + flight.igc_file.model + ']'), aircraft_reg=flight.registration or flight.igc_file.registration or "Unknown", flight_id=flight.id, num_comments=num_comments)) return dict(response_dict, aaData=aaData) else: if not date: flights = flights.order_by(desc(Flight.date_local)) flights_count = flights.count() if flights_count > int(config.get('skylines.lists.server_side', 250)): limit = int(config.get('skylines.lists.display_length', 50)) else: limit = int(config.get('skylines.lists.server_side', 250)) flights = flights.order_by(desc(Flight.index_score)) flights = flights.limit(limit) return dict(tab=tab, date=date, pilot=pilot, club=club, airport=airport, flights=flights, flights_count=flights_count)
def get_time_series_analytics(request): import isodate from datetime import datetime start = request.GET.get("start", None) end = request.GET.get("end", None) interval = request.GET.get("interval", None) discussion = request.context._instance user_id = authenticated_userid(request) or Everyone try: if start: start = parse_datetime(start) if end: end = parse_datetime(end) if interval: interval = isodate.parse_duration(interval) except isodate.ISO8601Error as e: raise HTTPBadRequest(e) if interval and not start: raise HTTPBadRequest("You cannot define an interval and no start") if interval and not end: end = datetime.now() results = [] from sqlalchemy import Table, MetaData, and_, case, cast, Float from sqlalchemy.exc import ProgrammingError import pprint import transaction with transaction.manager: metadata = MetaData(discussion.db.get_bind()) # make sure we are using the same connexion intervals_table = Table('temp_table_intervals_' + str(user_id), metadata, Column('interval_id', Integer, primary_key=True), Column('interval_start', DateTime, nullable=False), Column('interval_end', DateTime, nullable=False), prefixes=None if discussion.using_virtuoso else ['TEMPORARY'] ) try: intervals_table.drop() # In case there is a leftover from a previous crash except ProgrammingError: pass intervals_table.create() interval_start = start intervals = [] if interval: while interval_start < end: interval_end = min(interval_start + interval, end) intervals.append({'interval_start': interval_start, 'interval_end': interval_end}) interval_start = interval_start + interval #pprint.pprint(intervals) discussion.db.execute(intervals_table.insert(), intervals) else: raise HTTPBadRequest("Please specify an interval") from assembl.models import Post, AgentProfile, AgentStatusInDiscussion, ViewPost # The posters post_subquery = discussion.db.query(intervals_table.c.interval_id, func.count(distinct(Post.id)).label('count_posts'), func.count(distinct(Post.creator_id)).label('count_post_authors'), # func.DB.DBA.BAG_AGG(Post.creator_id).label('post_authors'), # func.DB.DBA.BAG_AGG(Post.id).label('post_ids'), ) post_subquery = post_subquery.outerjoin(Post, and_(Post.creation_date >= intervals_table.c.interval_start, Post.creation_date < intervals_table.c.interval_end, Post.discussion_id == discussion.id)) post_subquery = post_subquery.group_by(intervals_table.c.interval_id) post_subquery = post_subquery.subquery() # The cumulative posters cumulative_posts_aliased = aliased(Post) cumulative_posts_subquery = discussion.db.query(intervals_table.c.interval_id, func.count(distinct(cumulative_posts_aliased.id)).label('count_cumulative_posts'), func.count(distinct(cumulative_posts_aliased.creator_id)).label('count_cumulative_post_authors') # func.DB.DBA.BAG_AGG(cumulative_posts_aliased.id).label('cumulative_post_ids') ) cumulative_posts_subquery = cumulative_posts_subquery.outerjoin(cumulative_posts_aliased, and_(cumulative_posts_aliased.creation_date < intervals_table.c.interval_end, cumulative_posts_aliased.discussion_id == discussion.id)) cumulative_posts_subquery = cumulative_posts_subquery.group_by(intervals_table.c.interval_id) cumulative_posts_subquery = cumulative_posts_subquery.subquery() # The post viewers postViewers = aliased(ViewPost) viewedPosts = aliased(Post) post_viewers_subquery = discussion.db.query(intervals_table.c.interval_id, func.count(distinct(postViewers.actor_id)).label('UNRELIABLE_count_post_viewers') ) post_viewers_subquery = post_viewers_subquery.outerjoin(postViewers, and_(postViewers.creation_date >= intervals_table.c.interval_start, postViewers.creation_date < intervals_table.c.interval_end)).\ join(viewedPosts, and_(postViewers.post_id == viewedPosts.id, viewedPosts.discussion_id == discussion.id)) post_viewers_subquery = post_viewers_subquery.group_by(intervals_table.c.interval_id) post_viewers_subquery = post_viewers_subquery.subquery() # The visitors firstTimeVisitorAgent = aliased(AgentStatusInDiscussion) visitors_subquery = discussion.db.query(intervals_table.c.interval_id, func.count(firstTimeVisitorAgent.id).label('count_first_time_logged_in_visitors'), # func.DB.DBA.BAG_AGG(firstTimeVisitorAgent.id).label('first_time_visitors') ) visitors_subquery = visitors_subquery.outerjoin(firstTimeVisitorAgent, and_(firstTimeVisitorAgent.first_visit >= intervals_table.c.interval_start, firstTimeVisitorAgent.first_visit < intervals_table.c.interval_end, firstTimeVisitorAgent.discussion_id == discussion.id)) visitors_subquery = visitors_subquery.group_by(intervals_table.c.interval_id) visitors_subquery = visitors_subquery.subquery() # The cumulative visitors cumulativeVisitorAgent = aliased(AgentStatusInDiscussion) cumulative_visitors_query = discussion.db.query(intervals_table.c.interval_id, func.count(distinct(cumulativeVisitorAgent.id)).label('count_cumulative_logged_in_visitors'), # func.DB.DBA.BAG_AGG(cumulativeVisitorAgent.id).label('first_time_visitors') ) cumulative_visitors_query = cumulative_visitors_query.outerjoin(cumulativeVisitorAgent, and_(cumulativeVisitorAgent.first_visit < intervals_table.c.interval_end, cumulativeVisitorAgent.discussion_id == discussion.id)) cumulative_visitors_query = cumulative_visitors_query.group_by(intervals_table.c.interval_id) cumulative_visitors_subquery = cumulative_visitors_query.subquery() # query = cumulative_visitors_query # The members (can go up and down...) Assumes that first_subscribed is available commented_out = """ first_subscribed isn't yet filled in by assembl memberAgentStatus = aliased(AgentStatusInDiscussion) members_subquery = discussion.db.query(intervals_table.c.interval_id, func.count(memberAgentStatus.id).label('count_approximate_members') ) members_subquery = members_subquery.outerjoin(memberAgentStatus, ((memberAgentStatus.last_unsubscribed >= intervals_table.c.interval_end) | (memberAgentStatus.last_unsubscribed.is_(None))) & ((memberAgentStatus.first_subscribed < intervals_table.c.interval_end) | (memberAgentStatus.first_subscribed.is_(None))) & (memberAgentStatus.discussion_id==discussion.id)) members_subquery = members_subquery.group_by(intervals_table.c.interval_id) query = members_subquery members_subquery = members_subquery.subquery() """ subscribersAgentStatus = aliased(AgentStatusInDiscussion) subscribers_query = discussion.db.query(intervals_table.c.interval_id, func.sum( case([ (subscribersAgentStatus.last_visit == None, 0), (and_(subscribersAgentStatus.last_visit < intervals_table.c.interval_end, subscribersAgentStatus.last_visit >= intervals_table.c.interval_start), 1) ], else_=0) ).label('retention_count_last_visit_in_period'), func.sum( case([ (subscribersAgentStatus.first_visit == None, 0), (and_(subscribersAgentStatus.first_visit < intervals_table.c.interval_end, subscribersAgentStatus.first_visit >= intervals_table.c.interval_start), 1) ], else_=0) ).label('recruitment_count_first_visit_in_period'), func.sum( case([ (subscribersAgentStatus.first_subscribed == None, 0), (and_(subscribersAgentStatus.first_subscribed < intervals_table.c.interval_end, subscribersAgentStatus.first_subscribed >= intervals_table.c.interval_start), 1) ], else_=0) ).label('UNRELIABLE_recruitment_count_first_subscribed_in_period'), func.sum( case([ (subscribersAgentStatus.last_unsubscribed == None, 0), (and_(subscribersAgentStatus.last_unsubscribed < intervals_table.c.interval_end, subscribersAgentStatus.last_unsubscribed >= intervals_table.c.interval_start), 1) ], else_=0) ).label('UNRELIABLE_retention_count_first_subscribed_in_period'), ) subscribers_query = subscribers_query.outerjoin(subscribersAgentStatus, subscribersAgentStatus.discussion_id==discussion.id) subscribers_query = subscribers_query.group_by(intervals_table.c.interval_id) subscribers_subquery = subscribers_query.subquery() #query = subscribers_query combined_query = discussion.db.query(intervals_table, post_subquery, cumulative_posts_subquery, post_viewers_subquery, visitors_subquery, cumulative_visitors_subquery, case([ (cumulative_posts_subquery.c.count_cumulative_post_authors == 0, None), (cumulative_posts_subquery.c.count_cumulative_post_authors != 0, (cast(post_subquery.c.count_post_authors, Float) / cast(cumulative_posts_subquery.c.count_cumulative_post_authors, Float))) ]).label('fraction_cumulative_authors_who_posted_in_period'), case([ (cumulative_visitors_subquery.c.count_cumulative_logged_in_visitors == 0, None), (cumulative_visitors_subquery.c.count_cumulative_logged_in_visitors != 0, (cast(post_subquery.c.count_post_authors, Float) / cast(cumulative_visitors_subquery.c.count_cumulative_logged_in_visitors, Float))) ]).label('fraction_cumulative_logged_in_visitors_who_posted_in_period'), subscribers_subquery, ) combined_query = combined_query.join(post_subquery, post_subquery.c.interval_id == intervals_table.c.interval_id) combined_query = combined_query.join(post_viewers_subquery, post_viewers_subquery.c.interval_id == intervals_table.c.interval_id) combined_query = combined_query.join(visitors_subquery, visitors_subquery.c.interval_id == intervals_table.c.interval_id) combined_query = combined_query.join(cumulative_visitors_subquery, cumulative_visitors_subquery.c.interval_id == intervals_table.c.interval_id) # combined_query = combined_query.join(members_subquery, members_subquery.c.interval_id==intervals_table.c.interval_id) combined_query = combined_query.join(subscribers_subquery, subscribers_subquery.c.interval_id==intervals_table.c.interval_id) combined_query = combined_query.join(cumulative_posts_subquery, cumulative_posts_subquery.c.interval_id == intervals_table.c.interval_id) query = combined_query query = query.order_by(intervals_table.c.interval_id) results = query.all() # pprint.pprint(results) # end of transaction intervals_table.drop() if not (request.GET.get('format', None) == 'csv' or request.accept == 'text/csv'): # json default from assembl.lib.json import DateJSONEncoder return Response(json.dumps(results, cls=DateJSONEncoder), content_type='application/json') fieldnames = [ "interval_id", "interval_start", "interval_end", "count_first_time_logged_in_visitors", "count_cumulative_logged_in_visitors", "fraction_cumulative_logged_in_visitors_who_posted_in_period", "count_post_authors", "count_cumulative_post_authors", "fraction_cumulative_authors_who_posted_in_period", "count_posts", "count_cumulative_posts", "recruitment_count_first_visit_in_period", "UNRELIABLE_recruitment_count_first_subscribed_in_period", "retention_count_last_visit_in_period", "UNRELIABLE_retention_count_first_subscribed_in_period", "UNRELIABLE_count_post_viewers", ] # otherwise assume csv return csv_response(fieldnames, [r._asdict() for r in results])
def assignRole(self, roleId, toRoleId): """ @see: IRbacService.assignRole """ child, parent = aliased(RoleNode), aliased(RoleNode) # check if the parent is in child subtree sql = self.session().query(child) sql = sql.join(parent, and_(child.left < parent.left, child.right > parent.right)) sql = sql.filter(and_(child.role == toRoleId, parent.role == roleId)) if sql.count() > 0: return False # check if has parent root sql = self.session().query(parent.id) sql = sql.join(child, and_(child.left > parent.left, child.right < parent.right)) sql = sql.filter(child.role == roleId) parentCnt = sql.count() # get child roleNode childNode = self.session().query(RoleNode).filter(RoleNode.role == roleId).first() treeWidth = childNode.right - childNode.left + 1 id = childNode.id # get the number of duplicates for parent sql = self.session().query(RoleNode).filter(RoleNode.role == toRoleId) treeCnt = sql.count() right, count = 0, 0 sql = self.session().query(RoleNode.right).filter(RoleNode.role == toRoleId).order_by(RoleNode.right.desc()) for (left,) in sql.all(): if count == 0: # last interval gap = treeWidth * treeCnt sql = self.session().query(RoleNode).filter(RoleNode.left >= left) sql.update({RoleNode.left: RoleNode.left + gap}, False) sql = self.session().query(RoleNode).filter(RoleNode.right >= left) sql.update({RoleNode.right: RoleNode.right + gap}, False) self.session().flush() # TODO: check the impact of removing: result: not working self.session().commit() # get child roleNode childNode = self.session().query(RoleNode).get(id) gap = left - childNode.left # insert insert = InsertFromSelect( tableFor(RoleNode), "fk_role_id, lft, rgt", select([RoleNode.role, RoleNode.left + gap, RoleNode.right + gap]).where( and_(RoleNode.left >= childNode.left, RoleNode.right <= childNode.right) ), ) self.session().execute(insert) right = left count = count + 1 continue gap = (treeCnt - count) * treeWidth sql = self.session().query(RoleNode).filter(and_(RoleNode.left >= left, RoleNode.left < right)) sql.update({RoleNode.left: RoleNode.left + gap}, False) sql = self.session().query(RoleNode).filter(and_(RoleNode.right >= left, RoleNode.right < right)) sql.update({RoleNode.right: RoleNode.right + gap}, False) self.session().flush() # TODO: check the impact of removing: result: not working self.session().commit() # get child roleNode sql = self.session().query(RoleNode).get(id) gap = gap + left - childNode.left # insert # TODO: for sqlite is nedeed: INSERT INTO t1 (columns) SELECT * FROM t1 # change how is specified the list of columns insert = InsertFromSelect( tableFor(RoleNode), "fk_role_id, lft, rgt", select([RoleNode.role, RoleNode.left + gap, RoleNode.right + gap]).where( and_(RoleNode.left >= childNode.left, RoleNode.right <= childNode.right) ), ) self.session().execute(insert) right = left count = count + 1 # if has root parent, delete from it if parentCnt == 1: # get child roleNode sql = self.session().query(RoleNode).get(id) left = childNode.left right = childNode.right # delete child subtree from root sql = self.session().query(RoleNode).filter(and_(RoleNode.left >= left, RoleNode.right <= right)) sql.delete() # update lft sql = self.session().query(RoleNode).filter(RoleNode.left > left) sql.update({RoleNode.left: RoleNode.left - treeWidth}, False) # update rgt sql = self.session().query(RoleNode).filter(RoleNode.right > right) sql.update({RoleNode.right: RoleNode.right - treeWidth}, False) return True
from sqlalchemy.sql.expression import func, or_ from sqlalchemy.sql.functions import current_timestamp from sqlalchemy.sql.operators import desc_op from superdesk.collaborator.meta.collaborator import CollaboratorMapped from superdesk.person.meta.person import PersonMapped from superdesk.person_icon.api.person_icon import IPersonIconService from superdesk.post.api.post import IPostService, Post, QPostUnpublished from superdesk.post.meta.type import PostTypeMapped from livedesk.impl.blog_collaborator_group import updateLastAccessOn from superdesk.source.meta.source import SourceMapped from superdesk.verification.meta.verification import PostVerificationMapped from superdesk.verification.meta.status import VerificationStatusMapped # -------------------------------------------------------------------- UserPerson = aliased(PersonMapped) @injected @setup(IBlogPostService, name='blogPostService') class BlogPostServiceAlchemy(SessionSupport, IBlogPostService): ''' Implementation for @see: IBlogPostService ''' postService = IPostService; wire.entity('postService') personIconService = IPersonIconService; wire.entity('personIconService') internal_source_type = 'internal' def __init__(self): ''' Construct the blog post service.
def _create_list(tab, kw, date=None, pilot=None, club=None, airport=None, pinned=None, filter=None, columns=None): pilot_alias = aliased(User, name='pilot') owner_alias = aliased(User, name='owner') subq = db.session \ .query(FlightComment.flight_id, func.count('*').label('count')) \ .group_by(FlightComment.flight_id).subquery() flights = db.session.query(Flight, subq.c.count) \ .join(Flight.igc_file) \ .options(contains_eager(Flight.igc_file)) \ .join(owner_alias, IGCFile.owner) \ .options(contains_eager(Flight.igc_file, IGCFile.owner, alias=owner_alias)) \ .outerjoin(pilot_alias, Flight.pilot) \ .options(contains_eager(Flight.pilot, alias=pilot_alias)) \ .options(joinedload(Flight.co_pilot)) \ .outerjoin(Flight.club) \ .options(contains_eager(Flight.club)) \ .outerjoin(Flight.takeoff_airport) \ .options(contains_eager(Flight.takeoff_airport)) \ .outerjoin(Flight.model) \ .options(contains_eager(Flight.model)) \ .outerjoin((subq, Flight.comments)) if date: flights = flights.filter(Flight.date_local == date) if pilot: flights = flights.filter( or_(Flight.pilot == pilot, Flight.co_pilot == pilot)) if club: flights = flights.filter(Flight.club == club) if airport: flights = flights.filter(Flight.takeoff_airport == airport) if pinned: flights = flights.filter(Flight.id.in_(pinned)) if filter is not None: flights = flights.filter(filter) if request.is_xhr: if not columns: columns = { 0: (Flight, 'date_local'), 1: (Flight, 'index_score'), 2: (pilot_alias, 'name'), 3: (Flight, 'olc_classic_distance'), 4: (Airport, 'name'), 5: (Club, 'name'), 6: (AircraftModel, 'name'), 7: (Flight, 'takeoff_time'), 8: (Flight, 'id'), 9: (Flight, 'num_comments'), } flights, response_dict = GetDatatableRecords(kw, flights, columns) aaData = [] for flight, num_comments in flights: aaData.append( dict(takeoff_time=flight.takeoff_time.strftime('%H:%M'), landing_time=flight.landing_time.strftime('%H:%M'), date=flight.date_local.strftime('%d.%m.%Y'), date_formatted=format_date(flight.date_local), index_score=format_decimal(flight.index_score, format='0'), olc_classic_distance=flight.olc_classic_distance, pilot_id=flight.pilot_id, pilot=flight.pilot and flight.pilot.name, pilot_name=flight.pilot_name, co_pilot_id=flight.co_pilot_id, co_pilot=flight.co_pilot and flight.co_pilot.name, co_pilot_name=flight.co_pilot_name, club_id=flight.club_id, club=flight.club and truncate(flight.club.name, 25), owner=flight.igc_file.owner.name, takeoff_airport=flight.takeoff_airport and flight.takeoff_airport.name, takeoff_airport_id=flight.takeoff_airport and flight.takeoff_airport.id, takeoff_airport_country_code=flight.takeoff_airport and flight.takeoff_airport.country_code.lower(), takeoff_airport_country_name=flight.takeoff_airport and country_name(flight.takeoff_airport.country_code), aircraft=(flight.model and flight.model.name) or (flight.igc_file.model and '[' + flight.igc_file.model + ']'), aircraft_reg=flight.registration or flight.igc_file.registration or "Unknown", flight_id=flight.id, num_comments=num_comments)) return jsonify(aaData=aaData, **response_dict) else: if not date: flights = flights.order_by(Flight.date_local.desc()) flights_count = flights.count() if flights_count > int( current_app.config.get('SKYLINES_LISTS_SERVER_SIDE', 250)): limit = int( current_app.config.get('SKYLINES_LISTS_DISPLAY_LENGTH', 50)) else: limit = int( current_app.config.get('SKYLINES_LISTS_SERVER_SIDE', 250)) flights = flights.order_by(Flight.index_score.desc()) flights = flights.limit(limit) return render_template('flights/list.jinja', tab=tab, date=date, pilot=pilot, club=club, airport=airport, flights=flights, flights_count=flights_count)
def set_recent_outings(waypoint, lang): """Set last 10 outings on routes associated to the given waypoint. """ t_outing_route = aliased(Association, name='a1') t_route_wp = aliased(Association, name='a2') t_route = aliased(Document, name='r') with_query_waypoints = _get_select_children(waypoint) recent_outings = DBSession.query(Outing). \ filter(Outing.redirects_to.is_(None)). \ join( t_outing_route, Outing.document_id == t_outing_route.child_document_id). \ join( t_route, and_( t_outing_route.parent_document_id == t_route.document_id, t_route.type == ROUTE_TYPE)). \ join( t_route_wp, t_route_wp.child_document_id == t_route.document_id). \ join( with_query_waypoints, with_query_waypoints.c.document_id == t_route_wp.parent_document_id ). \ options(load_only( Outing.document_id, Outing.activities, Outing.date_start, Outing.date_end, Outing.version, Outing.protected)). \ options(joinedload(Outing.locales).load_only( DocumentLocale.lang, DocumentLocale.title, DocumentLocale.version)). \ order_by(Outing.date_end.desc()). \ limit(NUM_RECENT_OUTINGS). \ all() set_author(recent_outings, None) if lang is not None: set_best_locale(recent_outings, lang) total = DBSession.query(Outing.document_id). \ filter(Outing.redirects_to.is_(None)). \ join( t_outing_route, Outing.document_id == t_outing_route.child_document_id). \ join( t_route, and_( t_outing_route.parent_document_id == t_route.document_id, t_route.type == ROUTE_TYPE)). \ join( t_route_wp, t_route_wp.child_document_id == t_route.document_id). \ join( with_query_waypoints, with_query_waypoints.c.document_id == t_route_wp.parent_document_id ). \ count() waypoint.associations['recent_outings'] = { 'total': total, 'outings': [ to_json_dict(outing, schema_association_outing) for outing in recent_outings ] }
def _create_list(tab, kw, date=None, pilot=None, club=None, airport=None, pinned=None, filter=None, columns=None): pilot_alias = aliased(User, name='pilot') owner_alias = aliased(User, name='owner') subq = db.session \ .query(FlightComment.flight_id, func.count('*').label('count')) \ .group_by(FlightComment.flight_id).subquery() flights = db.session.query(Flight, subq.c.count) \ .join(Flight.igc_file) \ .options(contains_eager(Flight.igc_file)) \ .join(owner_alias, IGCFile.owner) \ .options(contains_eager(Flight.igc_file, IGCFile.owner, alias=owner_alias)) \ .outerjoin(pilot_alias, Flight.pilot) \ .options(contains_eager(Flight.pilot, alias=pilot_alias)) \ .options(joinedload(Flight.co_pilot)) \ .outerjoin(Flight.club) \ .options(contains_eager(Flight.club)) \ .outerjoin(Flight.takeoff_airport) \ .options(contains_eager(Flight.takeoff_airport)) \ .outerjoin(Flight.model) \ .options(contains_eager(Flight.model)) \ .outerjoin((subq, Flight.comments)) if date: flights = flights.filter(Flight.date_local == date) if pilot: flights = flights.filter(or_(Flight.pilot == pilot, Flight.co_pilot == pilot)) if club: flights = flights.filter(Flight.club == club) if airport: flights = flights.filter(Flight.takeoff_airport == airport) if pinned: flights = flights.filter(Flight.id.in_(pinned)) if filter is not None: flights = flights.filter(filter) if request.is_xhr: if not columns: columns = { 0: (Flight, 'date_local'), 1: (Flight, 'index_score'), 2: (pilot_alias, 'name'), 3: (Flight, 'olc_classic_distance'), 4: (Airport, 'name'), 5: (Club, 'name'), 6: (AircraftModel, 'name'), 7: (Flight, 'takeoff_time'), 8: (Flight, 'id'), 9: (Flight, 'num_comments'), } flights, response_dict = GetDatatableRecords(kw, flights, columns) aaData = [] for flight, num_comments in flights: aaData.append(dict(takeoff_time=flight.takeoff_time.strftime('%H:%M'), landing_time=flight.landing_time.strftime('%H:%M'), date=flight.date_local.strftime('%d.%m.%Y'), date_formatted=format_date(flight.date_local), index_score=format_decimal(flight.index_score, format='0'), olc_classic_distance=flight.olc_classic_distance, pilot_id=flight.pilot_id, pilot=flight.pilot and flight.pilot.name, pilot_name=flight.pilot_name, co_pilot_id=flight.co_pilot_id, co_pilot=flight.co_pilot and flight.co_pilot.name, co_pilot_name=flight.co_pilot_name, club_id=flight.club_id, club=flight.club and truncate(flight.club.name, 25), owner=flight.igc_file.owner.name, takeoff_airport=flight.takeoff_airport and flight.takeoff_airport.name, takeoff_airport_id=flight.takeoff_airport and flight.takeoff_airport.id, takeoff_airport_country_code=flight.takeoff_airport and flight.takeoff_airport.country_code.lower(), takeoff_airport_country_name=flight.takeoff_airport and country_name(flight.takeoff_airport.country_code), aircraft=(flight.model and flight.model.name) or (flight.igc_file.model and '[' + flight.igc_file.model + ']'), aircraft_reg=flight.registration or flight.igc_file.registration or "Unknown", flight_id=flight.id, num_comments=num_comments)) return jsonify(aaData=aaData, **response_dict) else: if not date: flights = flights.order_by(Flight.date_local.desc()) flights_count = flights.count() if flights_count > int(current_app.config.get('SKYLINES_LISTS_SERVER_SIDE', 250)): limit = int(current_app.config.get('SKYLINES_LISTS_DISPLAY_LENGTH', 50)) else: limit = int(current_app.config.get('SKYLINES_LISTS_SERVER_SIDE', 250)) flights = flights.order_by(Flight.index_score.desc()) flights = flights.limit(limit) return render_template('flights/list.jinja', tab=tab, date=date, pilot=pilot, club=club, airport=airport, flights=flights, flights_count=flights_count)