def results_by_district(self):
        """ Returns the results aggregated by the distict.  """

        counted = func.coalesce(func.bool_and(BallotResult.counted), False)
        yeas = func.sum(BallotResult.yeas)
        nays = func.sum(BallotResult.nays)
        yeas_percentage = 100 * yeas / (
            cast(func.coalesce(func.nullif(yeas + nays, 0), 1), Float)
        )
        nays_percentage = 100 - yeas_percentage
        accepted = case({True: yeas > nays}, counted)
        results = self.results.with_entities(
            BallotResult.district.label('name'),
            counted.label('counted'),
            accepted.label('accepted'),
            yeas.label('yeas'),
            nays.label('nays'),
            yeas_percentage.label('yeas_percentage'),
            nays_percentage.label('nays_percentage'),
            func.sum(BallotResult.empty).label('empty'),
            func.sum(BallotResult.invalid).label('invalid'),
            func.sum(BallotResult.eligible_voters).label('eligible_voters'),
            func.array_agg(BallotResult.entity_id).label('entity_ids')
        )
        results = results.group_by(BallotResult.district)
        results = results.order_by(None).order_by(BallotResult.district)
        return results

def packages_all_licenses_in_files(package_id):
    pac = db.packages.alias()
    pfi = db.packages_files.alias()
    fil = db.files.alias()
    fli = db.files_licenses.alias()
    lic1 = license_approved_names().alias()
    lic2 = license_approved_names().alias()
    return (select([
        pac.c.package_id,
        pac.c.name              .label('package_name'),
        func.coalesce(lic1.c.short_name, lic2.c.short_name).label('license_short_name'),
        func.count()            .label('license_found_count')
        ])
    .select_from(
        pac
        .join(pfi, pac.c.package_id == pfi.c.package_id)
        .join(fil, pfi.c.file_id == fil.c.file_id)
        .join(lic1, pfi.c.concluded_license_id == lic1.c.license_id, isouter=True)
        .join(fli, fil.c.file_id == fli.c.file_id, isouter=True)
        .join(lic2, fli.c.license_id == lic2.c.license_id)
        )
    .where(pac.c.package_id == package_id)
    .group_by(
        pac.c.package_id,
        pac.c.name,
        func.coalesce(lic1.c.short_name, lic2.c.short_name)
        )
    )

    def test_functional_ix_two(self):
        m1 = MetaData()
        m2 = MetaData()

        t1 = Table(
            'foo', m1,
            Column('id', Integer, primary_key=True),
            Column('email', String(50)),
            Column('name', String(50))
        )
        Index(
            "email_idx",
            func.coalesce(t1.c.email, t1.c.name).desc(), unique=True)

        t2 = Table(
            'foo', m2,
            Column('id', Integer, primary_key=True),
            Column('email', String(50)),
            Column('name', String(50))
        )
        Index(
            "email_idx",
            func.coalesce(t2.c.email, t2.c.name).desc(), unique=True)

        with assertions.expect_warnings(
                "Skipped unsupported reflection",
                "autogenerate skipping functional index"
        ):
            diffs = self._fixture(m1, m2)
        eq_(diffs, [])

    def _get_dep_statuses(self, ti, session, dep_context):
        TI = airflow.models.TaskInstance
        TR = airflow.models.TriggerRule

        # Checking that all upstream dependencies have succeeded
        if not ti.task.upstream_list:
            yield self._passing_status(
                reason="The task instance did not have any upstream tasks.")
            raise StopIteration

        if ti.task.trigger_rule == TR.DUMMY:
            yield self._passing_status(reason="The task had a dummy trigger rule set.")
            raise StopIteration

        # TODO(unknown): this query becomes quite expensive with dags that have many
        # tasks. It should be refactored to let the task report to the dag run and get the
        # aggregates from there.
        qry = (
            session
            .query(
                func.coalesce(func.sum(
                    case([(TI.state == State.SUCCESS, 1)], else_=0)), 0),
                func.coalesce(func.sum(
                    case([(TI.state == State.EXCLUDED, 1)], else_=0)), 0),
                func.coalesce(func.sum(
                    case([(TI.state == State.SKIPPED, 1)], else_=0)), 0),
                func.coalesce(func.sum(
                    case([(TI.state == State.FAILED, 1)], else_=0)), 0),
                func.coalesce(func.sum(
                    case([(TI.state == State.UPSTREAM_FAILED, 1)], else_=0)), 0),
                func.count(TI.task_id),
            )
            .filter(
                TI.dag_id == ti.dag_id,
                TI.task_id.in_(ti.task.upstream_task_ids),
                TI.execution_date == ti.execution_date,
                TI.state.in_([
                    State.SUCCESS, State.FAILED, State.EXCLUDED,
                    State.UPSTREAM_FAILED, State.SKIPPED]),
            )
        )

        successes, excluded, skipped, failed, upstream_failed, done = qry.first()

        # Add excluded tasks into successful tasks as they are equivalent for
        # dependency purposes. This is done in this way, not using the
        # state_for_dependents function, due to the constraints of SQLAlchemy
        # queries.
        successes = successes + excluded

        for dep_status in self._evaluate_trigger_rule(
                ti=ti,
                successes=successes,
                skipped=skipped,
                failed=failed,
                upstream_failed=upstream_failed,
                done=done,
                flag_upstream_failed=dep_context.flag_upstream_failed,
                session=session):
            yield dep_status

 def get_info_op_bphtb(cls, p_kode, p_tahun):
     pkey = FixLength(NOP)
     pkey.set_raw(p_kode)
     q = pbb_DBSession.query(cls.luas_bumi_sppt, cls.luas_bng_sppt,
             cls.njop_bumi_sppt, cls.njop_bng_sppt, DatObjekPajak.jalan_op,
             DatObjekPajak.blok_kav_no_op, DatObjekPajak.rt_op, DatObjekPajak.rw_op,
             cls.nm_wp_sppt.label('nm_wp'),
             func.coalesce(SpptOpBersama.luas_bumi_beban_sppt,0).label('luas_bumi_beban'),
             func.coalesce(SpptOpBersama.luas_bng_beban_sppt,0).label('luas_bng_beban'),
             func.coalesce(SpptOpBersama.njop_bumi_beban_sppt,0).label('njop_bumi_beban'),
             func.coalesce(SpptOpBersama.njop_bng_beban_sppt,0).label('njop_bng_beban'))
     q = q.filter(
             cls.kd_propinsi == DatObjekPajak.kd_propinsi,
             cls.kd_dati2 == DatObjekPajak.kd_dati2,
             cls.kd_kecamatan == DatObjekPajak.kd_kecamatan,
             cls.kd_kelurahan == DatObjekPajak.kd_kelurahan,
             cls.kd_blok == DatObjekPajak.kd_blok,
             cls.no_urut == DatObjekPajak.no_urut,
             cls.kd_jns_op == DatObjekPajak.kd_jns_op)
     q = q.outerjoin(SpptOpBersama)
     return q.filter(cls.kd_propinsi == pkey['kd_propinsi'], 
                     cls.kd_dati2 == pkey['kd_dati2'], 
                     cls.kd_kecamatan == pkey['kd_kecamatan'], 
                     cls.kd_kelurahan == pkey['kd_kelurahan'], 
                     cls.kd_blok == pkey['kd_blok'], 
                     cls.no_urut == pkey['no_urut'], 
                     cls.kd_jns_op == pkey['kd_jns_op'],
                     cls.thn_pajak_sppt == p_tahun)

    def do_job(self):
        """
        SELECT *, coalesce(end_time,now)-start_time
        FROM task t, subject s
        WHERE t.subject_id=s.id AND start_time > GetDate() - @days
        ORDER BY start_time
        :return:
        """
        session = DBSession()
        now = datetime.now()
        time_worked = (func.julianday(func.coalesce(Task.end_time,now)) - func.julianday(Task.start_time)) * 86400

        query = session.query(Task.start_time,
                              func.coalesce(Task.end_time, now),
                              time_worked,
                              Subject.title,
                              Task.title) \
            .filter(Subject.id==Task.subject_id) \
            .filter(func.date(Task.start_time) > func.date('now', '-%s day' % self.args.days)) \
            .order_by(Task.start_time)

        print '\n'

        table = PrettyTable(['Start', 'End', 'Time', 'Subject', 'Title'])
        table.align["Title"] = "l"

        total_time = 0
        day_total = 0
        last_date = None

        for row in query:
            if last_date == None:
                last_date = row[0].date()

            if row[0].date() != last_date:
                table.add_row([
                    '', '', timedelta(seconds=round(day_total)), '', ''
                ])
                last_date = row[0].date()
                day_total = 0

            day_total += row[2]
            total_time += row[2]

            table.add_row([
                row[0],
                row[1],
                timedelta(seconds=round(row[2])),
                row[3],
                row[4],
            ])

        if day_total > 0:
            table.add_row([
                '', '', timedelta(seconds=round(day_total)), '', ''
            ])

        print table
        print 'Total Work time: %s' % timedelta(seconds=total_time)
        print

    def _total(self, balance_uid):
        model = request.environ["sqlalchemy.model"]
        db = request.environ["sqlalchemy.session"]

        incomes = db.execute(
            select(
                [func.coalesce(func.sum(model.BalanceChange.c.amount), 0)],
                and_(model.BalanceChange.c.balance_uid == balance_uid, model.BalanceChange.c.is_income == True),
                from_obj=[model.balance_changes_table],
            )
        ).fetchone()[0]

        expenses = db.execute(
            select(
                [func.coalesce(func.sum(model.BalanceChange.c.amount), 0)],
                and_(model.BalanceChange.c.balance_uid == balance_uid, model.BalanceChange.c.is_income == False),
                from_obj=[model.balance_changes_table],
            )
        ).fetchone()[0]

        try:
            return incomes - expenses
        except:
            log.error("", exc_info=1)
            return 0

    def _ops_for_date_range(self, balance_uids, start_date, end_date, tags = [], change_categories = []):
        model = self.get_sa_model()
        db = self.get_sa_session()

        conditions = [or_(*[model.BalanceChange.balance_uid == balance_uid for balance_uid in balance_uids])]
        if start_date:
            conditions.append(model.BalanceChange.occurred_on >= start_date)

        if end_date:
            conditions.append(model.BalanceChange.occurred_on <= end_date)

        if isinstance(tags, list) and len(tags) > 0:
            conditions.extend([model.BalanceChange.tags.any(tag=tag.strip().lower()) for tag in tags if tag is not None and tag.strip() != ''])

        if isinstance(change_categories, list) and len(change_categories) > 0:
            conditions.extend([model.BalanceChange.change_category_uid == value.strip() for value in change_categories if value is not None and value.strip() != ''])

        try:
            summary = db.execute(select([
                    func.coalesce(func.sum(
                        case([[model.BalanceChange.amount<0, model.BalanceChange.amount]], else_=0).label("expenses"))), 
                    func.coalesce(func.sum(
                        case([[model.BalanceChange.amount>0, model.BalanceChange.amount]], else_=0).label("incomes")))
                ], 
                and_(*conditions),
                from_obj=[model.balance_changes_table])).fetchone()
            
            return {
                "expenses": summary[0],
                "incomes": summary[1],
            }
        except:
            log.error(_("Can't get summary"), exc_info=1)
            return 0

 def advanced_search(self, **kwargs):
     super(TurnoversQueryBuilder, self).advanced_search(**kwargs)
     self._filter_account(kwargs.get('account_id'))
     if 'date_from' in kwargs or 'date_to' in kwargs:
         self._filter_date(
             kwargs.get('date_from'), kwargs.get('date_to')
         )
     self._cashflows_from_sub = self._cashflows_from_sub.subquery()
     self._cashflows_to_sub = self._cashflows_to_sub.subquery()
     self.query = (
         self.query
         .outerjoin(
             self._cashflows_from_sub,
             self._cashflows_from_sub.c.account_item_id == AccountItem.id
         )
         .outerjoin(
             self._cashflows_to_sub,
             self._cashflows_to_sub.c.account_item_id == AccountItem.id
         )
     )
     balance_condition = or_(
         self._cashflows_to_sub.c.revenue != None, 
         self._cashflows_from_sub.c.expenses != None
     )
     balance_expression = (
         func.coalesce(self._cashflows_to_sub.c.revenue, 0)
         - func.coalesce(self._cashflows_from_sub.c.expenses, 0)
     )
     balance_case = (balance_condition, balance_expression)
     self.update_fields({
         'expenses': self._cashflows_from_sub.c.expenses,
         'revenue': self._cashflows_to_sub.c.revenue,
         'balance': case([balance_case,], else_=None),            
     })
     ResourcesQueryBuilder.build_query(self)

 def _filter_payment(self, payment_from, payment_to):
     if payment_from:
         self.query = self.query.filter(
             func.coalesce(self._sum_payments.c.payments, 0) >= payment_from
         )
     if payment_to:
         self.query = self.query.filter(
             func.coalesce(self._sum_payments.c.payments, 0) <= payment_to
         )

 def _filter_price(self, price_from, price_to):
     if price_from:
         self.query = self.query.filter(
             func.coalesce(LeadItem.price_from, 0) >= price_from,
         )
     if price_to:
         self.query = self.query.filter(
             func.coalesce(LeadItem.price_to, 0) <= price_to,
         )

    def percentage_by_district(self):
        """ Returns the percentage of votes aggregated by the distict. Includes
        uncounted districts and districts with no results available.

        """

        results = self.election.results
        results = results.join(ElectionResult.candidate_results)
        results = results.filter(CandidateResult.candidate_id == self.id)
        results = results.with_entities(
            ElectionResult.district.label('name'),
            func.array_agg(ElectionResult.entity_id).label('entities'),
            func.coalesce(
                func.bool_and(ElectionResult.counted), False
            ).label('counted'),
            func.sum(ElectionResult.accounted_ballots).label('total'),
            func.sum(CandidateResult.votes).label('votes'),
        )
        results = results.group_by(ElectionResult.district)
        results = results.order_by(None)
        results = results.all()
        percentage = {
            r.name: {
                'counted': r.counted,
                'entities': r.entities,
                'percentage': 100 * (r.votes / r.total) if r.total else 0.0
            } for r in results
        }

        empty = self.election.results
        empty = empty.with_entities(
            ElectionResult.district.label('name'),
            func.array_agg(ElectionResult.entity_id).label('entities'),
            func.coalesce(
                func.bool_and(ElectionResult.counted), False
            ).label('counted')
        )
        empty = empty.group_by(ElectionResult.district)
        empty = empty.order_by(None)
        for result in empty:
            update = (
                result.name not in percentage
                or (
                    set(percentage[result.name]['entities'])
                    != set(result.entities)
                )
            )
            if update:
                percentage[result.name] = {
                    'counted': result.counted,
                    'entities': result.entities,
                    'percentage': 0.0
                }

        return percentage

 def index(self, format='html'):
     """GET /rdfdocs: All items in the collection"""
     # url('rdfdocs')
     rdfdocs = Session.query(model.RDFDoc).filter(model.RDFDoc.active==True).join(model.RDFDoc.current_stats)
     c.query_string = '?'
     c.search = ''
     if request.GET.has_key('search'):
         rdfdocs = rdfdocs.filter(model.RDFDoc.name.ilike("%%%s%%" % request.GET['search']))
         c.query_string += 'search=%s&' % request.GET['search']
         c.search = request.GET['search']
     if request.GET.has_key('errors'):
         rdfdocs = rdfdocs.filter(model.StatResult.errors!=None)
         c.query_string += 'errors=1&'
     if request.GET.has_key('valid'):
         rdfdocs = rdfdocs.filter(model.StatResult.errors==None)
         c.query_string += 'valid=1&'
     if request.GET.has_key('sparql'):
         rdfdocs = rdfdocs.filter(model.RDFDoc.format=='sparql')
         c.query_string += 'sparql=1&'
     if request.GET.has_key('dumps'):
         rdfdocs = rdfdocs.filter(model.RDFDoc.format!='sparql')
         c.query_string += 'dumps=1&'
     c.sort_order = request.GET.get('sort')
     if request.GET.has_key('sort'):
         if request.GET['sort'] == 'triples':
             c.rdfdocs = rdfdocs.order_by(desc(func.coalesce(model.StatResult.triples, '0')))
         elif request.GET['sort'] == 'warnings':
             c.rdfdocs = rdfdocs.order_by(desc(func.coalesce(model.StatResult.warnings, '0')))
         elif request.GET['sort'] == 'format':
             c.rdfdocs = rdfdocs.order_by(func.coalesce(model.RDFDoc.format, '0'))
         elif request.GET['sort'] == 'issue':
             c.rdfdocs = rdfdocs.order_by(model.StatResult.errors)
         elif request.GET['sort'] == 'update':
             c.rdfdocs = rdfdocs.order_by(model.RDFDoc.last_updated.desc())
         else:
             c.rdfdocs = rdfdocs.order_by(model.RDFDoc.worked_on.desc(), model.RDFDoc.name, model.RDFDoc.last_updated.desc())
     else:
         c.rdfdocs = rdfdocs.order_by(model.RDFDoc.worked_on.desc(), model.RDFDoc.name, model.RDFDoc.last_updated.desc())
     if request.GET.has_key('page'):
         page = request.GET['page']
     else:
         page = 1
     page_url = PageURL_WebOb(request)
     c.rdfdocs_page = Page(c.rdfdocs, page=page, items_per_page=50, url=page_url)
     c.rdfdoc_count = c.rdfdocs.count()
     c.workers = Session.query(model.WorkerProc).order_by(model.WorkerProc.started.desc()).all()
     if format=='json' or 'application/json' in request.headers.get('accept', ''):
         response.content_type = 'application/json'
         json_rdfdocs = []
         for r in rdfdocs:
             json_rdfdocs.append(r.name)
         return json.dumps(json_rdfdocs)
     return render('/rdfdoc/index.html')

    def get_bayar(cls, p_kode):
        pkey = FixLength(NOP)
        pkey.set_raw(p_kode)    
        query = pbb_DBSession.query(
              func.concat(cls.kd_propinsi, '.').concat(cls.kd_dati2).concat('-').\
                   concat(cls.kd_kecamatan).concat('.').concat(cls.kd_kelurahan).concat('-').\
                   concat(cls.kd_blok).concat('.').concat(cls.no_urut).concat('-').\
                   concat(cls.kd_jns_op).label('nop'), cls.thn_pajak_sppt,
			cls.nm_wp_sppt,	cls.jln_wp_sppt, cls.blok_kav_no_wp_sppt,
			cls.rw_wp_sppt, cls.rt_wp_sppt, cls.kelurahan_wp_sppt,
            cls.kota_wp_sppt, cls.kd_pos_wp_sppt, cls.npwp_sppt, 
            cls.kd_kls_tanah, cls.kd_kls_bng, 
			cls.luas_bumi_sppt, cls.luas_bng_sppt, 
            cls.njop_bumi_sppt, cls.njop_bng_sppt, cls.njop_sppt,			
			cls.njoptkp_sppt, cls.pbb_terhutang_sppt, cls.faktor_pengurang_sppt,
			cls.status_pembayaran_sppt, 
            cls.tgl_jatuh_tempo_sppt,
			cls.pbb_yg_harus_dibayar_sppt.label('pokok'),
            func.max(PembayaranSppt.tgl_pembayaran_sppt).label('tgl_pembayaran_sppt'),
            func.sum(func.coalesce(PembayaranSppt.jml_sppt_yg_dibayar,0)).label('bayar'),
            func.sum(func.coalesce(PembayaranSppt.denda_sppt,0)).label('denda_sppt'),).\
			outerjoin(PembayaranSppt,and_(
                            cls.kd_propinsi==PembayaranSppt.kd_propinsi,
                            cls.kd_dati2==PembayaranSppt.kd_dati2,
                            cls.kd_kecamatan==PembayaranSppt.kd_kecamatan,
                            cls.kd_kelurahan==PembayaranSppt.kd_kelurahan,
                            cls.kd_blok==PembayaranSppt.kd_blok,
                            cls.no_urut==PembayaranSppt.no_urut,
                            cls.kd_jns_op==PembayaranSppt.kd_jns_op,
                            cls.thn_pajak_sppt==PembayaranSppt.thn_pajak_sppt
                            )).\
            group_by(cls.kd_propinsi, cls.kd_dati2, cls.kd_kecamatan, cls.kd_kelurahan, 
                    cls.kd_blok, cls.no_urut, cls.kd_jns_op, cls.thn_pajak_sppt,
                    cls.nm_wp_sppt,	cls.jln_wp_sppt, cls.blok_kav_no_wp_sppt,
                    cls.rw_wp_sppt, cls.rt_wp_sppt, cls.kelurahan_wp_sppt,
                    cls.kota_wp_sppt, cls.kd_pos_wp_sppt, cls.npwp_sppt, 
                    cls.kd_kls_tanah, cls.kd_kls_bng, 
                    cls.luas_bumi_sppt, cls.luas_bng_sppt, 
                    cls.njop_bumi_sppt, cls.njop_bng_sppt, cls.njop_sppt,			
                    cls.njoptkp_sppt, cls.pbb_terhutang_sppt, cls.faktor_pengurang_sppt,
                    cls.status_pembayaran_sppt, 
                    cls.tgl_jatuh_tempo_sppt,
                    cls.pbb_yg_harus_dibayar_sppt.label('pokok'),)
            
        return query.filter(cls.kd_propinsi == pkey['kd_propinsi'], 
                            cls.kd_dati2 == pkey['kd_dati2'], 
                            cls.kd_kecamatan == pkey['kd_kecamatan'], 
                            cls.kd_kelurahan == pkey['kd_kelurahan'], 
                            cls.kd_blok == pkey['kd_blok'], 
                            cls.no_urut == pkey['no_urut'], 
                            cls.kd_jns_op == pkey['kd_jns_op'],)

 def get_colour_map(cls):
     colour = func.coalesce(Category.colour, ParentCategory.colour)
     return dict(
         Category.query.outerjoin(ParentCategory, ParentCategory.id == Category.parent)
         .filter(colour.isnot(None))
         .values(Category.id, colour)
     )

    def _ops_for_date_range(self, balance_uids, start_date, end_date, incomes = True, expenses = False):
        """
        Will be removed in next release.
        """
        model = request.environ["sqlalchemy.model"]
        db = request.environ["sqlalchemy.session"]

        conditions = [or_(*[model.BalanceChange.balance_uid == balance_uid for balance_uid in balance_uids])]
        if incomes:
            conditions.append(model.BalanceChange.amount >= 0)
        if expenses:
            conditions.append(model.BalanceChange.amount < 0)

        if start_date:
            conditions.append(model.BalanceChange.occurred_on >= start_date)

        if end_date:
            conditions.append(model.BalanceChange.occurred_on <= end_date)

        try:
            return db.execute(select([func.coalesce(func.sum(model.BalanceChange.amount), 0).label("summary")], 
                and_(*conditions),
                from_obj=[model.balance_changes_table])).fetchone()[0]
        except:
            log.error("", exc_info=1)
            return 0

    def build_query_to_populate(self, query, full_table, aggregate_table):
        insert_columns = [aggregate_table.c.join_key]
        fk = Column(self.key, Integer)
        geom = Column(self.geometry_column, Geometry())
        bins_table = Table(self.table, full_table.metadata, fk, geom)

        if self.join_custom_data: 
            extra_data = Table("extra_data", full_table.metadata, 
                    Column("verified", Boolean),
                    Column("timestamp", DateTime),
                    Column("client_ip", Integer),
                    Column("server_ip", Integer),
                    Column("location", Geometry("Point", srid=4326)),
                    keep_existing = True)

            joining = join(full_table, extra_data,
                    and_(extra_data.c.client_ip == full_table.c.client_ip,
                        extra_data.c.server_ip == full_table.c.server_ip,
                        extra_data.c.timestamp == full_table.c.time),
                    isouter = True)
            query = query.select_from(joining)
            location = case([(extra_data.c.verified, func.coalesce(extra_data.c.location, full_table.c.location))], else_ = full_table.c.location)
        else:
            location = full_table.c.location

        select_query = (query.select_from(bins_table)
             .where(ST_Intersects(location, geom))
             .column(fk)
             .group_by(fk))
        return insert_columns, select_query

    def build_query_to_populate(self, query, full_table, aggregate_table):
        insert_columns = [aggregate_table.c.join_key]
        fk = Column(self.key, Integer)
        geom = Column(self.geometry_column, Geometry())
        bins_table = Table(self.table, full_table.metadata, fk, geom)

        if self.join_custom_data: 
            extra_data = Table("extra_data", full_table.metadata, 
                    Column("timestamp", DateTime),
                    Column("verified", Boolean),
                    Column("bigquery_key", String),
                    Column("connection_type", String),
                    Column("advertised_download", Integer),
                    Column("actual_download", Float),
                    Column("advertised_upload", Integer),
                    Column("actual_upload", Float),
                    Column("min_rtt", Integer),
                    Column("location_type", String),
                    Column("cost_of_service", Integer),
                    Column("location", Geometry("Point", srid=4326)),
                    keep_existing = True)

            joining = join(full_table, extra_data,
                    and_(extra_data.c.bigquery_key == full_table.c.bigquery_key),
                    isouter = True)
            query = query.select_from(joining)
            location = case([(extra_data.c.verified, func.coalesce(extra_data.c.location, full_table.c.location))], else_ = full_table.c.location)
        else:
            location = full_table.c.location

        select_query = (query.select_from(bins_table)
             .where(ST_Intersects(location, geom))
             .column(fk)
             .group_by(fk))
        return insert_columns, select_query

def update_canonicals(canonicals):
    '''
    Update canonical data for android devices.
    '''
    global ENGINE
    binding = [{"p_{}".format(k): v for k, v in canonical.items()} for canonical in canonicals]
    device_table = model.metadata.tables['device']
    stmt = update(device_table).\
        values(device_token_new=bindparam('p_new_token')).\
        where(and_(device_table.c.login_id == bindparam('p_login_id'),
                   func.coalesce(device_table.c.device_token_new, device_table.c.device_token) == bindparam('p_old_token')))
    ENGINE.execute(stmt, binding)

    with session_scope() as session:
        query = text('SELECT keep_max_users_per_device( \
                     (:platform_id)::int2, :device_token, (:max_users_per_device)::int2)')
        for canonical in canonicals:
            session.execute(query,
                            {'platform_id': constants.PLATFORM_ANDROID,
                             'device_token': canonical['new_token'],
                             'max_users_per_device': config.max_users_per_device
                            })
            session.execute(query,
                            {'platform_id': constants.PLATFORM_ANDROID_TABLET,
                             'device_token': canonical['new_token'],
                             'max_users_per_device': config.max_users_per_device
                            })
        session.commit()

 def _set_orderby_desc(self, query, model, limit,
                       last_id, offset, descending, orderby):
     """Return an updated query with the proper orderby and desc."""
     if orderby == 'fav_user_ids':
         n_favs = func.coalesce(func.array_length(model.fav_user_ids, 1), 0).label('n_favs')
         query = query.add_column(n_favs)
     if orderby in ['created', 'updated', 'finish_time']:
         if descending:
             query = query.order_by(desc(
                                         cast(getattr(model,
                                                      orderby),
                                              TIMESTAMP)))
         else:
             query = query.order_by(cast(getattr(model, orderby), TIMESTAMP))
     else:
         if orderby != 'fav_user_ids':
             if descending:
                 query = query.order_by(desc(getattr(model, orderby)))
             else:
                 query = query.order_by(getattr(model, orderby))
         else:
             if descending:
                 query = query.order_by(desc("n_favs"))
             else:
                 query = query.order_by("n_favs")
     if last_id:
         query = query.limit(limit)
     else:
         query = query.limit(limit).offset(offset)
     return query