def fast_query(self, categories, start, radius_m):
        """ Experimental fast sql query using only two CTE queries for all requested POIs
            This approach was tested (EXPLAIN ANALYZE) to be faster than using osm_poi because the osm_poi view uses UNION instead of UNION ALL (needs re-sort)
                WITH my_cte AS ( ... our cte with radius constraint on osm_point and osm_polygon with UNION ALL ) SELECT ... UNION SELECT ... UNION SELECT ...
        """

        from sqlalchemy import select, literal, union_all
        from app import db
        loc = geoalchemy2.shape.from_shape(start, 4326).ST_Transform(Point.SRID)
        lookup = {hash(c.original_id): c for c in categories}

        q_points = select([Point.name, Point.osm_id, Point.way, Point.tags, Point.way.ST_Transform(4326).ST_X().label(
            'lon'), Point.way.ST_Transform(4326).ST_Y().label('lat'), literal(True).label('is_point')]).where(Point.way.ST_DWithin(loc, radius_m))
        q_polygons = select([Polygon.name, Polygon.osm_id, Polygon.way, Polygon.tags, Polygon.way.ST_Transform(4326).ST_Centroid().ST_X().label(
            'lon'), Polygon.way.ST_Transform(4326).ST_Centroid().ST_Y().label('lat'), literal(False).label('is_point')]).where(Polygon.way.ST_DWithin(loc, radius_m))

        cte = union_all(q_points, q_polygons).cte()

        unions = []
        for id_hash, category in lookup.iteritems():
            cond = category._get_condition(cte.c)
            query = select([cte.c.name, cte.c.osm_id, cte.c.lon, cte.c.lat, cte.c.is_point, literal(id_hash).label('c_id')]).where(cond)
            unions.append(query)
        inner_query = union_all(*unions)
        results = db.session.execute(inner_query).fetchall()

        points = []
        for name, osm_id, lon, lat, is_point, cat_id in results:
            points.append(OsmPoi(lookup[cat_id], name, lon, lat, osm_id, is_point))

        return points

    def main(self):
        incomplete_builds = self.db.query(Build.package_id)\
                                .filter(Build.state == Build.RUNNING)
        if incomplete_builds.count() >= self.max_builds:
            return
        queries = self.get_priority_queries().values()
        union_query = union_all(*queries).alias('un')
        pkg_id = union_query.c.pkg_id
        current_priority = func.sum(union_query.c.priority)\
                               .label('curr_priority')
        candidates = self.db.query(pkg_id, current_priority)\
                            .having(current_priority >=
                                    self.priority_threshold)\
                            .group_by(pkg_id).subquery()
        to_schedule = self.db.query(Package, candidates.c.curr_priority)\
                             .join(candidates,
                                   Package.id == candidates.c.pkg_id)\
                             .filter((Package.resolved == True) |
                                     (Package.resolved == None))\
                             .filter(Package.id.notin_(
                                 incomplete_builds.subquery()))\
                             .filter(Package.ignored == False)\
                             .order_by(candidates.c.curr_priority.desc())\
                             .first()

        if to_schedule:
            if util.get_koji_load(self.koji_session) > self.load_threshold:
                return
            package, priority = to_schedule
            self.log.info('Scheduling build for {}, priority {}'
                          .format(package.name, priority))
            self.backend.submit_build(package)
            self.db.commit()

    def get_next_to_translate(self, session):
        """ Use the milestones and priority to find the next description to translate """
        # This is the query we want:
        # select description_id from (
        #         select description_id, 50 as score from languages_tb join description_milestone_tb on (milestone_high=milestone) where language = 'nl'
        # union all
        #         select description_id, 30 from languages_tb join description_milestone_tb on (milestone_medium=milestone) where language = 'nl'
        # union all
        #         select description_id, 10 from languages_tb join description_milestone_tb on (milestone_low=milestone) where language = 'nl'
        # union all
        #         select description_id, prioritize from description_tb
        # ) x
        # where not exists (select 1 from translation_tb where translation_tb.description_id = x.description_id)
        # group by description_id order by sum(score) desc
        # limit 1;
        lang_cte = session.query(Languages).filter_by(language=self.language).cte("language")

        prio = session.query(Description.description_id, Description.prioritize)
        high = session.query(DescriptionMilestone.description_id, literal(50).label("prioritize")).join(lang_cte, lang_cte.c.milestone_high==DescriptionMilestone.milestone)
        medium = session.query(DescriptionMilestone.description_id, literal(30).label("prioritize")).join(lang_cte, lang_cte.c.milestone_medium==DescriptionMilestone.milestone)
        low = session.query(DescriptionMilestone.description_id, literal(10).label("prioritize")).join(lang_cte, lang_cte.c.milestone_low==DescriptionMilestone.milestone)

        prio_cte = union_all(prio, high, medium, low).cte()

        q = session.query(prio_cte.c.description_tb_description_id). \
                    filter(~exists([1], Translation.description_id == prio_cte.c.description_tb_description_id)). \
                    filter(~exists([1], PendingTranslation.description_id == prio_cte.c.description_tb_description_id)). \
                    group_by(prio_cte.c.description_tb_description_id). \
                    order_by(func.sum(prio_cte.c.description_tb_prioritize).desc())

        row = q.first()
        if row:
            return row[0]

  def _similar_asmnt_issue(cls, type_, id_):
    """Find similar Issues for Assessment.

    Args:
        type_: Assessment type.
        id_: Assessment id.

    Returns:
        SQLAlchemy query that yields results [(similar_id,)] - the id of
        similar objects.
    """
    mapped_obj = cls.mapped_to_assessment([id_]).subquery()
    similar_queries = cls.mapped_to_obj_snapshot(
        mapped_obj.c.obj_type, mapped_obj.c.obj_id
    )
    mapped_related = cls.mapped_objs(
        mapped_obj.c.obj_type, mapped_obj.c.obj_id
    )
    similar_queries.append(
        db.session.query(
            mapped_related.c.obj_id.label("similar_id"),
            mapped_related.c.obj_type.label("similar_type"),
            mapped_related.c.base_type.label("related_type"),
        )
    )
    similar_objs = sa.union_all(*similar_queries).alias("scoped_similar")
    return db.session.query(similar_objs.c.similar_id).filter(
        similar_objs.c.similar_type == type_,
    )

def update():
    g.user.info = json.loads(g.user.info)

    #get oldest and lateset status in database
    query_latest = db.session.query(WeiboList).filter(WeiboList.user_uid == g.user.uid)\
        .order_by(WeiboList.created_at.desc()).limit(1).subquery().select()
    query_oldest = db.session.query(WeiboList).filter(WeiboList.user_uid == g.user.uid)\
        .order_by(WeiboList.created_at).limit(1).subquery().select()
    query = db.session.query(WeiboList).select_from(union_all(query_latest,query_oldest)).order_by(WeiboList.created_at)
    records = query.all()
    oldest_datetime = records[0].created_at
    latest_datetime = records[1].created_at
    latest_uid = records[1].uid

    # get total count in database
    total_count_in_database = db.session.query(func.count(WeiboList)) \
        .filter(WeiboList.user_uid == g.user.uid).first()[0]

    # get total count of update status
    token = json.loads(g.user.token)
    client.set_access_token(token['access_token'], token['expires'])
    statuses = client.statuses.user_timeline.get(count=10, page=1, since_id=latest_uid)
    total_count = statuses['total_number']

    page_info = {
        "oldest_date": oldest_datetime.date().isoformat(),
        "latest_date": latest_datetime.date().isoformat(),
        "total_count_in_database": total_count_in_database,
        "total_count_for_update": total_count - total_count_in_database
    }

    return render_template('update.html', user=g.user, current_navi="update", page_info=page_info)

    def test_distinct(self):
        users, items, order_items, orders, \
            Item, User, Address, Order, addresses = (
                self.tables.users,
                self.tables.items,
                self.tables.order_items,
                self.tables.orders,
                self.classes.Item,
                self.classes.User,
                self.classes.Address,
                self.classes.Order,
                self.tables.addresses)

        mapper(Item, items)
        mapper(Order, orders, properties={
            'items': relationship(Item, secondary=order_items, lazy='select')
        })
        mapper(User, users, properties={
            'addresses': relationship(
                mapper(Address, addresses), lazy='select'),
            'orders': relationship(Order, lazy='select')
        })

        sess = create_session()
        q = sess.query(User)

        # use a union all to get a lot of rows to join against
        u2 = users.alias('u2')
        s = sa.union_all(
            u2.select(use_labels=True),
            u2.select(use_labels=True), u2.select(use_labels=True)).alias('u')
        result = q.filter(s.c.u2_id == User.id).order_by(User.id).distinct() \
            .all()
        eq_(self.static.user_all_result, result)

    def test_distinct(self):
        users, items, order_items, orders, Item, User, Address, Order, addresses = (
            self.tables.users,
            self.tables.items,
            self.tables.order_items,
            self.tables.orders,
            self.classes.Item,
            self.classes.User,
            self.classes.Address,
            self.classes.Order,
            self.tables.addresses,
        )

        mapper(Item, items)
        mapper(Order, orders, properties={"items": relationship(Item, secondary=order_items, lazy="select")})
        mapper(
            User,
            users,
            properties={
                "addresses": relationship(mapper(Address, addresses), lazy="select"),
                "orders": relationship(Order, lazy="select"),
            },
        )

        sess = create_session()
        q = sess.query(User)

        # use a union all to get a lot of rows to join against
        u2 = users.alias("u2")
        s = sa.union_all(u2.select(use_labels=True), u2.select(use_labels=True), u2.select(use_labels=True)).alias("u")
        l = q.filter(s.c.u2_id == User.id).order_by(User.id).distinct().all()
        eq_(self.static.user_all_result, l)

  def _similar_obj_assessment(cls, type_, id_):
    """Find similar Assessments for object.

    Args:
        type_: Object type.
        id_: Object id.

    Returns:
        SQLAlchemy query that yields results [(similar_id,)] - the id of
        similar objects.
    """
    from ggrc.models import all_models
    # Find objects directly mapped to Snapshot of base object
    # Object1 <-> Snapshot of Object1 <-> Object2
    similar_queries = cls.mapped_to_obj_snapshot(cls.__name__, id_)

    # Find objects mapped to Snapshot of base object through another object
    # Object1 <-> Object2 <-> Snapshot of Object2 <-> Object3
    mapped_obj = cls.mapped_objs(cls.__name__, id_, True)
    similar_queries += cls.mapped_to_obj_snapshot(
        mapped_obj.c.obj_type, mapped_obj.c.obj_id
    )
    similar_objs = sa.union_all(*similar_queries).alias("similar_objs")
    return db.session.query(similar_objs.c.similar_id).join(
        all_models.Assessment,
        sa.and_(
            all_models.Assessment.assessment_type == cls.__name__,
            all_models.Assessment.id == similar_objs.c.similar_id,
        )
    ).filter(
        similar_objs.c.similar_type == type_,
    )

def _queryNewsVolumes(aggStartDatetime, aggStopDatetime):
  """ Query the database for the counts of security releases+headlines for each
  company that were detected during the specified time window.

  :param aggStartDatetime: inclusive start of aggregation interval as
    UTC datetime
  :param aggStopDatetime: non-inclusive upper bound of aggregation interval as
    UTC datetime
  :returns: a sparse sequence of two-tuples: (symbol, count); companies that
    have no detected news in the given aggregation period will be absent from
    the result.
  """
  headlineSel = sql.select(
    [schema.xigniteSecurityHeadline.c.symbol.label("symbol")]
    ).where(
      (schema.xigniteSecurityHeadline.c.discovered_at >= aggStartDatetime) &
      (schema.xigniteSecurityHeadline.c.discovered_at < aggStopDatetime))

  releaseSel = sql.select(
    [schema.xigniteSecurityRelease.c.symbol]
    ).where(
      (schema.xigniteSecurityRelease.c.discovered_at >= aggStartDatetime) &
      (schema.xigniteSecurityRelease.c.discovered_at < aggStopDatetime))

  allNewsUnion = sql.union_all(headlineSel, releaseSel)

  aggSel = sql.select(
    ["symbol", sql.func.count("symbol").label("sym_count")]
    ).select_from(allNewsUnion.alias("union_of_tables")
    ).group_by("symbol")

  return collectorsdb.engineFactory().execute(aggSel).fetchall()

    def union(self, tables):
        from sqlalchemy import union_all

        if type(tables) is list:
            datas = [self.data] + map(lambda x: x.data, tables)
        else:
            datas = [self.data, tables.data]
        return Table(self.ctx, gen_table_name(), union_all(*datas))#, self.data.c)

def _activities_union_all(*qlist):
    '''
    Return union of two or more activity queries sorted by timestamp,
    and remove duplicates
    '''
    import ckan.model as model
    return model.Session.query(model.Activity).select_entity_from(
        union_all(*[q.subquery().select() for q in qlist])
        ).distinct(model.Activity.timestamp)

    def test_union_all(self):
        e = union_all(select([t1.c.col3]), union(select([t1.c.col3]), select([t1.c.col3])))

        wanted = [("aaa",), ("aaa",), ("bbb",), ("bbb",), ("ccc",), ("ccc",)]
        found1 = self._fetchall_sorted(e.execute())
        eq_(found1, wanted)

        found2 = self._fetchall_sorted(e.alias("foo").select().execute())
        eq_(found2, wanted)

  def mapped_to_assessment(cls, related_ids):
    """Collect objects that have snapshot mapped to assessment.

    Args:
        related_ids: List of Assessment ids.

    Returns:
        SQLAlchemy query with id and type of found
        objects [(obj_id, obj_type)].
    """
    from ggrc.models import all_models
    asmnt = all_models.Assessment

    objects_mapped = sa.union_all(
        db.session.query(
            Snapshot.child_id.label("obj_id"),
            asmnt.assessment_type.label("obj_type"),
        ).join(
            Relationship,
            sa.and_(
                Relationship.source_id == Snapshot.id,
                Relationship.source_type == Snapshot.__name__,
            )
        ).join(
            asmnt,
            sa.and_(
                Relationship.destination_type == asmnt.__name__,
                Relationship.destination_id == asmnt.id,
            )
        ).filter(
            asmnt.id.in_(related_ids),
            Snapshot.child_type == asmnt.assessment_type,
        ),
        db.session.query(
            Snapshot.child_id.label("obj_id"),
            asmnt.assessment_type.label("obj_type"),
        ).join(
            Relationship,
            sa.and_(
                Relationship.destination_id == Snapshot.id,
                Relationship.destination_type == Snapshot.__name__,
            )
        ).join(
            asmnt,
            sa.and_(
                Relationship.source_type == asmnt.__name__,
                Relationship.source_id == asmnt.id,
            )
        ).filter(
            asmnt.id.in_(related_ids),
            Snapshot.child_type == asmnt.assessment_type,
        )
    ).alias("objects_mapped")
    return db.session.query(
        objects_mapped.c.obj_id.label("obj_id"),
        objects_mapped.c.obj_type.label("obj_type")
    )

 def invalid_query(self, session):
     cte = session.query(Languoid.pk,
             Languoid.pk.label('father_pk'), sa.literal(0).label('depth'))\
         .cte(recursive=True)
     parent = sa.orm.aliased(Languoid)
     cte = cte.union_all(session.query(cte.c.pk, parent.father_pk, cte.c.depth + 1)\
         .join(parent, cte.c.father_pk == parent.pk)\
         .filter(parent.father_pk != None))
     tree1 = session.query(TreeClosureTable.child_pk, TreeClosureTable.parent_pk, TreeClosureTable.depth)
     tree2 = session.query(cte.c.pk, cte.c.father_pk, cte.c.depth)
     diff = sa.union_all(tree1.except_all(tree2), tree2.except_all(tree1))
     return session.query(diff.alias())

    def get(self):
        query = self.get_argument("query", "")
        offset = int(self.get_argument("offset", 0))
        limit = int(self.get_argument("limit", 100))
        if limit > 9000:
            limit = 9000

        groups = (
            self.session.query(
                label("type", literal("Group")),
                label("id", Group.id),
                label("name", Group.groupname),
            )
            .filter(Group.enabled == True, Group.groupname.like("%{}%".format(query)))
            .subquery()
        )

        permissions = (
            self.session.query(
                label("type", literal("Permission")),
                label("id", Permission.id),
                label("name", Permission.name),
            )
            .filter(Permission.enabled == True, Permission.name.like("%{}%".format(query)))
            .subquery()
        )

        users = (
            self.session.query(
                label("type", literal("User")), label("id", User.id), label("name", User.username)
            )
            .filter(User.enabled == True, User.username.like("%{}%".format(query)))
            .subquery()
        )

        results_query = self.session.query("type", "id", "name").select_entity_from(
            union_all(users.select(), permissions.select(), groups.select())
        )
        total = results_query.count()
        results = results_query.offset(offset).limit(limit).all()

        if len(results) == 1:
            result = results[0]
            return self.redirect("/{}s/{}".format(result.type.lower(), result.name))

        self.render(
            "search.html",
            results=results,
            search_query=query,
            offset=offset,
            limit=limit,
            total=total,
        )

def glottocode(name, conn, codes=None):
    letters = slug(name)[:4].ljust(4, 'a')
    active = select([cast(func.substring(Languoid.id, 5), Integer).label('number')])\
        .where(Languoid.id.startswith(letters))
    legacy = select([cast(func.substring(LegacyCode.id, 5), Integer).label('number')])\
        .where(LegacyCode.id.startswith(letters))
    if not codes:
        known = union_all(active, legacy)
    else:
        dirty = select([cast(func.substring(literal_column('dirty'), 5), Integer).label('number')])\
            .select_from(func.unnest(list(codes)).alias('dirty'))\
            .where(literal_column('dirty').startswith(letters))
        known = union_all(active, legacy, dirty)
    number = conn.execute(select([func.coalesce(func.max(literal_column('number') + 1), 1234)])\
        .select_from(known.alias())).scalar()
    number = str(number)
    assert len(number) == 4
    res = letters + number
    assert GLOTTOCODE_PATTERN.match(res)
    if codes is not None:
        codes[res] = True
    return res

    def test_union_all(self):
        e = union_all(
            select([t1.c.col3]),
            union(
                select([t1.c.col3]),
                select([t1.c.col3]),
            )
        )

        wanted = [('aaa',), ('aaa',), ('bbb',), ('bbb',), ('ccc',), ('ccc',)]
        found1 = self._fetchall_sorted(e.execute())
        eq_(found1, wanted)

        found2 = self._fetchall_sorted(e.alias('foo').select().execute())
        eq_(found2, wanted)

 def join_committee_queries(self, kwargs):
     queries = []
     total = 0
     for committee_id in kwargs['committee_id']:
         query, count = self.build_committee_query(kwargs, committee_id)
         queries.append(query.subquery().select())
         total += count
     query = models.db.session.query(
         models.ScheduleA
     ).select_entity_from(
         sa.union_all(*queries)
     )
     query = query.options(sa.orm.joinedload(models.ScheduleA.committee))
     query = query.options(sa.orm.joinedload(models.ScheduleA.contributor))
     return query, total

 def join_committee_queries(self, kwargs):
     """Build and compose per-committee subqueries using `UNION ALL`.
     """
     queries = []
     total = 0
     for committee_id in kwargs.get('committee_id', []):
         query, count = self.build_committee_query(kwargs, committee_id)
         queries.append(query.subquery().select())
         total += count
     query = models.db.session.query(
         self.model
     ).select_entity_from(
         sa.union_all(*queries)
     )
     query = query.options(*self.query_options)
     return query, total

def argos_unchecked_list(request):
    """Returns the unchecked sensor data summary.
    """
    # SQL query
    unchecked = union_all(
        select([
            Argos.pk,
            Argos.ptt.label('ptt'),
            Argos.date,
            literal('argos/gps').label('type')
        ]).where(Argos.checked == False),
        select([
            Gps.pk,
            Gps.ptt.label('ptt'),
            Gps.date,
            literal('argos/gps').label('type')
        ]).where(Gps.checked == False),
        select([
            Gsm.pk_id,
            Gsm.fk_ptt.label('ptt'),
            Gsm.date,
            literal('gsm').label('type')
        ]).where(Gsm.checked == False)
    ).alias()
    # Add the bird associated to each ptt.
    pie = ProtocolIndividualEquipment
    unchecked_with_ind = select([
        pie.ind_id.label('ind_id'),
        'ptt',
        func.count().label('count'),
        'type'
    ]).select_from(
        unchecked.join(SatTrx, SatTrx.ptt == unchecked.c.ptt)
        .outerjoin(
            pie,
            and_(SatTrx.id == pie.sat_id,
                 unchecked.c.date >= pie.begin_date,
                 or_(
                     unchecked.c.date < pie.end_date,
                     pie.end_date == None
                )
            )
        )
    ).group_by('ptt', 'type', pie.ind_id)#.order_by('ptt')
    # Populate Json array
    data = DBSession.execute(unchecked_with_ind).fetchall()
    return [dict(row) for row in data]