示例#1
0
文件: persons.py 项目: alishir/tcr
def query_person_contacts():
    return (
        DBSession.query(
            Person.id.label('person_id'),
            func.array_to_string(
                func.array_agg(
                    case([(Contact.contact_type == 'phone', Contact.contact)])
                ),
                ', '
            ).label('phone'),
            func.array_to_string(
                func.array_agg(
                    case([(Contact.contact_type == 'email', Contact.contact)])
                ),
                ', '
            ).label('email'),
            func.array_to_string(
                func.array_agg(
                    case([(Contact.contact_type == 'skype', Contact.contact)])
                ),
                ', '
            ).label('skype'),
        )
        .join(Contact, Person.contacts)
        .group_by(Person.id)
    )
示例#2
0
def search():
    '''
    Generic search page
    '''
    form = SearchForm()
    if request.method == 'GET':
        return render_template('search.html', form=form)
    if request.method == 'POST':
        query = User.query  #pylint: disable=no-member

        if form.country.data and form.country.data != 'ZZ':
            query = query.filter(User.country == form.country.data)

        if form.locales.data:
            query = query.join(User.languages).filter(UserLanguage.locale.in_(
                form.locales.data))

        if form.expertise_domain_names.data:
            query = query.join(User.expertise_domains).filter(UserExpertiseDomain.name.in_(
                form.expertise_domain_names.data))

        if form.fulltext.data:
            query = query.filter(func.to_tsvector(func.array_to_string(array([
                User.first_name, User.last_name, User.organization, User.position,
                User.projects]), ' ')).op('@@')(func.plainto_tsquery(form.fulltext.data)))

        # TODO ordering by relevance
        return render_template('search-results.html',
                               title='Expertise search',
                               form=form,
                               results=query.limit(20).all())
示例#3
0
def emails(env):
    emails = (
        session.query(*Email.columns())
        .order_by(Email.gm_thrid, Email.date.desc())
    )
    if 'label' in env.request.args:
        label_id = env.request.args['label']
        label = session.query(Label).filter(Label.id == label_id).one()
        emails = emails.filter(Email.labels.has_key(label_id))
    elif 'email' in env.request.args:
        email = env.request.args['email']
        emails = emails.filter(
            func.array_to_string(Email.from_ + Email.to, ',')
            .contains('<%s>' % email)
        )
        label = None
    else:
        env.abort(404)

    groups = groupby(emails, lambda v: v.gm_thrid)
    groups = [(k, list(v)) for k, v in groups]
    counts = {k: len(v) for k, v in groups}
    emails = (Email.model(v[0]) for k, v in groups)
    emails = sorted(emails, key=lambda v: v.date, reverse=True)
    return env.render('emails.tpl', {
        'emails': emails,
        'counts': counts,
        'label': label,
        'labels': get_labels()
    })
示例#4
0
def service_info_column(crop):
    """return optionally cropped service.info column"""

    if crop:
        return func.array_to_string(
            func.string_to_array(Service.info,
                                 ' ',
                                 type_=postgresql.ARRAY(
                                     db.String))[1:int(crop)], ' ')
    return Service.info
示例#5
0
文件: persons.py 项目: alishir/tcr
def query_person_passports():
    return (
        DBSession.query(
            Person.id.label('person_id'),
            func.array_to_string(
                func.array_agg(
                    case([(Passport.passport_type == 'citizen', Passport.num)])
                ),
                ', '
            ).label('citizen'),
            func.array_to_string(
                func.array_agg(
                    case([(Passport.passport_type == 'foreign', Passport.num)])
                ),
                ', '
            ).label('foreign'),
        )
        .join(Passport, Person.passports)
        .group_by(Person.id)
    )
    
示例#6
0
    def _search_in_fields_query(cls,
                                term,
                                fields=None,
                                array_fields=None,
                                apply_to_query=None):
        """
        Perform the actual search in specified fields.

        Search is performed using SQL ILIKE, and all array fields are joined using a comma (',').
        All clauses are OR'ed.
        E.g. ``Searchable._search_in_fields('aba', ['title', 'description'], ['keywords'])``
        gives the following SQL:

        .. sourcecode:: sql

            SELECT * FROM whatever WHERE
                title ILIKE 'aba' OR
                description ILIKE 'aba' OR
                array_to_string(keywords, ",") ILIKE 'aba';

        ``array_to_string`` is ``sqlalchemy.func.array_to_string``.

        ``apply_to_query`` can be used for chaining multiple logical searches.

        .. sourcecode:: python

            first_clause = Model._search_in_fields_query('term', ['whatever'])
            results = Model._search_in_fields_query('term2', ['whatever2'], apply_to_query=first_clause).all()


        :param term: term to search for.
        :param fields: usual text fields to perform search in.
        :param array_fields: array fields to perform search in.
        :param apply_to_query: chaining helper.
        :return: list of results.
        """

        if fields is None:
            fields = []
        if array_fields is None:
            array_fields = []
        if apply_to_query is None:
            apply_to_query = cls.query
        filters = [
            getattr(cls, field).ilike('%' + term + '%') for field in fields
        ] + [
            func.array_to_string(getattr(cls, field),
                                 ',').ilike('%' + term + '%')
            for field in array_fields
        ]
        return apply_to_query.filter(or_(*filters))
示例#7
0
 def __init__(self, column_name):
     column = ColumnClause(column_name)
     # Postgres allows the condition "<sth> <op> ALL (<array>)" that
     # is true iff for all elements of array "<sth> <op> <element>".
     # This works for (in)equality but, as the order of the operands
     # is preserved, it doesn't work for regexp matching, where the
     # syntax is "<text> ~ <pattern>". Our regexp operates on a per
     # character basis so we can work around it by concatenating the
     # items of the array (using array_to_string) and match the
     # regexp on the result.
     empty = literal_column("''")
     super(FilenameListConstraint, self).__init__(
         and_(
             func.array_to_string(column, empty).op("~")(
                 literal_column("'^[A-Za-z0-9_.%-]*$'")),
             empty != all_(column),
             literal_column("'.'") != all_(column),
             literal_column("'..'") != all_(column)))
示例#8
0
文件: views.py 项目: aaeds/mailr
def emails(env):
    emails = (
        session.query(Email.gm_thrid)
        .order_by(Email.gm_thrid, Email.date.desc())
    )
    if 'label' in env.request.args:
        label_id = env.request.args['label']
        label = session.query(Label).filter(Label.id == label_id).one()
        emails = emails.filter(Email.labels.has_key(label_id))
    elif 'email' in env.request.args:
        email = env.request.args['email']
        emails = emails.filter(
            func.array_to_string(Email.from_ + Email.to, ',')
            .contains('<%s>' % email)
        )
        label = None
    else:
        env.abort(404)

    threads = list(
        session.query(
            Email.gm_thrid,
            func.count('*').label('count'),
            func.max(Email.uid).label('uid')
        )
        .filter(Email.gm_thrid.in_([m.gm_thrid for m in emails]))
        .group_by(Email.gm_thrid)
    )
    emails = (
        session.query(Email)
        .filter(Email.uid.in_([m.uid for m in threads]))
        .order_by(Email.date.desc())
    )
    counts = {t.gm_thrid: t.count for t in threads}
    return env.render('emails.tpl', {
        'emails': emails,
        'counts': counts,
        'label': label,
        'labels': get_labels()
    })
示例#9
0
    def visit_reduction(self, expr):
        if getattr(expr, '_unique', False):
            raise NotImplementedError

        input = self._expr_to_sqlalchemy[expr.input]

        # TODO: MEDIAN does not support
        if isinstance(expr, (Max, GroupedMax)):
            f = func.max
        elif isinstance(expr, (Min, GroupedMin)):
            f = func.min
        elif isinstance(expr, (Count, GroupedCount)):
            f = func.count
        elif isinstance(expr, (Sum, GroupedSum)):
            f = func.sum
        elif isinstance(expr, (Var, GroupedVar)) and expr._ddof in (0, 1):
            f = func.var_pop if expr._ddof == 0 else func.var_samp
        elif isinstance(expr, (Std, GroupedStd)) and expr._ddof in (0, 1):
            f = func.stddev_pop if expr._ddof == 0 else func.stddev_samp
        elif isinstance(expr, (Mean, GroupedMean)):
            f = func.avg
        elif isinstance(expr, (NUnique, GroupedNUnique)):
            f = lambda *x: func.count(distinct(*x))
        elif isinstance(expr, (Cat, GroupedCat)):
            f = lambda x: func.array_to_string(func.array_agg(x),
                                               self._expr_to_sqlalchemy[expr._sep])
        else:
            raise NotImplementedError

        if isinstance(expr, (Count, GroupedCount)) and \
                isinstance(expr.input, CollectionExpr):
            reduced = f()
        elif isinstance(expr, (NUnique, GroupedNUnique)):
            if len(expr.inputs) > 1:
                raise NotImplementedError
            reduced = f(*(self._expr_to_sqlalchemy[i] for i in expr.inputs))
        else:
            reduced = f(input)
        self._add(expr, reduced)
示例#10
0
def _query_session(app,
                   staff,
                   domain,
                   type,
                   is_active,
                   context_label=None,
                   handler_context_label=None,
                   leader_context_label=None,
                   owner=None,
                   handler_filter_self=None,
                   leader_filter_self=None,
                   tag=None,
                   is_online=None,
                   customer=None):
    q = Session.query.join('project').options(orm.undefer('project.*')) \
        .filter(Session.is_active == is_active,
                Session.project.has(
                    app_name=app.name,
                    domain=domain, type=type))

    filter_project_scopes = Session.project.has(
        func.x_scopes_match_ctxes(Project.scope_labels, staff.uid,
                                  staff.context_labels))

    leader_only_self = leader_filter_self == 'only'
    handler_only_self = handler_filter_self == 'only'
    if handler_only_self:
        # 仅自己接待的
        q = q.filter(Session.handler_id == staff.id)
        if leader_only_self:
            # 仅自己负责的
            q = q.filter(Session.project.has(leader_id=staff.id))
        elif leader_filter_self == 'exclude':
            # 排除自己负责的
            q = q.filter(Session.project.has(Project.leader_id != staff.id))
    elif handler_filter_self == 'exclude':
        # 排除自己接待的
        q = q.filter(Session.handler_id != staff.id)
        if leader_only_self:
            # 仅自己负责的
            q = q.filter(Session.project.has(leader_id=staff.id))
        elif leader_filter_self == 'exclude':
            # 排除自己负责的
            q = q.filter(Session.project.has(Project.leader_id != staff.id),
                         filter_project_scopes)
        else:
            # 包含自己负责的
            q = q.filter(
                or_(Session.project.has(leader_id=staff.id),
                    filter_project_scopes))
    else:
        # 包含自己接待的
        if leader_only_self:
            # 仅自己负责的
            q = q.filter(Session.project.has(leader_id=staff.id))
        elif leader_filter_self == 'exclude':
            # 排除自己负责的
            q = q.filter(Session.project.has(Project.leader_id != staff.id),
                         filter_project_scopes)
        else:
            # 包含自己负责的
            q = q.filter(
                or_(Session.handler_id == staff.id,
                    Session.project.has(leader_id=staff.id),
                    filter_project_scopes))

    if context_label is not None:
        path, uids = context_label
        if len(uids) > 0:
            q = q.filter(
                Session.project.has(
                    func.x_scopes_match_targets(Project.scope_labels,
                                                [path + uid for uid in uids])))
        else:
            q = q.filter(
                Session.project.has(
                    func.x_scopes_match_target(Project.scope_labels, path)))

    if handler_context_label is not None:
        path, uids = handler_context_label
        q = q.filter(
            Session.handler.has(
                func.x_target_match_ctxes(path, Staff.uid,
                                          Staff.context_labels)))
        if len(uids) > 0 and not handler_only_self:
            q = q.filter(Session.handler.has(Staff.uid.in_(uids)))

    if leader_context_label is not None:
        path, uids = leader_context_label
        q = q.filter(
            Session.project.has(
                Project.leader.has(
                    func.x_target_match_ctxes(path, Staff.uid,
                                              Staff.context_labels))))
        if len(uids) > 0 and not leader_filter_self:
            q = q.filter(
                Session.handler.has(Project.leader.has(Staff.uid.in_(uids))))

    if owner:
        s = f'%{owner}%'
        q = q.filter(
            Session.project.has(
                Project.owner.has(
                    or_(Customer.name.like(s), Customer.mobile.like(s),
                        Customer.uid.like(s)))))

    if customer:
        s = f'%{customer}%'
        q = q.filter(
            Session.project.has(
                Project.customers.any(
                    or_(Customer.name.like(s), Customer.mobile.like(s),
                        Customer.uid.like(s)))))

    if tag:
        s = f'%{tag}%'
        q = q.filter(
            Session.project.has(
                func.array_to_string(Project.tags, '*', ',').like(s)))

    if is_online is not None:
        q = q.filter(Session.project.has(Project.is_online == is_online))

    return q
示例#11
0
def emails(env):
    label = None
    emails = (
        session.query(Email.gm_thrid)
        .order_by(Email.gm_thrid, Email.date.desc())
    )
    if 'label' in env.request.args:
        label_id = env.request.args['label']
        label = session.query(Label).filter(Label.id == label_id).one()
        emails = emails.filter(Email.labels.has_key(label_id)).all()
    elif 'email' in env.request.args:
        email = env.request.args['email']
        emails = emails.filter(
            func.array_to_string(Email.from_ + Email.to, ',')
            .contains('<%s>' % email)
        ).all()
    elif 'subj' in env.request.args:
        subj = env.request.args['subj']
        subj = re.sub(r'([()\[\]{}_*|+?])', r'\\\1', subj)
        emails = emails.filter(
            Email.subject.op('SIMILAR TO')('(_{2,10}:)*\ ?' + subj)
        ).all()
    elif 'q' in env.request.args and env.request.args['q']:
        query = env.request.args['q']
        query = query.replace(' ', '\ ')
        emails = session.execute(
            '''
            SELECT id, gm_thrid
            FROM emails_search
            WHERE document @@ to_tsquery('simple', :query)
            ORDER BY ts_rank(document, to_tsquery('simple', :query)) DESC
            ''',
            {'query': query}
        ).fetchall()
    else:
        env.abort(404)

    if len(emails):
        threads = list(
            session.query(
                Email.gm_thrid,
                func.count('*').label('count'),
                func.max(Email.uid).label('uid')
            )
            .filter(Email.gm_thrid.in_([m.gm_thrid for m in emails]))
            .group_by(Email.gm_thrid)
        )
        emails = (
            session.query(Email)
            .filter(Email.uid.in_([m.uid for m in threads]))
            .order_by(Email.date.desc())
        ).all()
        counts = {t.gm_thrid: t.count for t in threads}
    else:
        emails, counts = [], {}

    return env.render('emails.tpl', {
        'emails': emails,
        'emails_count': len(emails),
        'counts': counts,
        'label': label,
        'labels': get_labels()
    })
示例#12
0
def array_vectorizer(column):
    return func.array_to_string(column, ' ')
示例#13
0
    # Order by time:
    q = q.unique_join(Posi_Poly_Ext).unique_join(Inference_Ext).unique_join(
        Grd_Ext)
    q = q.order_by(Grd_Ext.starttime)

    if MockRequest.get("startdate"):
        q = q.filter(Grd_Ext.starttime >= MockRequest.get("startdate"))
    if MockRequest.get("enddate"):
        q = q.filter(Grd_Ext.starttime <= MockRequest.get("enddate"))
    if MockRequest.get("eez_sov"):
        q = q.unique_join(Posi_Poly_Ext).unique_join(
            Eez_Ext,
            func.ST_Intersects(Posi_Poly_Ext.geometry, Eez_Ext.geometry))
        q = q.filter(
            or_((func.array_to_string(Eez_Ext.sovereigns,
                                      "||").ilike(f"%{sov}%")
                 for sov in MockRequest.get("eez_sov"))))
    if MockRequest.get("min_score"):
        q = q.unique_join(Posi_Poly_Ext).unique_join(Coincident_Ext)
        q = q.filter(Coincident_Ext.score >= MockRequest.get("min_score"))
    if MockRequest.get("vessel_mmsi"):
        q = (q.unique_join(Posi_Poly_Ext).unique_join(
            Coincident_Ext).unique_join(Vessel_Ext))
        q = q.filter(
            or_((Vessel_Ext.mmsi == m
                 for m in MockRequest.get("vessel_mmsi"))))
    if MockRequest.get("vessel_flag"):
        q = (q.unique_join(Posi_Poly_Ext).unique_join(
            Coincident_Ext).unique_join(Vessel_Ext))
        q = q.filter(
            or_((Vessel_Ext.flag == m
示例#14
0
 def _ilike_in_str_array_sql_filter_(self, model_column, value, **kwargs):
     return func.array_to_string(model_column, '|').ilike(f'%{value}%')
示例#15
0
def users_with_groups():
    result = db.session.query(User, func.array_to_string(func.array_agg(func.distinct(Group.name)), ',').label('group_name'), func.count(Group.id)).join('groups').group_by(User).order_by(desc(User.name)).all()
    setJSONFormat()
    return jsonify(result)
示例#16
0
    def bulletin_query(self, q):
        query = []

        tsv = q.get('tsv')
        if tsv:
            words = tsv.split(' ')
            qsearch = [Bulletin.search.ilike('%{}%'.format(word)) for word in words]
            query.extend(qsearch)

        # exclude  filter
        extsv = q.get('extsv')
        if extsv:
            words = extsv.split(' ')
            for word in words:
                query.append(not_(Bulletin.search.ilike('%{}%'.format(word))))

        # ref
        ref = q.get('ref')

        if ref:
            search = ['%' + r + '%' for r in ref]
            #get serach operator
            op = q.get('opref', False)
            if op:
                query.append(or_(func.array_to_string(Bulletin.ref, '').ilike(r) for r in search))
            else:
                query.append(and_(func.array_to_string(Bulletin.ref, '').ilike(r) for r in search))

        exref = q.get('exref')
        if exref:
            # get operator
            opexref = q.get('opexref')
            if opexref:
                subq = Bulletin.query.filter(and_(Bulletin.ref.any(x) for x in exref)).with_entities('id')
                query.append(~Bulletin.id.in_(subq))
            else:
                query.extend([~Bulletin.ref.any(ref) for ref in exref])

        labels = q.get('labels', [])
        if len(labels):
            ids = [item.get('id') for item in labels]
            # children search ?
            recursive = q.get('childlabels', None)
            if q.get('oplabels'):
                # or operator
                if recursive:
                    # get ids of children // update ids 
                    result = Label.query.filter(Label.id.in_(ids)).all()
                    direct = [label for label in result]
                    all = direct + Label.get_children(direct)
                    #remove dups
                    all = list(set(all))
                    ids = [label.id for label in all]

                query.append(Bulletin.labels.any(Label.id.in_(ids)))
            else:
                # and operator (modify children search logic)
                if recursive:
                    direct = Label.query.filter(Label.id.in_(ids)).all()
                    for label in direct:
                        children = Label.get_children([label])
                        # add original label + uniquify list
                        children = list(set([label] + children))
                        ids = [child.id for child in children]
                        query.append(Bulletin.labels.any(Label.id.in_(ids)))

                else:
                    # non-recursive (apply and on all ids)
                    query.extend([Bulletin.labels.any(Label.id == id) for id in ids])

        # Excluded labels
        exlabels = q.get('exlabels', [])
        if len(exlabels):
            ids = [item.get('id') for item in exlabels]
            query.append(~Bulletin.labels.any(Label.id.in_(ids)))

        vlabels = q.get('vlabels', [])
        if len(vlabels):
            ids = [item.get('id') for item in vlabels]
            # children search ?
            recursive = q.get('childverlabels', None)
            if q.get('opvlabels'):
                # or operator
                if recursive:
                    # get ids of children // update ids
                    result = Label.query.filter(Label.id.in_(ids)).all()
                    direct = [label for label in result]
                    all = direct + Label.get_children(direct)
                    # remove dups
                    all = list(set(all))
                    ids = [label.id for label in all]

                query.append(Bulletin.ver_labels.any(Label.id.in_(ids)))
            else:
                # and operator (modify children search logic)
                if recursive:
                    direct = Label.query.filter(Label.id.in_(ids)).all()
                    for label in direct:
                        children = Label.get_children([label])
                        # add original label + uniquify list
                        children = list(set([label] + children))
                        ids = [child.id for child in children]
                        query.append(Bulletin.ver_labels.any(Label.id.in_(ids)))

                else:
                    # non-recursive (apply and on all ids)
                    query.extend([Bulletin.ver_labels.any(Label.id == id) for id in ids])

        # Excluded vlabels
        exvlabels = q.get('exvlabels', [])
        if len(exvlabels):
            ids = [item.get('id') for item in exvlabels]
            query.append(~Bulletin.ver_labels.any(Label.id.in_(ids)))

        sources = q.get('sources', [])
        if len(sources):
            ids = [item.get('id') for item in sources]
            # children search ?
            recursive = q.get('childsources', None)
            if q.get('opsources'):
                # or operator
                if recursive:
                    # get ids of children // update ids
                    result = Source.query.filter(Source.id.in_(ids)).all()
                    direct = [source for source in result]
                    all = direct + Source.get_children(direct)
                    # remove dups
                    all = list(set(all))
                    ids = [source.id for source in all]

                query.append(Bulletin.sources.any(Source.id.in_(ids)))
            else:
                # and operator (modify children search logic)
                if recursive:
                    direct = Source.query.filter(Source.id.in_(ids)).all()
                    for source in direct:
                        children = Source.get_children([source])
                        # add original label + uniquify list
                        children = list(set([source] + children))
                        ids = [child.id for child in children]
                        query.append(Bulletin.sources.any(Source.id.in_(ids)))

                else:
                    # non-recursive (apply and on all ids)
                    query.extend([Bulletin.sources.any(Source.id == id) for id in ids])

        # Excluded sources
        exsources = q.get('exsources', [])
        if len(exsources):
            ids = [item.get('id') for item in exsources]
            query.append(~Bulletin.sources.any(Source.id.in_(ids)))

        locations = q.get('locations', [])
        if len(locations):
            ids = [item.get('id') for item in locations]
            children = Location.find_by_ids(ids)
            all_ids =  [l.get('id')for l in children]

            if q.get('oplocations'):
                query.append(Bulletin.locations.any(Location.id.in_(all_ids)))
            else:
                for id in ids:
                    children = Location.find_by_ids([id])
                    all_ids = [l.get("id") for l in children]
                    query.append(Bulletin.locations.any(Location.id.in_(all_ids)))

                    
        # Excluded sources
        exlocations = q.get('exlocations', [])
        if len(exlocations):
            ids = [item.get('id') for item in exlocations]
            query.append(~Bulletin.locations.any(Location.id.in_(ids)))

        # event date
        edate = q.get('edate', None)
        edatewithin = q.get('edatewithin', None)
        if edate:
            if edatewithin in self.ACCEPTED_DATE_RANGES:
                diff = timedelta(days=int(edatewithin[:-1]))
                edate = parse(edate).date()
                query.append(Bulletin.events.any((func.date(Event.from_date) >= edate - diff) & (func.date(Event.from_date) <= edate + diff)))
            else:
                query.append(Bulletin.events.any(func.date(Event.from_date) == edate))

        elocation = q.get('elocation')
        if elocation:
            id = elocation.get('id', -1)
            query.append(Bulletin.events.any(Event.location_id.in_([id])))

        etype = q.get('etype', None)
        if etype:
            id = etype.get('id', -1)
            query.append(Bulletin.events.any(Event.eventtype_id == id))

        # publish date
        pubdate = q.get('pubdate', None)
        pubdatewithin = q.get('pubdatewithin', None)
        if pubdate:
            if pubdatewithin in self.ACCEPTED_DATE_RANGES:
                diff = timedelta(days=int(pubdatewithin[:-1]))
                pubdate = parse(pubdate).date()
                query.append((func.date(Bulletin.publish_date) >= pubdate - diff) & (func.date(Bulletin.publish_date) <= pubdate + diff))
            else:
                query.append(func.date(Bulletin.publish_date) == pubdate)

        # documentation date
        docdate = q.get('docdate', None)
        docdatewithin = q.get('docdatewithin', None)
        if docdate:
            if docdatewithin in self.ACCEPTED_DATE_RANGES:
                diff = timedelta(days=int(docdatewithin[:-1]))
                docdate = parse(docdate).date()
                query.append((func.date(Bulletin.documentation_date) >= docdate - diff) & (func.date(Bulletin.documentation_date) <= docdate + diff))
            else:
                query.append(func.date(Bulletin.documentation_date) == docdate)

        # creation date
        created = q.get('created', None)
        created_within = q.get('createdwithin', None)
        if created:
            if created_within in self.ACCEPTED_DATE_RANGES:
                diff = timedelta(days=int(created_within[:-1]))
                created = parse(created).date()
                query.append((func.date(Bulletin.created_at) >= created - diff) & (func.date(Bulletin.created_at) <= created + diff))
            else:
                query.append(func.date(Bulletin.created_at) == created)

        # modified date
        updated = q.get('updated', None)
        updated_within = q.get('updatedwithin', None)
        if updated:
            if updated_within in self.ACCEPTED_DATE_RANGES:
                diff = timedelta(days=int(updated_within[:-1]))
                updated = parse(updated).date()
                query.append((func.date(Bulletin.updated_at) >= updated - diff) & (func.date(Bulletin.updated_at) <= updated + diff))
            else:
                query.append(func.date(Bulletin.updated_at) == updated)
                

        # assigned user(s)
        assigned = q.get('assigned', [])
        if (assigned):
            query.append(Bulletin.assigned_to_id.in_(assigned))

        # unassigned
        unassigned = q.get('unassigned', None)
        if unassigned:
            query.append(Bulletin.assigned_to == None)

        # First peer reviewer
        fpr = q.get('reviewer', [])
        if fpr:
            query.append(Bulletin.first_peer_reviewer_id.in_(fpr))

        # workflow status
        status = q.get('status', None)
        if status:
            query.append(Bulletin.status == status)

        # review status
        review_action = q.get('reviewAction', None)
        if review_action:
            query.append(Bulletin.review_action == review_action)

        # Related to bulletin search
        rel_to_bulletin= q.get('rel_to_bulletin')
        if rel_to_bulletin:
            bulletin = Bulletin.query.get(int(rel_to_bulletin))
            if bulletin:
                ids = [b.get_other_id(bulletin.id) for b in bulletin.bulletin_relations]
                query.append(Bulletin.id.in_(ids))

        # Related to actor search
        rel_to_actor = q.get('rel_to_actor')
        if rel_to_actor:
            actor = Actor.query.get(int(rel_to_actor))
            if actor:
                ids = [b.bulletin_id for b in actor.bulletin_relations]
                query.append(Bulletin.id.in_(ids))

        # Related to incident search
        rel_to_incident = q.get('rel_to_incident')
        if rel_to_incident:
            incident = Incident.query.get(int(rel_to_incident))
            if incident:
                ids = [b.bulletin_id for b in incident.bulletin_relations]
                query.append(Bulletin.id.in_(ids))

        return query