def threads(namespace_id, subject, from_addr, to_addr, cc_addr, bcc_addr, any_email, thread_public_id, started_before, started_after, last_message_before, last_message_after, filename, in_, unread, starred, limit, offset, view, db_session): if view == 'count': query = db_session.query(func.count(distinct(Thread.id))) elif view == 'ids': query = db_session.query(Thread.public_id) else: query = db_session.query(Thread) filters = _threads_filters(namespace_id, thread_public_id, started_before, started_after, last_message_before, last_message_after, subject) query = _threads_join_category(query, namespace_id, in_) query = query.filter(*filters) for subquery in _threads_subqueries(namespace_id, from_addr, to_addr, cc_addr, bcc_addr, any_email, filename, unread, starred, db_session): query = query.filter(Thread.id.in_(subquery)) if view == 'count': return {"count": query.one()[0]} # Eager-load some objects in order to make constructing API # representations faster. if view != 'ids': expand = (view == 'expanded') query = query.options(*Thread.api_loading_options(expand)) query = query.order_by(desc(Thread.recentdate)).limit(limit) if offset: query = query.offset(offset) if view == 'ids': return [x[0] for x in query.all()] return query.all()
def format_transactions_after_pointer(namespace, pointer, db_session, result_limit, exclude_types=None, include_types=None, exclude_folders=True, expand=False): """ Return a pair (deltas, new_pointer), where deltas is a list of change events, represented as dictionaries: { "object": <API object type, e.g. "thread">, "event": <"create", "modify", or "delete>, "attributes": <API representation of the object for insert/update events> "cursor": <public_id of the transaction> } and new_pointer is the integer id of the last included transaction Arguments --------- namespace_id: int Id of the namespace for which to get changes. pointer: int Process transactions starting after this id. db_session: new_session database session result_limit: int Maximum number of results to return. (Because we may roll up multiple changes to the same object, fewer results can be returned.) format_transaction_fn: function pointer Function that defines how to format the transactions. exclude_types: list, optional If given, don't include transactions for these types of objects. """ exclude_types = set(exclude_types) if exclude_types else set() # Begin backwards-compatibility shim -- suppress new object types for now, # because clients may not be able to deal with them. exclude_types.add('account') if exclude_folders is True: exclude_types.update(('folder', 'label')) # End backwards-compatibility shim. last_trx = _get_last_trx_id_for_namespace(namespace.id, db_session) if last_trx == pointer: return ([], pointer) while True: # deleted_at condition included to allow this query to be satisfied via # the legacy index on (namespace_id, deleted_at) for performance. # Also need to explicitly specify the index hint because the query # planner is dumb as nails and otherwise would make this super slow for # some values of namespace_id and pointer. # TODO(emfree): Remove this hack and ensure that the right index (on # namespace_id only) exists. transactions = db_session.query(Transaction). \ filter( Transaction.id > pointer, Transaction.namespace_id == namespace.id, Transaction.deleted_at.is_(None)). \ with_hint(Transaction, 'USE INDEX (namespace_id_deleted_at)') if exclude_types is not None: transactions = transactions.filter( ~Transaction.object_type.in_(exclude_types)) if include_types is not None: transactions = transactions.filter( Transaction.object_type.in_(include_types)) transactions = transactions. \ order_by(asc(Transaction.id)).limit(result_limit).all() if not transactions: return ([], pointer) results = [] # Group deltas by object type. trxs_by_obj_type = collections.defaultdict(list) for trx in transactions: trxs_by_obj_type[trx.object_type].append(trx) for obj_type, trxs in trxs_by_obj_type.items(): # Build a dictionary mapping pairs (record_id, command) to # transaction. If successive modifies for a given record id appear # in the list of transactions, this will only keep the latest # one (which is what we want). latest_trxs = {(trx.record_id, trx.command): trx for trx in sorted(trxs, key=lambda t: t.id) }.values() # Load all referenced not-deleted objects. ids_to_query = [ trx.record_id for trx in latest_trxs if trx.command != 'delete' ] object_cls = transaction_objects()[obj_type] query = db_session.query(object_cls).filter( object_cls.id.in_(ids_to_query), object_cls.namespace_id == namespace.id) if object_cls == Thread: query = query.options(*Thread.api_loading_options(expand)) elif object_cls == Message: query = query.options(*Message.api_loading_options(expand)) objects = {obj.id: obj for obj in query} for trx in latest_trxs: delta = { 'object': trx.object_type, 'event': EVENT_NAME_FOR_COMMAND[trx.command], 'id': trx.object_public_id, 'cursor': trx.public_id } if trx.command != 'delete': obj = objects.get(trx.record_id) if obj is None: continue repr_ = encode(obj, namespace_public_id=namespace.public_id, expand=expand) delta['attributes'] = repr_ results.append((trx.id, delta)) if results: # Sort deltas by id of the underlying transactions. results.sort() deltas = [d for _, d in results] return (deltas, results[-1][0]) else: # It's possible that none of the referenced objects exist any more, # meaning the result list is empty. In that case, keep traversing # the log until we get actual results or reach the end. pointer = transactions[-1].id
def threads( namespace_id, subject, from_addr, to_addr, cc_addr, bcc_addr, any_email, message_id_header, thread_public_id, started_before, started_after, last_message_before, last_message_after, filename, in_, unread, starred, limit, offset, view, db_session, ): if view == "count": query = db_session.query(func.count(Thread.id)) elif view == "ids": query = db_session.query(Thread.public_id) else: query = db_session.query(Thread) filters = [Thread.namespace_id == namespace_id, Thread.deleted_at == None] if thread_public_id is not None: filters.append(Thread.public_id == thread_public_id) if started_before is not None: filters.append(Thread.subjectdate < started_before) if started_after is not None: filters.append(Thread.subjectdate > started_after) if last_message_before is not None: filters.append(Thread.recentdate < last_message_before) if last_message_after is not None: filters.append(Thread.recentdate > last_message_after) if subject is not None: filters.append(Thread.subject == subject) query = query.filter(*filters) if from_addr is not None: from_query = contact_subquery(db_session, namespace_id, from_addr, "from_addr") query = query.filter(Thread.id.in_(from_query)) if to_addr is not None: to_query = contact_subquery(db_session, namespace_id, to_addr, "to_addr") query = query.filter(Thread.id.in_(to_query)) if cc_addr is not None: cc_query = contact_subquery(db_session, namespace_id, cc_addr, "cc_addr") query = query.filter(Thread.id.in_(cc_query)) if bcc_addr is not None: bcc_query = contact_subquery(db_session, namespace_id, bcc_addr, "bcc_addr") query = query.filter(Thread.id.in_(bcc_query)) if any_email is not None: any_contact_query = (db_session.query( Message.thread_id).join(MessageContactAssociation).join( Contact, MessageContactAssociation.contact_id == Contact.id).filter( Contact.email_address.in_(any_email), Contact.namespace_id == namespace_id, ).subquery()) query = query.filter(Thread.id.in_(any_contact_query)) if message_id_header is not None: message_id_query = db_session.query(Message.thread_id).filter( Message.message_id_header == message_id_header) query = query.filter(Thread.id.in_(message_id_query)) if filename is not None: files_query = (db_session.query( Message.thread_id).join(Part).join(Block).filter( Block.filename == filename, Block.namespace_id == namespace_id).subquery()) query = query.filter(Thread.id.in_(files_query)) if in_ is not None: category_filters = [Category.name == in_, Category.display_name == in_] try: valid_public_id(in_) category_filters.append(Category.public_id == in_) except InputError: pass category_query = (db_session.query( Message.thread_id).prefix_with("STRAIGHT_JOIN").join( Message.messagecategories).join( MessageCategory.category).filter( Category.namespace_id == namespace_id, or_(*category_filters)).subquery()) query = query.filter(Thread.id.in_(category_query)) if unread is not None: read = not unread unread_query = (db_session.query(Message.thread_id).filter( Message.namespace_id == namespace_id, Message.is_read == read).subquery()) query = query.filter(Thread.id.in_(unread_query)) if starred is not None: starred_query = (db_session.query(Message.thread_id).filter( Message.namespace_id == namespace_id, Message.is_starred == starred).subquery()) query = query.filter(Thread.id.in_(starred_query)) if view == "count": return {"count": query.one()[0]} # Eager-load some objects in order to make constructing API # representations faster. if view != "ids": expand = view == "expanded" query = query.options(*Thread.api_loading_options(expand)) query = query.order_by(desc(Thread.recentdate)).limit(limit) if offset: query = query.offset(offset) if view == "ids": return [x[0] for x in query.all()] return query.all()
def format_transactions_after_pointer(namespace, pointer, db_session, result_limit, exclude_types=None, include_types=None, exclude_folders=True, exclude_metadata=True, exclude_account=True, expand=False): """ Return a pair (deltas, new_pointer), where deltas is a list of change events, represented as dictionaries: { "object": <API object type, e.g. "thread">, "event": <"create", "modify", or "delete>, "attributes": <API representation of the object for insert/update events> "cursor": <public_id of the transaction> } and new_pointer is the integer id of the last included transaction Arguments --------- namespace_id: int Id of the namespace for which to get changes. pointer: int Process transactions starting after this id. db_session: new_session database session result_limit: int Maximum number of results to return. (Because we may roll up multiple changes to the same object, fewer results can be returned.) format_transaction_fn: function pointer Function that defines how to format the transactions. exclude_types: list, optional If given, don't include transactions for these types of objects. """ exclude_types = set(exclude_types) if exclude_types else set() # Begin backwards-compatibility shim -- suppress new object types for now, # because clients may not be able to deal with them. if exclude_folders is True: exclude_types.update(('folder', 'label')) if exclude_account is True: exclude_types.add('account') # End backwards-compatibility shim. # Metadata is excluded by default, and can only be included by setting the # exclude_metadata flag to False. If listed in include_types, remove it. if exclude_metadata is True: exclude_types.add('metadata') if include_types is not None and 'metadata' in include_types: include_types.remove('metadata') last_trx = _get_last_trx_id_for_namespace(namespace.id, db_session) if last_trx == pointer: return ([], pointer) while True: # deleted_at condition included to allow this query to be satisfied via # the legacy index on (namespace_id, deleted_at) for performance. # Also need to explicitly specify the index hint because the query # planner is dumb as nails and otherwise would make this super slow for # some values of namespace_id and pointer. # TODO(emfree): Remove this hack and ensure that the right index (on # namespace_id only) exists. transactions = db_session.query(Transaction). \ filter( Transaction.id > pointer, Transaction.namespace_id == namespace.id, Transaction.deleted_at.is_(None)). \ with_hint(Transaction, 'USE INDEX (namespace_id_deleted_at)') if exclude_types is not None: transactions = transactions.filter( ~Transaction.object_type.in_(exclude_types)) if include_types is not None: transactions = transactions.filter( Transaction.object_type.in_(include_types)) transactions = transactions. \ order_by(asc(Transaction.id)).limit(result_limit).all() if not transactions: return ([], pointer) results = [] # Group deltas by object type. trxs_by_obj_type = collections.defaultdict(list) for trx in transactions: trxs_by_obj_type[trx.object_type].append(trx) for obj_type, trxs in trxs_by_obj_type.items(): # Build a dictionary mapping pairs (record_id, command) to # transaction. If successive modifies for a given record id appear # in the list of transactions, this will only keep the latest # one (which is what we want). latest_trxs = {(trx.record_id, trx.command): trx for trx in sorted(trxs, key=lambda t: t.id)}.values() # Load all referenced not-deleted objects. ids_to_query = [trx.record_id for trx in latest_trxs if trx.command != 'delete'] object_cls = transaction_objects()[obj_type] if object_cls == Account: # The base query for Account queries the /Namespace/ table # since the API-returned "`account`" is a `namespace` # under-the-hood. query = db_session.query(Namespace).join(Account).filter( Account.id.in_(ids_to_query), Namespace.id == namespace.id) # Key by /namespace.account_id/ -- # namespace.id may not be equal to account.id # and trx.record_id == account.id for `account` trxs. objects = {obj.account_id: obj for obj in query} else: query = db_session.query(object_cls).filter( object_cls.id.in_(ids_to_query), object_cls.namespace_id == namespace.id) if object_cls == Thread: query = query.options(*Thread.api_loading_options(expand)) elif object_cls == Message: query = query.options(*Message.api_loading_options(expand)) objects = {obj.id: obj for obj in query} for trx in latest_trxs: delta = { 'object': trx.object_type, 'event': EVENT_NAME_FOR_COMMAND[trx.command], 'id': trx.object_public_id, 'cursor': trx.public_id } if trx.command != 'delete': obj = objects.get(trx.record_id) if obj is None: continue repr_ = encode( obj, namespace_public_id=namespace.public_id, expand=expand) delta['attributes'] = repr_ results.append((trx.id, delta)) if results: # Sort deltas by id of the underlying transactions. results.sort() deltas = [d for _, d in results] return (deltas, results[-1][0]) else: # It's possible that none of the referenced objects exist any more, # meaning the result list is empty. In that case, keep traversing # the log until we get actual results or reach the end. pointer = transactions[-1].id
def format_transactions_after_pointer(namespace, pointer, db_session, result_limit, exclude_types=None, include_types=None, exclude_folders=True, exclude_metadata=True, exclude_account=True, expand=False, is_n1=False): """ Return a pair (deltas, new_pointer), where deltas is a list of change events, represented as dictionaries: { "object": <API object type, e.g. "thread">, "event": <"create", "modify", or "delete>, "attributes": <API representation of the object for insert/update events> "cursor": <public_id of the transaction> } and new_pointer is the integer id of the last included transaction Arguments --------- namespace_id: int Id of the namespace for which to get changes. pointer: int Process transactions starting after this id. db_session: new_session database session result_limit: int Maximum number of results to return. (Because we may roll up multiple changes to the same object, fewer results can be returned.) format_transaction_fn: function pointer Function that defines how to format the transactions. exclude_types: list, optional If given, don't include transactions for these types of objects. """ exclude_types = set(exclude_types) if exclude_types else set() # Begin backwards-compatibility shim -- suppress new object types for now, # because clients may not be able to deal with them. if exclude_folders is True: exclude_types.update(('folder', 'label')) if exclude_account is True: exclude_types.add('account') # End backwards-compatibility shim. # Metadata is excluded by default, and can only be included by setting the # exclude_metadata flag to False. If listed in include_types, remove it. if exclude_metadata is True: exclude_types.add('metadata') if include_types is not None and 'metadata' in include_types: include_types.remove('metadata') last_trx = _get_last_trx_id_for_namespace(namespace.id, db_session) if last_trx == pointer: return ([], pointer) while True: transactions = db_session.query(Transaction). \ filter( Transaction.id > pointer, Transaction.namespace_id == namespace.id) if exclude_types is not None: transactions = transactions.filter( ~Transaction.object_type.in_(exclude_types)) if include_types is not None: transactions = transactions.filter( Transaction.object_type.in_(include_types)) transactions = transactions. \ order_by(asc(Transaction.id)).limit(result_limit).all() if not transactions: return ([], pointer) results = [] # Group deltas by object type. trxs_by_obj_type = collections.defaultdict(list) for trx in transactions: trxs_by_obj_type[trx.object_type].append(trx) for obj_type, trxs in trxs_by_obj_type.items(): # Build a dictionary mapping pairs (record_id, command) to # transaction. If successive modifies for a given record id appear # in the list of transactions, this will only keep the latest # one (which is what we want). latest_trxs = {(trx.record_id, trx.command): trx for trx in sorted(trxs, key=lambda t: t.id) }.values() # Load all referenced not-deleted objects. ids_to_query = [ trx.record_id for trx in latest_trxs if trx.command != 'delete' ] object_cls = transaction_objects()[obj_type] if object_cls == Account: # The base query for Account queries the /Namespace/ table # since the API-returned "`account`" is a `namespace` # under-the-hood. query = db_session.query(Namespace).join(Account).filter( Account.id.in_(ids_to_query), Namespace.id == namespace.id) # Key by /namespace.account_id/ -- # namespace.id may not be equal to account.id # and trx.record_id == account.id for `account` trxs. objects = {obj.account_id: obj for obj in query} else: query = db_session.query(object_cls).filter( object_cls.id.in_(ids_to_query), object_cls.namespace_id == namespace.id) if object_cls == Thread: query = query.options(*Thread.api_loading_options(expand)) elif object_cls == Message: query = query.options(*Message.api_loading_options(expand)) objects = {obj.id: obj for obj in query} for trx in latest_trxs: delta = { 'object': trx.object_type, 'event': EVENT_NAME_FOR_COMMAND[trx.command], 'id': trx.object_public_id, 'cursor': trx.public_id } if trx.command != 'delete': obj = objects.get(trx.record_id) if obj is None: continue repr_ = encode(obj, namespace_public_id=namespace.public_id, expand=expand, is_n1=is_n1) delta['attributes'] = repr_ results.append((trx.id, delta)) if results: # Sort deltas by id of the underlying transactions. results.sort() deltas = [d for _, d in results] return (deltas, results[-1][0]) else: # It's possible that none of the referenced objects exist any more, # meaning the result list is empty. In that case, keep traversing # the log until we get actual results or reach the end. pointer = transactions[-1].id
def threads(namespace_id, subject, from_addr, to_addr, cc_addr, bcc_addr, any_email, thread_public_id, started_before, started_after, last_message_before, last_message_after, filename, in_, unread, starred, limit, offset, view, db_session): if view == 'count': query = db_session.query(func.count(Thread.id)) elif view == 'ids': query = db_session.query(Thread.public_id) else: query = db_session.query(Thread) filters = [Thread.namespace_id == namespace_id] if thread_public_id is not None: filters.append(Thread.public_id == thread_public_id) if started_before is not None: filters.append(Thread.subjectdate < started_before) if started_after is not None: filters.append(Thread.subjectdate > started_after) if last_message_before is not None: filters.append(Thread.recentdate < last_message_before) if last_message_after is not None: filters.append(Thread.recentdate > last_message_after) if subject is not None: filters.append(Thread.subject == subject) query = query.filter(*filters) if from_addr is not None: from_query = db_session.query(Message.thread_id). \ join(MessageContactAssociation).join(Contact).filter( Contact.email_address == from_addr, Contact.namespace_id == namespace_id, MessageContactAssociation.field == 'from_addr').subquery() query = query.filter(Thread.id.in_(from_query)) if to_addr is not None: to_query = db_session.query(Message.thread_id). \ join(MessageContactAssociation).join(Contact).filter( Contact.email_address == to_addr, Contact.namespace_id == namespace_id, MessageContactAssociation.field == 'to_addr').subquery() query = query.filter(Thread.id.in_(to_query)) if cc_addr is not None: cc_query = db_session.query(Message.thread_id). \ join(MessageContactAssociation).join(Contact).filter( Contact.email_address == cc_addr, Contact.namespace_id == namespace_id, MessageContactAssociation.field == 'cc_addr').subquery() query = query.filter(Thread.id.in_(cc_query)) if bcc_addr is not None: bcc_query = db_session.query(Message.thread_id). \ join(MessageContactAssociation).join(Contact).filter( Contact.email_address == bcc_addr, Contact.namespace_id == namespace_id, MessageContactAssociation.field == 'bcc_addr').subquery() query = query.filter(Thread.id.in_(bcc_query)) if any_email is not None: any_contact_query = db_session.query(Message.thread_id). \ join(MessageContactAssociation).join(Contact). \ filter(Contact.email_address == any_email, Contact.namespace_id == namespace_id).subquery() query = query.filter(Thread.id.in_(any_contact_query)) if filename is not None: files_query = db_session.query(Message.thread_id). \ join(Part).join(Block). \ filter(Block.filename == filename, Block.namespace_id == namespace_id). \ subquery() query = query.filter(Thread.id.in_(files_query)) if in_ is not None: category_filters = [Category.name == in_, Category.display_name == in_] try: valid_public_id(in_) category_filters.append(Category.public_id == in_) except InputError: pass category_query = db_session.query(Message.thread_id). \ join(MessageCategory).join(Category). \ filter(Category.namespace_id == namespace_id, or_(*category_filters)).subquery() query = query.filter(Thread.id.in_(category_query)) if unread is not None: read = not unread unread_query = db_session.query(Message.thread_id).filter( Message.namespace_id == namespace_id, Message.is_read == read).subquery() query = query.filter(Thread.id.in_(unread_query)) if starred is not None: starred_query = db_session.query(Message.thread_id).filter( Message.namespace_id == namespace_id, Message.is_starred == starred).subquery() query = query.filter(Thread.id.in_(starred_query)) if view == 'count': return {"count": query.one()[0]} # Eager-load some objects in order to make constructing API # representations faster. if view != 'ids': expand = (view == 'expanded') query = query.options(*Thread.api_loading_options(expand)) query = query.order_by(desc(Thread.recentdate)).limit(limit) if offset: query = query.offset(offset) if view == 'ids': return [x[0] for x in query.all()] return query.all()
def threads(namespace_id, subject, from_addr, to_addr, cc_addr, bcc_addr, any_email, thread_public_id, started_before, started_after, last_message_before, last_message_after, filename, in_, unread, starred, limit, offset, view, db_session): if view == 'count': query = db_session.query(func.count(Thread.id)) elif view == 'ids': query = db_session.query(Thread.public_id) else: query = db_session.query(Thread) filters = [Thread.namespace_id == namespace_id] if thread_public_id is not None: filters.append(Thread.public_id == thread_public_id) if started_before is not None: filters.append(Thread.subjectdate < started_before) if started_after is not None: filters.append(Thread.subjectdate > started_after) if last_message_before is not None: filters.append(Thread.recentdate < last_message_before) if last_message_after is not None: filters.append(Thread.recentdate > last_message_after) if subject is not None: filters.append(Thread.subject == subject) query = query.filter(*filters) if from_addr is not None: from_query = db_session.query(Message.thread_id). \ join(MessageContactAssociation).join(Contact).filter( Contact.email_address == from_addr, Contact.namespace_id == namespace_id, MessageContactAssociation.field == 'from_addr').subquery() query = query.filter(Thread.id.in_(from_query)) if to_addr is not None: to_query = db_session.query(Message.thread_id). \ join(MessageContactAssociation).join(Contact).filter( Contact.email_address == to_addr, Contact.namespace_id == namespace_id, MessageContactAssociation.field == 'to_addr').subquery() query = query.filter(Thread.id.in_(to_query)) if cc_addr is not None: cc_query = db_session.query(Message.thread_id). \ join(MessageContactAssociation).join(Contact).filter( Contact.email_address == cc_addr, Contact.namespace_id == namespace_id, MessageContactAssociation.field == 'cc_addr').subquery() query = query.filter(Thread.id.in_(cc_query)) if bcc_addr is not None: bcc_query = db_session.query(Message.thread_id). \ join(MessageContactAssociation).join(Contact).filter( Contact.email_address == bcc_addr, Contact.namespace_id == namespace_id, MessageContactAssociation.field == 'bcc_addr').subquery() query = query.filter(Thread.id.in_(bcc_query)) if any_email is not None: any_contact_query = db_session.query(Message.thread_id). \ join(MessageContactAssociation).join(Contact). \ filter(Contact.email_address.in_(any_email), Contact.namespace_id == namespace_id).subquery() query = query.filter(Thread.id.in_(any_contact_query)) if filename is not None: files_query = db_session.query(Message.thread_id). \ join(Part).join(Block). \ filter(Block.filename == filename, Block.namespace_id == namespace_id). \ subquery() query = query.filter(Thread.id.in_(files_query)) if in_ is not None: category_filters = [Category.name == in_, Category.display_name == in_] try: valid_public_id(in_) category_filters.append(Category.public_id == in_) except InputError: pass category_query = db_session.query(Message.thread_id). \ join(MessageCategory).join(Category). \ filter(Category.namespace_id == namespace_id, or_(*category_filters)).subquery() query = query.filter(Thread.id.in_(category_query)) if unread is not None: read = not unread unread_query = db_session.query(Message.thread_id).filter( Message.namespace_id == namespace_id, Message.is_read == read).subquery() query = query.filter(Thread.id.in_(unread_query)) if starred is not None: starred_query = db_session.query(Message.thread_id).filter( Message.namespace_id == namespace_id, Message.is_starred == starred).subquery() query = query.filter(Thread.id.in_(starred_query)) if view == 'count': return {"count": query.one()[0]} # Eager-load some objects in order to make constructing API # representations faster. if view != 'ids': expand = (view == 'expanded') query = query.options(*Thread.api_loading_options(expand)) query = query.order_by(desc(Thread.recentdate)).limit(limit) if offset: query = query.offset(offset) if view == 'ids': return [x[0] for x in query.all()] return query.all()
def threads(namespace_id, subject, from_addr, to_addr, cc_addr, bcc_addr, any_email, thread_public_id, started_before, started_after, last_message_before, last_message_after, filename, in_, unread, starred, limit, offset, view, db_session, sort_field='recentdate', sort_order='desc'): if view == 'count': query = db_session.query(func.count(Thread.id)) elif view == 'ids': query = db_session.query(Thread.public_id) else: query = db_session.query(Thread) filters = [Thread.namespace_id == namespace_id, Thread.deleted_at == None] if thread_public_id is not None: filters.append(Thread.public_id == thread_public_id) if started_before is not None: filters.append(Thread.subjectdate < started_before) if started_after is not None: filters.append(Thread.subjectdate > started_after) if last_message_before is not None: filters.append(Thread.recentdate < last_message_before) if last_message_after is not None: filters.append(Thread.recentdate > last_message_after) if subject is not None: filters.append(Thread.subject == subject) query = query.filter(*filters) if from_addr is not None: from_query = contact_subquery(db_session, namespace_id, from_addr, 'from_addr') query = query.filter(Thread.id.in_(from_query)) if to_addr is not None: to_query = contact_subquery(db_session, namespace_id, to_addr, 'to_addr') query = query.filter(Thread.id.in_(to_query)) if cc_addr is not None: cc_query = contact_subquery(db_session, namespace_id, cc_addr, 'cc_addr') query = query.filter(Thread.id.in_(cc_query)) if bcc_addr is not None: bcc_query = contact_subquery(db_session, namespace_id, bcc_addr, 'bcc_addr') query = query.filter(Thread.id.in_(bcc_query)) if any_email is not None: any_contact_query = db_session.query(Message.thread_id) \ .join(MessageContactAssociation) \ .join(Contact, MessageContactAssociation.contact_id == Contact.id)\ .filter(Contact.email_address.in_(any_email), Contact.namespace_id == namespace_id)\ .subquery() query = query.filter(Thread.id.in_(any_contact_query)) if filename is not None: files_query = db_session.query(Message.thread_id). \ join(Part).join(Block). \ filter(Block.filename == filename, Block.namespace_id == namespace_id). \ subquery() query = query.filter(Thread.id.in_(files_query)) if in_ is not None: category_filters = [Category.name == in_, Category.display_name == in_] try: valid_public_id(in_) category_filters.append(Category.public_id == in_) except InputError: pass category_query = db_session.query(Message.thread_id). \ prefix_with('STRAIGHT_JOIN'). \ join(Message.messagecategories).join(MessageCategory.category). \ filter(Category.namespace_id == namespace_id, or_(*category_filters)).subquery() query = query.filter(Thread.id.in_(category_query)) if unread is not None: read = not unread unread_query = db_session.query(Message.thread_id).filter( Message.namespace_id == namespace_id, Message.is_read == read).subquery() query = query.filter(Thread.id.in_(unread_query)) if starred is not None: starred_query = db_session.query(Message.thread_id).filter( Message.namespace_id == namespace_id, Message.is_starred == starred).subquery() query = query.filter(Thread.id.in_(starred_query)) if view == 'count': return {"count": query.one()[0]} # Eager-load some objects in order to make constructing API # representations faster. if view != 'ids': expand = (view == 'expanded') query = query.options(*Thread.api_loading_options(expand)) #Sort by field and direction sort_field = sort_field.lower() if sort_field is not None else None sort_order = sort_order.lower() if sort_order is not None else None db_sort_field = Thread.recentdate db_sort_order = desc sort_field_mapping = { 'subject': Thread.subject, 'date': Thread.recentdate, 'start_date': Thread.subjectdate } if sort_field in sort_field_mapping: db_sort_field = sort_field_mapping[sort_field] if sort_order == 'asc': db_sort_order = asc query = query.order_by(db_sort_order(db_sort_field)).limit(limit) if offset: query = query.offset(offset) if view == 'ids': return [x[0] for x in query.all()] return query.all()