Exemple #1
0
def _get_last_trx_id_for_namespace(namespace_id, db_session):
    q = bakery(lambda session: session.query(Transaction.id))
    q += lambda q: q.filter(Transaction.namespace_id == bindparam(
        'namespace_id'))
    q += lambda q: q.order_by(desc(Transaction.created_at)).\
        order_by(desc(Transaction.id)).limit(1)
    return q(db_session).params(namespace_id=namespace_id).one()[0]
Exemple #2
0
def _get_last_trx_id_for_namespace(namespace_id, db_session):
    q = bakery(lambda session: session.query(Transaction.id))
    q += lambda q: q.filter(
        Transaction.namespace_id == bindparam('namespace_id'))
    q += lambda q: q.order_by(desc(Transaction.created_at)).\
        order_by(desc(Transaction.id)).limit(1)
    return q(db_session).params(namespace_id=namespace_id).one()[0]
def lastseenuid(account_id, session, folder_id):
    q = bakery(lambda session: session.query(func.max(ImapUid.msg_uid)))
    q += lambda q: q.filter(ImapUid.account_id == bindparam('account_id'),
                            ImapUid.folder_id == bindparam('folder_id'))
    res = q(session).params(account_id=account_id,
                            folder_id=folder_id).one()[0]
    return res or 0
def _get_last_trx_id_for_namespace(namespace_id, db_session):
    q = bakery(lambda session: session.query(func.max(Transaction.id)))
    q += lambda q: q.filter(
        Transaction.namespace_id == bindparam('namespace_id'),
        Transaction.deleted_at.is_(None))
    q += lambda q: q.with_hint(Transaction,
                               'USE INDEX (namespace_id_deleted_at)')
    return q(db_session).params(namespace_id=namespace_id).one()[0]
Exemple #5
0
def lastseenuid(account_id, session, folder_id):
    q = bakery(lambda session: session.query(func.max(ImapUid.msg_uid)))
    q += lambda q: q.filter(
        ImapUid.account_id == bindparam('account_id'),
        ImapUid.folder_id == bindparam('folder_id'))
    res = q(session).params(account_id=account_id,
                            folder_id=folder_id).one()[0]
    return res or 0
Exemple #6
0
def _get_last_trx_id_for_namespace(namespace_id, db_session):
    q = bakery(lambda session: session.query(func.max(Transaction.id)))
    q += lambda q: q.filter(
        Transaction.namespace_id == bindparam('namespace_id'),
        Transaction.deleted_at.is_(None))
    q += lambda q: q.with_hint(Transaction,
                               'USE INDEX (namespace_id_deleted_at)')
    return q(db_session).params(namespace_id=namespace_id).one()[0]
Exemple #7
0
def local_uids(account_id, session, folder_id, limit=None):
    q = bakery(lambda session: session.query(ImapUid.msg_uid))
    q += lambda q: q.filter(ImapUid.account_id == bindparam("account_id"), ImapUid.folder_id == bindparam("folder_id"))
    if limit:
        q += lambda q: q.order_by(desc(ImapUid.msg_uid))
        q += lambda q: q.limit(bindparam("limit"))
    results = q(session).params(account_id=account_id, folder_id=folder_id, limit=limit).all()
    return {u for u, in results}
def local_uids(account_id, session, folder_id, limit=None):
    q = bakery(lambda session: session.query(ImapUid.msg_uid))
    q += lambda q: q.filter(ImapUid.account_id == bindparam('account_id'),
                            ImapUid.folder_id == bindparam('folder_id'))
    if limit:
        q += lambda q: q.order_by(desc(ImapUid.msg_uid))
        q += lambda q: q.limit(bindparam('limit'))
    results = q(session).params(account_id=account_id,
                                folder_id=folder_id,
                                limit=limit).all()
    return {u for u, in results}
Exemple #9
0
 def from_public_id(cls, public_id, namespace_id, db_session):
     q = bakery(lambda s: s.query(cls))
     q += lambda q: q.filter(
         Message.public_id == bindparam('public_id'),
         Message.namespace_id == bindparam('namespace_id'))
     q += lambda q: q.options(
         joinedload(Message.thread).load_only('discriminator', 'public_id'),
         joinedload(Message.messagecategories).joinedload('category'),
         joinedload(Message.parts).joinedload('block'),
         joinedload(Message.events))
     return q(db_session).params(
         public_id=public_id, namespace_id=namespace_id).one()
Exemple #10
0
 def from_public_id(cls, public_id, namespace_id, db_session):
     q = bakery(lambda s: s.query(cls))
     q += lambda q: q.filter(
         Message.public_id == bindparam("public_id"),
         Message.namespace_id == bindparam("namespace_id"),
     )
     q += lambda q: q.options(
         joinedload(Message.thread).load_only("discriminator", "public_id"),
         joinedload(Message.messagecategories).joinedload(MessageCategory.
                                                          category),
         joinedload(Message.parts).joinedload("block"),
         joinedload(Message.events),
     )
     return (q(db_session).params(public_id=public_id,
                                  namespace_id=namespace_id).one())
Exemple #11
0
 def get(cls, id_, session):
     q = bakery(lambda session: session.query(cls))
     q += lambda q: q.filter(cls.id == bindparam('id_'))
     return q(session).params(id_=id_).first()
Exemple #12
0
 def get(cls, id_, session):
     q = bakery(lambda session: session.query(cls))
     q += lambda q: q.filter(cls.id == bindparam('id_'))
     return q(session).params(id_=id_).first()
Exemple #13
0
def messages_or_drafts(
    namespace_id,
    drafts,
    subject,
    from_addr,
    to_addr,
    cc_addr,
    bcc_addr,
    any_email,
    thread_public_id,
    started_before,
    started_after,
    last_message_before,
    last_message_after,
    received_before,
    received_after,
    filename,
    in_,
    unread,
    starred,
    limit,
    offset,
    view,
    db_session,
):
    # Warning: complexities ahead. This function sets up the query that gets
    # results for the /messages API. It loads from several tables, supports a
    # variety of views and filters, and is performance-critical for the API. As
    # such, it is not super simple.
    #
    # We bake the generated query to avoid paying query compilation overhead on
    # every request. This requires some attention: every parameter that can
    # vary between calls *must* be inserted via bindparam(), or else the first
    # value passed will be baked into the query and reused on each request.
    # Subqueries (on contact tables) can't be properly baked, so we have to
    # call query.spoil() on those code paths.

    param_dict = {
        "namespace_id": namespace_id,
        "drafts": drafts,
        "subject": subject,
        "from_addr": from_addr,
        "to_addr": to_addr,
        "cc_addr": cc_addr,
        "bcc_addr": bcc_addr,
        "any_email": any_email,
        "thread_public_id": thread_public_id,
        "received_before": received_before,
        "received_after": received_after,
        "started_before": started_before,
        "started_after": started_after,
        "last_message_before": last_message_before,
        "last_message_after": last_message_after,
        "filename": filename,
        "in_": in_,
        "unread": unread,
        "starred": starred,
        "limit": limit,
        "offset": offset,
    }

    if view == "count":
        query = bakery(lambda s: s.query(func.count(Message.id)))
    elif view == "ids":
        query = bakery(lambda s: s.query(Message.public_id))
    else:
        query = bakery(lambda s: s.query(Message))

        # Sometimes MySQL doesn't pick the right index. In the case of a
        # regular /messages query, ix_message_ns_id_is_draft_received_date
        # is the best index because we always filter on
        # the namespace_id, is_draft and then order by received_date.
        # For other "exotic" queries, we let the MySQL query planner
        # pick the right index.
        if all(v is None for v in [
                subject,
                from_addr,
                to_addr,
                cc_addr,
                bcc_addr,
                any_email,
                thread_public_id,
                filename,
                in_,
                started_before,
                started_after,
                last_message_before,
                last_message_after,
        ]):
            query += lambda q: q.with_hint(
                Message,
                "FORCE INDEX (ix_message_ns_id_is_draft_received_date)",
                "mysql",
            )

    query += lambda q: q.join(Thread, Message.thread_id == Thread.id)
    query += lambda q: q.filter(
        Message.namespace_id == bindparam("namespace_id"),
        Message.is_draft == bindparam("drafts"),
        Thread.deleted_at == None,
    )

    if subject is not None:
        query += lambda q: q.filter(Message.subject == bindparam("subject"))

    if unread is not None:
        query += lambda q: q.filter(Message.is_read != bindparam("unread"))

    if starred is not None:
        query += lambda q: q.filter(Message.is_starred == bindparam("starred"))

    if thread_public_id is not None:
        query += lambda q: q.filter(Thread.public_id == bindparam(
            "thread_public_id"))

    # TODO: deprecate thread-oriented date filters on message endpoints.
    if started_before is not None:
        query += lambda q: q.filter(
            Thread.subjectdate < bindparam("started_before"),
            Thread.namespace_id == bindparam("namespace_id"),
        )

    if started_after is not None:
        query += lambda q: q.filter(
            Thread.subjectdate > bindparam("started_after"),
            Thread.namespace_id == bindparam("namespace_id"),
        )

    if last_message_before is not None:
        query += lambda q: q.filter(
            Thread.recentdate < bindparam("last_message_before"),
            Thread.namespace_id == bindparam("namespace_id"),
        )

    if last_message_after is not None:
        query += lambda q: q.filter(
            Thread.recentdate > bindparam("last_message_after"),
            Thread.namespace_id == bindparam("namespace_id"),
        )

    if received_before is not None:
        query += lambda q: q.filter(Message.received_date <= bindparam(
            "received_before"))

    if received_after is not None:
        query += lambda q: q.filter(Message.received_date > bindparam(
            "received_after"))

    if to_addr is not None:
        query.spoil()
        to_query = (db_session.query(
            MessageContactAssociation.message_id).join(
                Contact,
                MessageContactAssociation.contact_id == Contact.id).filter(
                    MessageContactAssociation.field == "to_addr",
                    Contact.email_address == to_addr,
                    Contact.namespace_id == bindparam("namespace_id"),
                ).subquery())
        query += lambda q: q.filter(Message.id.in_(to_query))

    if from_addr is not None:
        query.spoil()
        from_query = (db_session.query(
            MessageContactAssociation.message_id).join(
                Contact,
                MessageContactAssociation.contact_id == Contact.id).filter(
                    MessageContactAssociation.field == "from_addr",
                    Contact.email_address == from_addr,
                    Contact.namespace_id == bindparam("namespace_id"),
                ).subquery())
        query += lambda q: q.filter(Message.id.in_(from_query))

    if cc_addr is not None:
        query.spoil()
        cc_query = (db_session.query(
            MessageContactAssociation.message_id).join(
                Contact,
                MessageContactAssociation.contact_id == Contact.id).filter(
                    MessageContactAssociation.field == "cc_addr",
                    Contact.email_address == cc_addr,
                    Contact.namespace_id == bindparam("namespace_id"),
                ).subquery())
        query += lambda q: q.filter(Message.id.in_(cc_query))

    if bcc_addr is not None:
        query.spoil()
        bcc_query = (db_session.query(
            MessageContactAssociation.message_id).join(
                Contact,
                MessageContactAssociation.contact_id == Contact.id).filter(
                    MessageContactAssociation.field == "bcc_addr",
                    Contact.email_address == bcc_addr,
                    Contact.namespace_id == bindparam("namespace_id"),
                ).subquery())
        query += lambda q: q.filter(Message.id.in_(bcc_query))

    if any_email is not None:
        query.spoil()
        any_email_query = (db_session.query(
            MessageContactAssociation.message_id).join(
                Contact,
                MessageContactAssociation.contact_id == Contact.id).filter(
                    Contact.email_address.in_(any_email),
                    Contact.namespace_id == bindparam("namespace_id"),
                ).subquery())
        query += lambda q: q.filter(Message.id.in_(any_email_query))

    if filename is not None:
        query += (lambda q: q.join(Part).join(Block).filter(
            Block.filename == bindparam("filename"),
            Block.namespace_id == bindparam("namespace_id"),
        ))

    if in_ is not None:
        query.spoil()
        category_filters = [
            Category.name == bindparam("in_"),
            Category.display_name == bindparam("in_"),
        ]
        try:
            valid_public_id(in_)
            category_filters.append(Category.public_id == bindparam("in_id"))
            # Type conversion and bindparams interact poorly -- you can't do
            # e.g.
            # query.filter(or_(Category.name == bindparam('in_'),
            #                  Category.public_id == bindparam('in_')))
            # because the binary conversion defined by Category.public_id will
            # be applied to the bound value prior to its insertion in the
            # query. So we define another bindparam for the public_id:
            param_dict["in_id"] = in_
        except InputError:
            pass
        query += (lambda q: q.prefix_with("STRAIGHT_JOIN").join(
            Message.messagecategories).join(MessageCategory.category).filter(
                Category.namespace_id == namespace_id, or_(*category_filters)))

    if view == "count":
        res = query(db_session).params(**param_dict).one()[0]
        return {"count": res}

    query += lambda q: q.order_by(desc(Message.received_date))
    query += lambda q: q.limit(bindparam("limit"))
    if offset:
        query += lambda q: q.offset(bindparam("offset"))

    if view == "ids":
        res = query(db_session).params(**param_dict).all()
        return [x[0] for x in res]

    # Eager-load related attributes to make constructing API representations
    # faster. Note that we don't use the options defined by
    # Message.api_loading_options() here because we already have a join to the
    # thread table. We should eventually try to simplify this.
    query += lambda q: q.options(
        contains_eager(Message.thread),
        subqueryload(Message.messagecategories).joinedload(
            "category", "created_at"),
        subqueryload(Message.parts).joinedload(Part.block),
        subqueryload(Message.events),
    )

    prepared = query(db_session).params(**param_dict)
    return prepared.all()
Exemple #14
0
 def from_public_id(cls, public_id, db_session):
     q = bakery(lambda session: session.query(Namespace))
     q += lambda q: q.filter(
         Namespace.public_id == bindparam('public_id'))
     return q(db_session).params(public_id=public_id).one()
Exemple #15
0
def messages_or_drafts(namespace_id, drafts, subject, from_addr, to_addr,
                       cc_addr, bcc_addr, any_email, thread_public_id,
                       started_before, started_after, last_message_before,
                       last_message_after, received_before, received_after,
                       filename, in_, unread, starred, limit, offset, view,
                       db_session):
    # Warning: complexities ahead. This function sets up the query that gets
    # results for the /messages API. It loads from several tables, supports a
    # variety of views and filters, and is performance-critical for the API. As
    # such, it is not super simple.
    #
    # We bake the generated query to avoid paying query compilation overhead on
    # every request. This requires some attention: every parameter that can
    # vary between calls *must* be inserted via bindparam(), or else the first
    # value passed will be baked into the query and reused on each request.
    # Subqueries (on contact tables) can't be properly baked, so we have to
    # call query.spoil() on those code paths.

    param_dict = {
        'namespace_id': namespace_id,
        'drafts': drafts,
        'subject': subject,
        'from_addr': from_addr,
        'to_addr': to_addr,
        'cc_addr': cc_addr,
        'bcc_addr': bcc_addr,
        'any_email': any_email,
        'thread_public_id': thread_public_id,
        'received_before': received_before,
        'received_after': received_after,
        'started_before': started_before,
        'started_after': started_after,
        'last_message_before': last_message_before,
        'last_message_after': last_message_after,
        'filename': filename,
        'in_': in_,
        'unread': unread,
        'starred': starred,
        'limit': limit,
        'offset': offset
    }

    if view == 'count':
        query = bakery(lambda s: s.query(func.count(Message.id)))
    elif view == 'ids':
        query = bakery(lambda s: s.query(Message.public_id))
    else:
        query = bakery(lambda s: s.query(Message))
    query += lambda q: q.join(Thread)
    query += lambda q: q.filter(
        Message.namespace_id == bindparam('namespace_id'),
        Message.is_draft == bindparam('drafts'))

    if subject is not None:
        query += lambda q: q.filter(Message.subject == bindparam('subject'))

    if unread is not None:
        query += lambda q: q.filter(Message.is_read != bindparam('unread'))

    if starred is not None:
        query += lambda q: q.filter(Message.is_starred == bindparam('starred'))

    if thread_public_id is not None:
        query += lambda q: q.filter(
            Thread.public_id == bindparam('thread_public_id'))

    # TODO: deprecate thread-oriented date filters on message endpoints.
    if started_before is not None:
        query += lambda q: q.filter(
            Thread.subjectdate < bindparam('started_before'),
            Thread.namespace_id == bindparam('namespace_id'))

    if started_after is not None:
        query += lambda q: q.filter(
            Thread.subjectdate > bindparam('started_after'),
            Thread.namespace_id == bindparam('namespace_id'))

    if last_message_before is not None:
        query += lambda q: q.filter(
            Thread.recentdate < bindparam('last_message_before'),
            Thread.namespace_id == bindparam('namespace_id'))

    if last_message_after is not None:
        query += lambda q: q.filter(
            Thread.recentdate > bindparam('last_message_after'),
            Thread.namespace_id == bindparam('namespace_id'))

    if received_before is not None:
        query += lambda q: q.filter(
            Message.received_date <= bindparam('received_before'))

    if received_after is not None:
        query += lambda q: q.filter(
            Message.received_date > bindparam('received_after'))

    if to_addr is not None:
        query.spoil()
        to_query = db_session.query(MessageContactAssociation.message_id). \
            join(Contact).filter(
                MessageContactAssociation.field == 'to_addr',
                Contact.email_address == to_addr,
                Contact.namespace_id == bindparam('namespace_id')).subquery()
        query += lambda q: q.filter(Message.id.in_(to_query))

    if from_addr is not None:
        query.spoil()
        from_query = db_session.query(MessageContactAssociation.message_id). \
            join(Contact).filter(
                MessageContactAssociation.field == 'from_addr',
                Contact.email_address == from_addr,
                Contact.namespace_id == bindparam('namespace_id')).subquery()
        query += lambda q: q.filter(Message.id.in_(from_query))

    if cc_addr is not None:
        query.spoil()
        cc_query = db_session.query(MessageContactAssociation.message_id). \
            join(Contact).filter(
                MessageContactAssociation.field == 'cc_addr',
                Contact.email_address == cc_addr,
                Contact.namespace_id == bindparam('namespace_id')).subquery()
        query += lambda q: q.filter(Message.id.in_(cc_query))

    if bcc_addr is not None:
        query.spoil()
        bcc_query = db_session.query(MessageContactAssociation.message_id). \
            join(Contact).filter(
                MessageContactAssociation.field == 'bcc_addr',
                Contact.email_address == bcc_addr,
                Contact.namespace_id == bindparam('namespace_id')).subquery()
        query += lambda q: q.filter(Message.id.in_(bcc_query))

    if any_email is not None:
        query.spoil()
        any_email_query = db_session.query(
            MessageContactAssociation.message_id).join(Contact). \
            filter(Contact.email_address == any_email,
                   Contact.namespace_id == bindparam('namespace_id')). \
            subquery()
        query += lambda q: q.filter(Message.id.in_(any_email_query))

    if filename is not None:
        query += lambda q: q.join(Part).join(Block). \
            filter(Block.filename == bindparam('filename'),
                   Block.namespace_id == bindparam('namespace_id'))

    if in_ is not None:
        query.spoil()
        category_filters = [Category.name == bindparam('in_'),
                            Category.display_name == bindparam('in_')]
        try:
            valid_public_id(in_)
            category_filters.append(Category.public_id == bindparam('in_id'))
            # Type conversion and bindparams interact poorly -- you can't do
            # e.g.
            # query.filter(or_(Category.name == bindparam('in_'),
            #                  Category.public_id == bindparam('in_')))
            # because the binary conversion defined by Category.public_id will
            # be applied to the bound value prior to its insertion in the
            # query. So we define another bindparam for the public_id:
            param_dict['in_id'] = in_
        except InputError:
            pass
        query += lambda q: q.join(MessageCategory).join(Category). \
            filter(Category.namespace_id == namespace_id,
                   or_(*category_filters))

    if view == 'count':
        res = query(db_session).params(**param_dict).one()[0]
        return {"count": res}

    query += lambda q: q.order_by(desc(Message.received_date))
    query += lambda q: q.limit(bindparam('limit'))
    if offset:
        query += lambda q: q.offset(bindparam('offset'))

    if view == 'ids':
        res = query(db_session).params(**param_dict).all()
        return [x[0] for x in res]

    # Eager-load related attributes to make constructing API representations
    # faster. Note that we don't use the options defined by
    # Message.api_loading_options() here because we already have a join to the
    # thread table. We should eventually try to simplify this.
    query += lambda q: q.options(
        contains_eager(Message.thread),
        subqueryload(Message.messagecategories).joinedload('category'),
        subqueryload(Message.parts).joinedload(Part.block),
        subqueryload(Message.events))

    prepared = query(db_session).params(**param_dict)
    return prepared.all()
Exemple #16
0
def messages_or_drafts(namespace_id, drafts, subject, from_addr, to_addr,
                       cc_addr, bcc_addr, any_email, thread_public_id,
                       started_before, started_after, last_message_before,
                       last_message_after, received_before, received_after,
                       filename, in_, unread, starred, limit, offset, view,
                       db_session):
    # Warning: complexities ahead. This function sets up the query that gets
    # results for the /messages API. It loads from several tables, supports a
    # variety of views and filters, and is performance-critical for the API. As
    # such, it is not super simple.
    #
    # We bake the generated query to avoid paying query compilation overhead on
    # every request. This requires some attention: every parameter that can
    # vary between calls *must* be inserted via bindparam(), or else the first
    # value passed will be baked into the query and reused on each request.
    # Subqueries (on contact tables) can't be properly baked, so we have to
    # call query.spoil() on those code paths.

    param_dict = {
        'namespace_id': namespace_id,
        'drafts': drafts,
        'subject': subject,
        'from_addr': from_addr,
        'to_addr': to_addr,
        'cc_addr': cc_addr,
        'bcc_addr': bcc_addr,
        'any_email': any_email,
        'thread_public_id': thread_public_id,
        'received_before': received_before,
        'received_after': received_after,
        'started_before': started_before,
        'started_after': started_after,
        'last_message_before': last_message_before,
        'last_message_after': last_message_after,
        'filename': filename,
        'in_': in_,
        'unread': unread,
        'starred': starred,
        'limit': limit,
        'offset': offset
    }

    if view == 'count':
        query = bakery(lambda s: s.query(func.count(Message.id)))
    elif view == 'ids':
        query = bakery(lambda s: s.query(Message.public_id))
    else:
        query = bakery(lambda s: s.query(Message))
    query += lambda q: q.join(Thread)
    query += lambda q: q.filter(
        Message.namespace_id == bindparam('namespace_id'), Message.is_draft ==
        bindparam('drafts'))

    if subject is not None:
        query += lambda q: q.filter(Message.subject == bindparam('subject'))

    if unread is not None:
        query += lambda q: q.filter(Message.is_read != bindparam('unread'))

    if starred is not None:
        query += lambda q: q.filter(Message.is_starred == bindparam('starred'))

    if thread_public_id is not None:
        query += lambda q: q.filter(Thread.public_id == bindparam(
            'thread_public_id'))

    # TODO: deprecate thread-oriented date filters on message endpoints.
    if started_before is not None:
        query += lambda q: q.filter(
            Thread.subjectdate < bindparam('started_before'), Thread.
            namespace_id == bindparam('namespace_id'))

    if started_after is not None:
        query += lambda q: q.filter(
            Thread.subjectdate > bindparam('started_after'), Thread.
            namespace_id == bindparam('namespace_id'))

    if last_message_before is not None:
        query += lambda q: q.filter(
            Thread.recentdate < bindparam('last_message_before'), Thread.
            namespace_id == bindparam('namespace_id'))

    if last_message_after is not None:
        query += lambda q: q.filter(
            Thread.recentdate > bindparam('last_message_after'), Thread.
            namespace_id == bindparam('namespace_id'))

    if received_before is not None:
        query += lambda q: q.filter(Message.received_date <= bindparam(
            'received_before'))

    if received_after is not None:
        query += lambda q: q.filter(Message.received_date > bindparam(
            'received_after'))

    if to_addr is not None:
        query.spoil()
        to_query = db_session.query(MessageContactAssociation.message_id). \
            join(Contact).filter(
                MessageContactAssociation.field == 'to_addr',
                Contact.email_address == to_addr,
                Contact.namespace_id == bindparam('namespace_id')).subquery()
        query += lambda q: q.filter(Message.id.in_(to_query))

    if from_addr is not None:
        query.spoil()
        from_query = db_session.query(MessageContactAssociation.message_id). \
            join(Contact).filter(
                MessageContactAssociation.field == 'from_addr',
                Contact.email_address == from_addr,
                Contact.namespace_id == bindparam('namespace_id')).subquery()
        query += lambda q: q.filter(Message.id.in_(from_query))

    if cc_addr is not None:
        query.spoil()
        cc_query = db_session.query(MessageContactAssociation.message_id). \
            join(Contact).filter(
                MessageContactAssociation.field == 'cc_addr',
                Contact.email_address == cc_addr,
                Contact.namespace_id == bindparam('namespace_id')).subquery()
        query += lambda q: q.filter(Message.id.in_(cc_query))

    if bcc_addr is not None:
        query.spoil()
        bcc_query = db_session.query(MessageContactAssociation.message_id). \
            join(Contact).filter(
                MessageContactAssociation.field == 'bcc_addr',
                Contact.email_address == bcc_addr,
                Contact.namespace_id == bindparam('namespace_id')).subquery()
        query += lambda q: q.filter(Message.id.in_(bcc_query))

    if any_email is not None:
        query.spoil()
        any_email_query = db_session.query(
            MessageContactAssociation.message_id).join(Contact). \
            filter(Contact.email_address.in_(any_email),
                   Contact.namespace_id == bindparam('namespace_id')). \
            subquery()
        query += lambda q: q.filter(Message.id.in_(any_email_query))

    if filename is not None:
        query += lambda q: q.join(Part).join(Block). \
            filter(Block.filename == bindparam('filename'),
                   Block.namespace_id == bindparam('namespace_id'))

    if in_ is not None:
        query.spoil()
        category_filters = [
            Category.name == bindparam('in_'),
            Category.display_name == bindparam('in_')
        ]
        try:
            valid_public_id(in_)
            category_filters.append(Category.public_id == bindparam('in_id'))
            # Type conversion and bindparams interact poorly -- you can't do
            # e.g.
            # query.filter(or_(Category.name == bindparam('in_'),
            #                  Category.public_id == bindparam('in_')))
            # because the binary conversion defined by Category.public_id will
            # be applied to the bound value prior to its insertion in the
            # query. So we define another bindparam for the public_id:
            param_dict['in_id'] = in_
        except InputError:
            pass
        query += lambda q: q.join(MessageCategory).join(Category). \
            filter(Category.namespace_id == namespace_id,
                   or_(*category_filters))

    if view == 'count':
        res = query(db_session).params(**param_dict).one()[0]
        return {"count": res}

    query += lambda q: q.order_by(desc(Message.received_date))
    query += lambda q: q.limit(bindparam('limit'))
    if offset:
        query += lambda q: q.offset(bindparam('offset'))

    if view == 'ids':
        res = query(db_session).params(**param_dict).all()
        return [x[0] for x in res]

    # Eager-load related attributes to make constructing API representations
    # faster. Note that we don't use the options defined by
    # Message.api_loading_options() here because we already have a join to the
    # thread table. We should eventually try to simplify this.
    query += lambda q: q.options(
        contains_eager(Message.thread),
        subqueryload(Message.messagecategories).joinedload('category'),
        subqueryload(Message.parts).joinedload(Part.block),
        subqueryload(Message.events))

    prepared = query(db_session).params(**param_dict)
    return prepared.all()