def order_by_name(query, table, language=None, *extra_languages, **kwargs):
    """Order a query by name.

    query: The query to order
    table: Table of the named objects
    language: The language to order names by. If None, use the
        connection default.
    extra_languages: Extra languages to order by, should the translations for
        `language` be incomplete (or ambiguous).

    name_attribute (keyword argument): the attribute to use; defaults to 'name'

    Uses the identifier as a fallback ordering.
    """
    name_attribute = kwargs.pop('name', 'name')
    if kwargs:
        raise ValueError('Unexpected keyword arguments: %s' % list(kwargs.keys()))
    order_columns = []
    if language is None:
        query = query.outerjoin(table.names_local)
        order_columns.append(func.lower(getattr(table.names_table, name_attribute)))
    else:
        extra_languages = (language, ) + extra_languages
    for language in extra_languages:
        names_table = aliased(table.names_table)
        query = query.outerjoin(names_table)
        query = query.filter(names_table.foreign_id == table.id)
        query = query.filter(names_table.local_language_id == language.id)
        order_columns.append(func.lower(getattr(names_table, name_attribute)))
    order_columns.append(table.identifier)
    query = query.order_by(coalesce(*order_columns))
    return query

 def filter(self, query, q, **kwargs):
   if q:
     query = query.filter(
       or_(func.lower(User.first_name).like(q + "%"),
           func.lower(User.last_name).like(q + "%"))
     )
   return query

        def apply_filters(query):
            if 'filters' not in query_params:
                return query

            filters = query_params['filters']

            for key, val in filters.items():
                op, field_name = key.split('_', 1)

                try:
                    field = getattr(cls, field_name)
                except AttributeError:
                    log.warn('RestMixin.rest_get_list(): filter "%s=%s": unknown attribute %s',
                             key, val, field_name)
                    continue

                if op == 'e':
                    query = query.filter(field == val)
                elif op == 'n':
                    query = query.filter(or_(field == val, field == None))
                elif op == 'l':
                    query = query.filter(func.lower(field).like('%' + val.lower() + '%'))
                elif op == 's':
                    query = query.filter(func.lower(field).like(val.lower() + '%'))
                else:
                    log.error('get_for_rest_grid: filter "%s=%s": unknown op: %s' % (key, val, op))

            return query

 def get_filter(self, trans, user, column_filter):
     # Parse filter to extract multiple tags.
     if isinstance(column_filter, list):
         # Collapse list of tags into a single string; this is redundant but effective. TODO: fix this by iterating over tags.
         column_filter = ",".join(column_filter)
     raw_tags = trans.app.tag_handler.parse_tags(column_filter.encode("utf-8"))
     clause_list = []
     for name, value in raw_tags.items():
         if name:
             # Filter by individual's tag names.
             clause_list.append(
                 self.model_class.tags.any(
                     and_(
                         func.lower(self.model_tag_association_class.user_tname).like("%" + name.lower() + "%"),
                         self.model_tag_association_class.user == user,
                     )
                 )
             )
             if value:
                 # Filter by individual's tag values.
                 clause_list.append(
                     self.model_class.tags.any(
                         and_(
                             func.lower(self.model_tag_association_class.user_value).like("%" + value.lower() + "%"),
                             self.model_tag_association_class.user == user,
                         )
                     )
                 )
     return and_(*clause_list)

 def sort( self, trans, query, ascending, column_name=None ):
     """ Sort column using case-insensitive alphabetical sorting on item's username. """
     if ascending:
         query = query.order_by( func.lower( self.model_class.username ).asc() )
     else:
         query = query.order_by( func.lower( self.model_class.username ).desc() )
     return query

def exact_name_lookup(card, edition=None):
    cquery = session.query(CardInfo).filter(func.lower(CardInfo.name)==func.lower(card))
    if edition is not None:
        cquery = cquery.filter(func.lower(CardInfo.edition)==func.lower(edition))
    cards = cquery.all();    
    if cards == []:
        print 'not found:', card
    return cards[0:1]

def quicksearch(request):
    message = None
    query = DBSession.query(Languoid)
    term = request.params['search'].strip()
    titlecase = term.istitle()
    term = term.lower()
    params = {'iso': '', 'country': '',
        'name': '', 'namequerytype': 'part', 'multilingual': ''}

    if not term:
        query = None
    elif len(term) < 3:
        query = None
        message = ('Please enter at least four characters for a name search '
            'or three characters for an iso code')
    elif len(term) == 3 and not titlecase:
        query = query.filter(Languoid.identifiers.any(
            type=IdentifierType.iso.value, name=term))
        kind = 'ISO 639-3'
    elif len(term) == 8 and GLOTTOCODE_PATTERN.match(term):
        query = query.filter(Languoid.id == term)
        kind = 'Glottocode'
    else:
        _query = query.filter(func.lower(Languoid.name) == term)
        if DBSession.query(_query.exists()).scalar():
            query = _query
        else:
            query = query.filter(or_(
                func.lower(Languoid.name).contains(term),
                Languoid.identifiers.any(and_(
                    Identifier.type == u'name',
                    Identifier.description == Languoid.GLOTTOLOG_NAME,
                    func.lower(Identifier.name).contains(term)))))

        kind = 'name part'
        params['name'] = term

    if query is None:
        languoids = []
    else:
        languoids = query.order_by(Languoid.name)\
            .options(joinedload(Languoid.family)).all()
        if not languoids:
            term_pre = HTML.kbd(term, style='white-space: pre')
            message = 'No matching languoids found for %s "' % kind + term_pre + '"'
        elif len(languoids) == 1:
            raise HTTPFound(request.resource_url(languoids[0]))

    map_, icon_map, family_map = get_selected_languages_map(request, languoids)
    layer = list(map_.get_layers())[0]
    if not layer.data['features']:
        map_ = None

    countries = json.dumps(['%s (%s)' % (c.name, c.id) for c in
        DBSession.query(Country).order_by(Country.description)])

    return {'message': message, 'params': params, 'languoids': languoids,
        'map': map_, 'countries': countries}

def json_search():
    query = request.args['query']
    return jsonify(users=map(
        lambda u: {"id": u.id, "name": u.name},
        session.query(User.id, User.name).filter(or_(
            func.lower(User.name).like(func.lower(u"%{}%".format(query))),
            User.id.like(u"{}%".format(query))
        ))
    ))

    def data(self, *args, **kw):
        security = get_service("security")
        length = int(kw.get("iDisplayLength", 0))
        start = int(kw.get("iDisplayStart", 0))
        sort_dir = kw.get("sSortDir_0", "asc")
        echo = int(kw.get("sEcho", 0))
        search = kw.get("sSearch", "").replace("%", "").strip().lower()

        end = start + length
        query = Group.query.options(sa.orm.noload("*"))
        total_count = query.count()

        if search:
            # TODO: gérer les accents
            query = query.filter(func.lower(Group.name).like("%" + search + "%"))

        count = query.count()
        columns = [func.lower(Group.name)]
        direction = asc if sort_dir == "asc" else desc
        order_by = list(map(direction, columns))

        # sqlite does not support 'NULLS FIRST|LAST' in ORDER BY clauses
        engine = query.session.get_bind(Group.__mapper__)
        if engine.name != "sqlite":
            order_by[0] = nullslast(order_by[0])

        query = query.order_by(*order_by).add_columns(Group.members_count)
        groups = query.slice(start, end).all()
        data = []

        for group, members_count in groups:
            # TODO: this should be done on the browser.
            group_url = url_for(".groups_group", group_id=group.id)
            name = html.escape(group.name or "")
            roles = [r for r in security.get_roles(group) if r.assignable]

            columns = [
                f'<a href="{group_url}">{name}</a>',
                str(members_count or 0),
                render_template_string(
                    """{%- for role in roles %}
                        <span class="badge badge-default">{{ role }}</span>
                        {%- endfor %}""",
                    roles=roles,
                ),
                "\u2713" if group.public else "",
            ]

            data.append(columns)

        return {
            "sEcho": echo,
            "iTotalRecords": total_count,
            "iTotalDisplayRecords": count,
            "aaData": data,
        }

  def data(self, *args, **kw):
    security = current_app.services['security']
    length = int(kw.get("iDisplayLength", 0))
    start = int(kw.get("iDisplayStart", 0))
    sort_dir = kw.get("sSortDir_0", "asc")
    echo = int(kw.get("sEcho", 0))
    search = kw.get("sSearch", "").replace("%", "").strip().lower()

    end = start + length
    q = Group.query \
      .options(sa.orm.noload('*'))
    total_count = q.count()

    if search:
      # TODO: gérer les accents
      q = q.filter(func.lower(Group.name).like("%" + search + "%"))

    count = q.count()
    columns = [func.lower(Group.name)]
    direction = asc if sort_dir == 'asc' else desc
    order_by = map(direction, columns)

    # sqlite does not support 'NULLS FIRST|LAST' in ORDER BY clauses
    engine = q.session.get_bind(Group.__mapper__)
    if engine.name != 'sqlite':
      order_by[0] = nullslast(order_by[0])

    q = q.order_by(*order_by) \
      .add_columns(Group.members_count)
    groups = q.slice(start, end).all()
    data = []

    for group, members_count in groups:
      # TODO: this should be done on the browser.
      group_url = url_for(".groups_group", group_id=group.id)
      name = escape(getattr(group, "name") or "")
      roles = [r for r in security.get_roles(group) if r.assignable]
      columns = []
      columns.append(
        u'<a href="{url}">{name}</a>'.format(url=group_url, name=name)
      )
      columns.append(unicode(members_count or 0))
      columns.append(render_template_string(
        u'''{%- for role in roles %}
            <span class="badge badge-default">{{ role }}</span>
            {%- endfor %}''',
        roles=roles))
      columns.append(u'\u2713' if group.public else u'')
      data.append(columns)

    return {
      "sEcho": echo,
      "iTotalRecords": total_count,
      "iTotalDisplayRecords": count,
      "aaData": data,
    }

    def rss_category(self):
        category = func.lower(self.request.matchdict['category'])
        query = self.Entry.get_all_active(self.request)
        query = query.join(self.Category)
        query = query.filter(
            func.lower(self.Category.title) ==  category
        )
        entries = query.all()

        return self.get_rss_data(entries)

 def wildcard_handler(col, wc_term):
     if wc_term.startswith('*') and not wc_term.endswith('*'):
         #postfix == endswith
         wc_term = remove_prefix(wc_term, prefix='*')
         return func.lower(col).endswith(wc_term)
     elif wc_term.startswith('*') and wc_term.endswith('*'):
         #wildcard == ilike
         wc_term = remove_prefix(wc_term, prefix='*')
         wc_term = remove_suffix(wc_term, suffix='*')
         return func.lower(col).contains(wc_term)

 def search_all_members(self, query, limit=10):
     """
     Searches for members from this place or places below this by name, phone or email.
     :param query: search query
     :return: List of object Member
     """
     like_q = "%{0}%".format(query.lower())
     q1 = Member.query.filter(
         place_parents.c.child_id == Member.place_id,
         place_parents.c.parent_id == self.id,
         (func.lower(Member.name).like(like_q) | Member.phone.like(like_q) | func.lower(Member.email).like(
             like_q))).limit(limit)
     return q1.all()

 def findCard(self, card, edition=None):
     cquery = datasources.database.db.session.query(CardInfo).filter(func.lower(CardInfo.name) == func.lower(card))
     edition = self.getRealEditionName(edition)
     if edition is not None:
         cquery = cquery.filter(func.lower(CardInfo.edition) == func.lower(edition))
     cards = cquery.all()
     if not cards:
         logger.warn('not found: %s', card)
     if len(cards) > 1 and self.forceSingleMatch:
         logger.warn('more than one matches found for card: {card}, selecting first match: {ed}'.format(card=card,
                     ed=cards[0].edition))
         return cards[0:1]
     return cards

    def get_by_name_or_email(self, name_or_email):
        """Get a user by name or email

        """
        User = tables.User
        user = (
            self.session
            .query(User)
            .filter(or_(
                func.lower(User.user_name) == name_or_email.lower(),
                func.lower(User.email) == name_or_email.lower()
            ))
        )
        return user.first()

        def apply_search(query):
            search_columns = getattr(cls, '_rest_search_columns', None)
            if not search_columns or not 'search' in query_params:
                return query

            search = query_params['search'].lower()

            if len(search_columns) == 1:
                col = search_columns[0]
                search_filter = func.lower(col).like('%' + search + '%')
            else:  # > 1
                clauses = [func.lower(col).like('%' + search + '%') for col in search_columns]
                search_filter = or_(*clauses)

            return query.filter(search_filter)

    def auto_movie(self, q):
        orig_q = q
        q = q.lstrip().lower().split(' ')
        full_words, q = q[:-1], q[-1]
        if 'the' in full_words:
            full_words.remove('the')

        target = literal(' ').op('||')(Movie.title)

        filters = []
        for word in full_words:
            filters.append(target.ilike('%% %s %%' % word))

        filters.append(target.ilike('%% %s%%' % q))
        if len(filters) > 1:
            filters = and_(*filters)
        else:
            filters = filters[0]


        res = self.session.query(Movie.id, Movie.title, Rating.rating)\
                  .outerjoin((Rating, and_(Rating.movie_id == Movie.id,
                                           Rating.user == self.user)))\
                  .filter(filters)\
                  .order_by(func.similarity(func.lower(target), orig_q).desc())\
                  .limit(7).all()
        self.return_json(res)

    def show(self, group_name, format='html'):
        """GET /repos_groups/group_name: Show a specific item"""
        # url('repos_group', group_name=GROUP_NAME)

        c.group = c.repos_group = ReposGroupModel()._get_repo_group(group_name)
        c.group_repos = c.group.repositories.all()

        #overwrite our cached list with current filter
        gr_filter = c.group_repos
        c.repo_cnt = 0

        groups = RepoGroup.query().order_by(RepoGroup.group_name)\
            .filter(RepoGroup.group_parent_id == c.group.group_id).all()
        c.groups = self.scm_model.get_repos_groups(groups)

        c.repos_list = Repository.query()\
                        .filter(Repository.group_id == c.group.group_id)\
                        .order_by(func.lower(Repository.repo_name))\
                        .all()

        repos_data = RepoModel().get_repos_as_dict(repos_list=c.repos_list,
                                                   admin=False)
        #json used to render the grid
        c.data = json.dumps(repos_data)

        return render('admin/repos_groups/repos_groups.html')

    def index(self):
        # Return a rendered template
        p = int(request.params.get('page', 1))

        c.user = User.get(self.rhodecode_user.user_id)
        all_repos = self.sa.query(Repository)\
                     .filter(Repository.user_id == c.user.user_id)\
                     .order_by(func.lower(Repository.repo_name)).all()

        c.user_repos = ScmModel().get_repos(all_repos)

        c.following = self.sa.query(UserFollowing)\
            .filter(UserFollowing.user_id == self.rhodecode_user.user_id)\
            .options(joinedload(UserFollowing.follows_repository))\
            .all()

        journal = self._get_journal_data(c.following)

        c.journal_pager = Page(journal, page=p, items_per_page=20)

        c.journal_day_aggreagate = self._get_daily_aggregate(c.journal_pager)

        c.journal_data = render('journal/journal_data.html')
        if request.environ.get('HTTP_X_PARTIAL_XHR'):
            return c.journal_data
        return render('journal/journal.html')

def critic():
    """ Return all movies reviewed by a given critic.

    Takes in name and publication as JSON parameters."""

    name = request.args.get('name')
    publication = request.args.get('publication')
    if not name:
        abort(400)
    query = session.query(Critic).filter(func.lower(Critic.name) == func.lower(name))
    if publication:
        query = query.filter(func.lower(Critic.publication) == func.lower(publication))

    critic = query.first()
    reviews = [review.to_json() for review in critic.reviews] if critic else []
    return jsonify(reviews=reviews)