def top_maps(self):
        """Returns the raw data shared by all renderers."""
        try:
            top_maps_q = DBSession.query(
                fg.row_number().over(order_by=expr.desc(func.count())).label("rank"),
                Game.map_id, Map.name, func.count().label("times_played"))\
                .filter(Map.map_id == Game.map_id)\
                .filter(Game.server_id == self.server_id)\
                .filter(Game.create_dt > (self.now - timedelta(days=self.lifetime)))\
                .group_by(Game.map_id)\
                .group_by(Map.name) \
                .order_by(expr.desc(func.count()))

            if self.last:
                top_maps_q = top_maps_q.offset(self.last)

            if self.limit:
                top_maps_q = top_maps_q.limit(self.limit)

            top_maps = top_maps_q.all()
        except Exception as e:
            log.debug(e)
            raise HTTPNotFound

        return top_maps

def top_maps_by_times_played_q(cutoff_days, region = None, game_type_cd = None):
    """
    Query to retrieve the top maps by the amount of times it was played
    during a date range.

    Games older than cutoff_days days old are ignored.
    """
    # only games played during this range are considered
    right_now = datetime.utcnow()
    cutoff_dt = right_now - timedelta(days=cutoff_days)

    top_maps_q = DBSession.query(Game.map_id, Map.name,
            func.count()).\
            filter(Map.map_id==Game.map_id).\
            filter(expr.between(Game.create_dt, cutoff_dt, right_now)).\
            order_by(expr.desc(func.count())).\
            group_by(Game.map_id).\
            group_by(Map.name)

    if region and region != "" and region != "0":
      top_maps_q = top_maps_q.filter(Server.region==region).filter(Server.server_id==Game.server_id)
    if game_type_cd and game_type_cd != "":
      top_maps_q = top_maps_q.filter(Game.game_type_cd == game_type_cd)    

    return top_maps_q

  def get(self):
    obj_count = \
      sa.sql.select([
        Tag.ns,
        functions.count(entity_tag_tbl.c.entity_id).label('obj_count'),
      ])\
            .select_from(Tag.__table__.join(entity_tag_tbl))\
            .group_by(Tag.ns)\
            .alias()

    ns_query = sa.sql.select(
      [Tag.ns,
       functions.count(Tag.id).label('tag_count'),
       obj_count.c.obj_count],
      from_obj=[Tag.__table__.outerjoin(obj_count, Tag.ns == obj_count.c.ns)]
    )\
                     .group_by(Tag.ns, obj_count.c.obj_count)\
                     .order_by(Tag.ns)

    session = current_app.db.session()
    namespaces = session.execute(ns_query)

    return render_template(
      'admin/tags.html',
      namespaces=namespaces,
    )

 def execute(self, message, user, params):
     
     tick = Updates.current_tick() + (params.group(1) or 1)
     replies = []
     
     Q = session.query(Galaxy.x, Galaxy.y, count())
     Q = Q.join(Target.planet)
     Q = Q.join(Planet.galaxy)
     Q = Q.filter(Planet.active == True)
     Q = Q.filter(Target.tick >= tick)
     Q = Q.group_by(Galaxy.x, Galaxy.y)
     result = Q.all()
     prev = []
     for x, y, bitches in result:
         prev.append("%s:%s(%s)"%(x,y,bitches))
     replies.append("Active bookings: " + ", ".join(prev))
     
     Q = session.query(Alliance.name, count())
     Q = Q.join(Target.planet)
     Q = Q.outerjoin(Planet.intel)
     Q = Q.outerjoin(Intel.alliance)
     Q = Q.filter(Planet.active == True)
     Q = Q.filter(Target.tick >= tick)
     Q = Q.group_by(Alliance.name)
     result = Q.all()
     prev = []
     for name, bitches in result:
         prev.append("%s (%s)"%(name or "Unknown", bitches))
     replies.append("Active bitches: " + ", ".join(prev))
     
     if len(replies) < 1:
         replies.append("No active bookings. This makes %s sad. Please don't make %s sad." %((Config.get("Connection","nick"),)*2))
     message.reply("\n".join(replies))

    def fleet_overview(self):
        if self.scantype not in ("J",):
            return

        from sqlalchemy.sql.functions import min, sum

        f = aliased(FleetScan)
        a = aliased(FleetScan)
        d = aliased(FleetScan)

        Q = session.query(
            f.landing_tick,
            f.landing_tick - min(Scan.tick),
            count(a.id),
            coalesce(sum(a.fleet_size), 0),
            count(d.id),
            coalesce(sum(d.fleet_size), 0),
        )
        Q = Q.join(f.scan)
        Q = Q.filter(f.scan == self)

        Q = Q.outerjoin((a, and_(a.id == f.id, a.mission.ilike("Attack"))))
        Q = Q.outerjoin((d, and_(d.id == f.id, d.mission.ilike("Defend"))))

        Q = Q.group_by(f.landing_tick)
        Q = Q.order_by(asc(f.landing_tick))
        return Q.all()

    def get_counts_for_query(self, q):
        # HACKITY HACK
        entities = [
            x.entity_zero.entity for x in q._entities]
        entities = {e.__mapper__.tables[0].name: e for e in entities}
        content_entity = entities['content']

        post = with_polymorphic(
            Post, [], Post.__table__,
            aliased=False, flat=True)
        q = q.join(
            post, (content_entity.id == post.id) &
                  (post.publication_state.in_(countable_publication_states)))

        if self.user_id:
            action_entity = entities['action']
            return q.with_entities(
                count(content_entity.id),
                count(post.creator_id.distinct()),
                count(action_entity.id)).first()
        else:
            (post_count, contributor_count) = q.with_entities(
                count(content_entity.id),
                count(post.creator_id.distinct())).first()
            return (post_count, contributor_count, 0)

 def execute(self, request, user, x, y, z, h=False, hs=False, ticks=None):
     planet = Planet.load(x,y,z)
     if planet is None:
         return HttpResponseRedirect(reverse("planet_ranks"))
     
     ticks = int(ticks or 0) if (h or hs) else 12
     
     if not hs:
         sizediffvalue = PlanetHistory.rdiff * PA.getint("numbers", "roid_value")
         valuediffwsizevalue = PlanetHistory.vdiff - sizediffvalue
         resvalue = valuediffwsizevalue * PA.getint("numbers", "res_value")
         shipvalue = valuediffwsizevalue * PA.getint("numbers", "ship_value")
         xpvalue = PlanetHistory.xdiff * PA.getint("numbers", "xp_value")
         Q = session.query(PlanetHistory,
                             sizediffvalue,
                             valuediffwsizevalue,
                             resvalue, shipvalue,
                             xpvalue,
                             )
         Q = Q.filter(PlanetHistory.current == planet)
         Q = Q.order_by(desc(PlanetHistory.tick))
         history = Q[:ticks] if ticks else Q.all()
     else:
         history = None
     
     if not (h or hs):
         landings = session.query(PlanetLandings.hour, count()).filter(PlanetLandings.planet==planet).group_by(PlanetLandings.hour).all()
         landed = session.query(PlanetLandedOn.hour, count()).filter(PlanetLandedOn.planet==planet).group_by(PlanetLandedOn.hour).all()
         vdrops = session.query(PlanetValueDrops.hour, count()).filter(PlanetValueDrops.planet==planet).group_by(PlanetValueDrops.hour).all()
         idles = session.query(PlanetIdles.hour, count()).filter(PlanetIdles.planet==planet).group_by(PlanetIdles.hour).all()
         hourstats = {
                         'landings' : dict(landings), 'landingsT' : sum([c for hour,c in landings]),
                         'landed'   : dict(landed),   'landedT'   : sum([c for hour,c in landed]),
                         'vdrops'   : dict(vdrops),   'vdropsT'   : sum([c for hour,c in vdrops]),
                         'idles'    : dict(idles),    'idlesT'    : sum([c for hour,c in idles]),
                         }
     else:
         hourstats = None
     
     if not h:
         Q = session.query(PlanetHistory)
         Q = Q.filter(or_(PlanetHistory.hour == 23, PlanetHistory.tick == Updates.current_tick()))
         Q = Q.filter(PlanetHistory.current == planet)
         Q = Q.order_by(desc(PlanetHistory.tick))
         hsummary = Q.all() if hs else Q[:14]
     else:
         hsummary = None
     
     return render(["planet.tpl",["hplanet.tpl","hsplanet.tpl"][hs]][h or hs],
                     request,
                     planet = planet,
                     history = history,
                     hour = datetime.utcnow().hour, hourstats = hourstats,
                     hsummary = hsummary,
                     ticks = ticks,
                   )

def main_index(request):
    leaderboard_count = 10
    recent_games_count = 32

    # top players by score
    top_players = DBSession.query(Player.player_id, Player.nick, 
            func.sum(PlayerGameStat.score)).\
            filter(Player.player_id == PlayerGameStat.player_id).\
            filter(Player.player_id > 2).\
            order_by(expr.desc(func.sum(PlayerGameStat.score))).\
            group_by(Player.nick).\
            group_by(Player.player_id).all()[0:10]

    top_players = [(player_id, html_colors(nick), score) \
            for (player_id, nick, score) in top_players]

    for i in range(leaderboard_count-len(top_players)):
        top_players.append(('-', '-', '-'))

    # top servers by number of total players played
    top_servers = DBSession.query(Server.server_id, Server.name, 
            func.count()).\
            filter(Game.server_id==Server.server_id).\
            order_by(expr.desc(func.count(Game.game_id))).\
            group_by(Server.server_id).\
            group_by(Server.name).all()[0:10]

    for i in range(leaderboard_count-len(top_servers)):
        top_servers.append(('-', '-', '-'))

    # top maps by total times played
    top_maps = DBSession.query(Map.map_id, Map.name, 
            func.count(Game.game_id)).\
            filter(Map.map_id==Game.game_id).\
            order_by(expr.desc(func.count(Game.game_id))).\
            group_by(Map.map_id).\
            group_by(Map.name).all()[0:10]

    for i in range(leaderboard_count-len(top_maps)):
        top_maps.append(('-', '-', '-'))

    recent_games = DBSession.query(Game, Server, Map).\
            filter(Game.server_id==Server.server_id).\
            filter(Game.map_id==Map.map_id).\
            order_by(expr.desc(Game.start_dt)).all()[0:recent_games_count]

    for i in range(recent_games_count-len(recent_games)):
        recent_games.append(('-', '-', '-'))

    return {'top_players':top_players,
            'top_servers':top_servers,
            'top_maps':top_maps,
            'recent_games':recent_games,
            }

    def getAlarmCount(days=0):
        """
        Get number of alarms, grouped by state

        :param optional days: 0 for all alarms, since days else
        :return: list grouped by state
        """
        if days != 0:
            return db.get(Alarm.state, count(Alarm.id)).filter(Alarm.timestamp > (datetime.datetime.now() - datetime.timedelta(days=days))).order_by(Alarm.timestamp.desc()).group_by(Alarm.state).all()
        else:
            return db.get(Alarm.state, count(Alarm.id)).group_by(Alarm.state).all()

 def execute(self, request, user, name):
     alliance = Alliance.load(name)
     if alliance is None:
         return HttpResponseRedirect(reverse("alliance_ranks"))
     
     ph = aliased(PlanetHistory)
     members = count().label("members")
     size = sum(ph.size).label("size")
     value = sum(ph.value).label("value")
     score = sum(ph.score).label("score")
     avg_size = size.op("/")(members).label("avg_size")
     avg_value = value.op("/")(members).label("avg_value")
     t10v = count(case(whens=((ph.value_rank <= 10 ,1),), else_=None)).label("t10v")
     t100v = count(case(whens=((ph.value_rank <= 100 ,1),), else_=None)).label("t100v")
     
     pho = aliased(PlanetHistory)
     sizeo = sum(pho.size).label("sizeo")
     valueo = sum(pho.value).label("valueo")
     scoreo = sum(pho.score).label("scoreo")
     
     Q = session.query(PlanetHistory.tick.label("tick"),
                       Alliance.id.label("id"),
                       literal_column("rank() OVER (PARTITION BY planet_history.tick ORDER BY sum(planet_history.size) DESC)").label("size_rank"),
                       literal_column("rank() OVER (PARTITION BY planet_history.tick ORDER BY sum(planet_history.value) DESC)").label("value_rank"),
                       )
     Q = Q.filter(PlanetHistory.active == True)
     Q = Q.join(PlanetHistory.current)
     Q = Q.join(Planet.intel)
     Q = Q.join(Intel.alliance)
     Q = Q.group_by(PlanetHistory.tick, Alliance.id)
     ranks = Q.subquery()
     
     Q = session.query(ph.tick, members,
                       size, value,
                       avg_size, avg_value,
                       size-sizeo, value-valueo, score-scoreo,
                       t10v, t100v,
                       )
     Q = Q.filter(ph.active == True)
     Q = Q.join(ph.current)
     Q = Q.join(Planet.intel)
     Q = Q.join(Intel.alliance)
     Q = Q.outerjoin((pho, and_(ph.id==pho.id, ph.tick-1==pho.tick),))
     Q = Q.filter(Intel.alliance == alliance)
     Q = Q.group_by(ph.tick)
     
     Q = Q.from_self().add_columns(ranks.c.size_rank, ranks.c.value_rank)
     Q = Q.outerjoin((ranks, and_(ph.tick == ranks.c.tick, alliance.id == ranks.c.id),))
     Q = Q.order_by(desc(ph.tick))
     
     history = Q.all()
     
     return render("ialliancehistory.tpl", request, alliance=alliance, members=alliance.intel_members, history=history)

def upgrade(pyramid_env):
    from assembl.models import IdeaLink, get_session_maker
    db = get_session_maker()()
    # First, reorder live links.
    with transaction.manager:
        ids = db.query(IdeaLink.source_id)\
            .filter(IdeaLink.tombstone_date == None)\
            .group_by(IdeaLink.source_id, IdeaLink.order)\
            .having((count(IdeaLink.id) > 1)).all()
        for (source_id,) in ids:
            links = db.query(IdeaLink).filter_by(
                source_id=source_id,
                tombstone_date=None).order_by(
                IdeaLink.order, IdeaLink.id).all()
            for n, link in enumerate(links):
                link.order = n + 1
        # Then dead links
        q = db.query(
            IdeaLink.source_id, IdeaLink.tombstone_date).\
            group_by(IdeaLink.source_id, IdeaLink.order,
                     IdeaLink.tombstone_date).\
            having((count(IdeaLink.id) > 1)).all()
        for (source_id, date) in q:
            if not date:
                continue
            dest_links = db.query(IdeaLink).filter_by(
                source_id=source_id,
                tombstone_date=date).all()
            # Try to find the order of the ordered link the closest
            # in date to each current link.
            all_links = db.query(IdeaLink).filter_by(source_id=source_id).all()
            by_base_id = defaultdict(list)
            for link in all_links:
                by_base_id[link.base_id].append(link)
            signatures = {}
            for dest in dest_links:
                base_id = dest.base_id
                links = by_base_id[base_id]
                # source_id should be the same.
                links = [l for l in links if l.order]

                def distance(l):
                    if l == dest:
                        return -1
                    if not l.tombstone_date:
                        return 0
                    return abs((l.tombstone_date-dest.tombstone_date).seconds)
                links.sort(key=distance)
                signatures[base_id] = tuple((l.order for l in links))
            dest_links.sort(key=lambda l: signatures[l.base_id])
            for n, link in enumerate(dest_links):
                link.order = n

def calculateDomainAffinities():
    DeityDomain2 = aliased(DeityDomain)
    Domain2 = aliased(Domain)
    q_affinity = session.query(Domain.name, Domain2.name, count()).\
        join(DeityDomain).\
        join(Deity).\
        join(DeityDomain2).\
        join(Domain2).\
        filter(Domain.id != Domain2.id).\
        order_by(Domain.name, count().desc(), Domain2.name).\
        group_by(Domain.name, Domain2.name)

    return q_affinity.all()

 def execute(self, message, user, params):
     
     tag_count = PA.getint("numbers", "tag_count")
     
     alliance = Alliance.load(params.group(1))
     if alliance is None:
         message.reply("No alliance matching '%s' found"%(params.group(1),))
         return
     
     Q = session.query(sum(Planet.value), sum(Planet.score),
                       sum(Planet.size), sum(Planet.xp),
                       count())
     Q = Q.join(Planet.intel)
     Q = Q.filter(Planet.active == True)
     Q = Q.filter(Intel.alliance==alliance)
     Q = Q.group_by(Intel.alliance_id)
     result = Q.first()
     if result is None:
         message.reply("No planets in intel match alliance %s"%(alliance.name,))
         return
     
     value, score, size, xp, members = result
     if members <= tag_count:
         reply="%s Members: %s/%s, Value: %s, Avg: %s," % (alliance.name,members,alliance.members,value,value//members)
         reply+=" Score: %s, Avg: %s," % (score,score//members) 
         reply+=" Size: %s, Avg: %s, XP: %s, Avg: %s" % (size,size//members,xp,xp//members)
         message.reply(reply)
         return
     
     Q = session.query(Planet.value, Planet.score, 
                       Planet.size, Planet.xp, 
                       Intel.alliance_id)
     Q = Q.join(Planet.intel)
     Q = Q.filter(Planet.active == True)
     Q = Q.filter(Intel.alliance==alliance)
     Q = Q.order_by(desc(Planet.score))
     Q = Q.limit(tag_count)
     Q = Q.from_self(sum(Planet.value), sum(Planet.score),
                     sum(Planet.size), sum(Planet.xp),
                     count())
     Q = Q.group_by(Intel.alliance_id)
     ts_result = Q.first()
     
     ts_value, ts_score, ts_size, ts_xp, ts_members = ts_result
     reply="%s Members: %s/%s (%s)" % (alliance.name,members,alliance.members,ts_members)
     reply+=", Value: %s (%s), Avg: %s (%s)" % (value,ts_value,value//members,ts_value//ts_members)
     reply+=", Score: %s (%s), Avg: %s (%s)" % (score,ts_score,score//members,ts_score//ts_members)
     reply+=", Size: %s (%s), Avg: %s (%s)" % (size,ts_size,size//members,ts_size//ts_members)
     reply+=", XP: %s (%s), Avg: %s (%s)" % (xp,ts_xp,xp//members,ts_xp//ts_members)
     message.reply(reply)

 def get_counts_for_query(self, q):
     if self.user_id:
         # HACKITY HACK
         (content_entity, action_entity) = [
             x.entity_zero.entity for x in q._entities]
         return q.with_entities(
             count(content_entity.id), count(action_entity.id)).first()
         return (post_count, viewed_count)
     else:
         (content_entity,) = [
             x.entity_zero.entity for x in q._entities]
         (post_count,) = q.with_entities(
             count(content_entity.id)).first()
         return (post_count, 0)

def calculateDomainAffinitiesForSetting(setting):
    DeityDomain2 = aliased(DeityDomain)
    Domain2 = aliased(Domain)
    q_settingaffinity = session.query(Domain.name, Domain2.name, count()).\
        join(DeityDomain).\
        join(Deity).\
        join(DeityDomain2).\
        join(Domain2).\
        join(DeitySetting).\
        filter(Domain.id != Domain2.id, DeitySetting.setting == setting).\
        order_by(Domain.name, count().desc(), Domain2.name).\
        group_by(Domain.name, Domain2.name)

    return q_settingaffinity.all()

    def execute(self, message, user, params):

        alliance = Alliance.load(params.group(1))
        if alliance is None:
            message.reply("No alliance matching '%s' found" % (params.group(1),))
            return
        if params.group(2):
            alliance2 = Alliance.load(params.group(2))
            if alliance2 is None:
                message.reply("No alliance matching '%s' found" % (params.group(2),))
                return
        bums = int(params.group(3) or 2)
        Q = session.query(Galaxy.x, Galaxy.y, count())
        Q = Q.join(Galaxy.planets)
        Q = Q.join(Planet.intel)
        Q = Q.filter(Galaxy.active == True)
        Q = Q.filter(Planet.active == True)
        if params.group(2):
            R = Q.filter(Intel.alliance == alliance2)
            R = R.group_by(Galaxy.x, Galaxy.y)
            R = R.having(count() >= bums)
        Q = Q.filter(Intel.alliance == alliance)
        Q = Q.group_by(Galaxy.x, Galaxy.y)
        Q = Q.having(count() >= bums)
        prev = []
        if params.group(2):
            for x1, y1, c1 in Q.all():
                for x2, y2, c2 in R.all():
                    if x1 == x2 and y1 == y2:
                        prev.append("%s:%s (%s,%s)" % (x1, y1, c1, c2))
            if len(prev) < 1:
                message.reply(
                    "No galaxies with at least %s bumchums from %s and %s" % (bums, alliance.name, alliance2.name)
                )
                return
            reply = "Galaxies with at least %s bums from %s and %s: " % (
                bums,
                alliance.name,
                alliance2.name,
            ) + " | ".join(prev)
        else:
            result = Q.all()
            if len(result) < 1:
                message.reply("No galaxies with at least %s bumchums from %s" % (bums, alliance.name))
                return
            prev = []
            for x, y, chums in result:
                prev.append("%s:%s (%s)" % (x, y, chums))
            reply = "Galaxies with at least %s bums from %s: " % (bums, alliance.name) + " | ".join(prev)
        message.reply(reply)

    def list_planets(self, message, user, params):
        oIntel = aliased(Intel)
        tIntel = aliased(Intel)
        
        # Find all planets with unknown alliance, who have been defended by planets (outside of their galaxy) with known alliance
        TQ = session.query(Planet.x, Planet.y, Planet.z, Alliance.name, count()).select_from(FleetScan).filter(FleetScan.in_galaxy==False, FleetScan.mission=="Defend")
        TQ = TQ.join(oIntel, FleetScan.owner_id == oIntel.planet_id).join(tIntel, FleetScan.target_id == tIntel.planet_id)
        TQ = TQ.filter(tIntel.alliance_id == None).filter(oIntel.alliance_id != None)
        TQ = TQ.join(Alliance, oIntel.alliance_id == Alliance.id).join(Planet, FleetScan.target_id == Planet.id)
        TQ = TQ.group_by(Planet.x, Planet.y, Planet.z, Alliance.name)

        # Find all planets with unknown alliance, who have defended planets (outside of their galaxy) with known alliance
        OQ = session.query(Planet.x, Planet.y, Planet.z, Alliance.name, count()).select_from(FleetScan).filter(FleetScan.in_galaxy==False, FleetScan.mission=="Defend")
        OQ = OQ.join(oIntel, FleetScan.owner_id == oIntel.planet_id).join(tIntel, FleetScan.target_id == tIntel.planet_id)
        OQ = OQ.filter(tIntel.alliance_id != None).filter(oIntel.alliance_id == None)
        OQ = OQ.join(Alliance, tIntel.alliance_id == Alliance.id).join(Planet, FleetScan.owner_id == Planet.id)
        OQ = OQ.group_by(Planet.x, Planet.y, Planet.z, Alliance.name)

        # A FULL OUTER JOIN would fit nicely here, but SQLAlchemy doesn't support it and I'm trying to stick with ORM, so we'll use Python

        # Combine the results into one sorted list
        results = sorted(TQ.all()+OQ.all())

        # Quit now if there are no results
        if len(results) == 0:
            message.reply("No suggestions found")
            return

        i = 0
        while i < (len(results)-1):
          # Check for planet/alliance combinations that appeared in both lists
          if results[i][:4] == results[i+1][:4]:
            r = list(results.pop(i))
            # Add the fleet counts (r[i+1] has moved to r[i])
            r[4] += results.pop(i)[4]
            results.insert(i, r)
          i+=1

        # Sort by number of fleets using a helper function
        from operator import itemgetter
        results.sort(key=itemgetter(4), reverse=True)

        # Reply to the user
        message.reply("Coords     Suggestion      Fleets")
        limit = int(params.group(1) or 5)
        for r in results[:limit]:
            message.reply("%-9s  %-14s  %s" % ("%s:%s:%s" % (r[0], r[1], r[2]), r[3], r[4]))
        if len(results) > limit:
            message.reply("%s results not shown (%s total)" % (len(results)-limit, len(results)))

def dashboard(request):
    dbsession = DBSession()

    jobs = Job.get_last()
    running_jobs = Job.get_running()
    upcoming_jobs = Job.get_upcoming()

    # statistics
    num_clients = dbsession.query(count(Client.clientid)).scalar()
    num_jobs = dbsession.query(count(Job.jobid)).scalar()
    num_volumes = dbsession.query(count(Media.mediaid)).scalar()
    sum_volumes = Media.format_byte_size(dbsession.query(sum(Media.volbytes)).scalar() or 0)
    database_size = get_database_size(DBSession.bind)

    return locals()

    def get_synthesis_contributors(self, id_only=True):
        # author of important extracts
        from .idea_content_link import Extract
        from .auth import AgentProfile
        from .post import Post
        from sqlalchemy.sql.functions import count
        local_uri = AssemblQuadStorageManager.local_uri()
        discussion_storage = \
            AssemblQuadStorageManager.discussion_storage_name()

        idea_uri = URIRef(self.uri(local_uri))
        clause = '''select distinct ?annotation where {
            %s idea:includes* ?ideaP .
            ?annotation assembl:resourceExpressesIdea ?ideaP }'''
        extract_ids = [x for (x,) in self.db.execute(
            SparqlClause(clause % (
                idea_uri.n3(),),
                quad_storage=discussion_storage.n3()))]
        r = list(self.db.query(AgentProfile.id, count(Extract.id)).join(
            Post, Post.creator_id==AgentProfile.id).join(Extract).filter(
            Extract.important == True, Extract.id.in_(extract_ids)))
        r.sort(key=lambda x: x[1], reverse=True)
        if id_only:
            return [AgentProfile.uri_generic(a) for (a, ce) in r]
        else:
            ids = [a for (a, ce) in r]
            order = {id: order for (order, id) in enumerate(ids)}
            agents = self.db.query(AgentProfile).filter(AgentProfile.id.in_(ids)).all()
            agents.sort(key=lambda a: order[a.id])
            return agents

def _base_query(table: Table,
                email: str,
                selected: list,
                where: BinaryExpression=None) -> Select:
    """
    Return a query for a DataTable without any text filtering, ordering,
    or limiting applied.

    :param table: the SQLAlchemy table. Should be one or more tables joined
                  with the auth_user table
    :param email: the user's e-mail address
    :param selected: the columns to select from the table
    :param where: an optional WHERE clause to apply to the query
    :return: the query object
    """
    # Selected columns (but not aggregate functions) must also appear in the
    # GROUP BY clause
    grouped = (column for column in selected if type(column) is Column)

    query = select(
        # The extra column is for the DataTable recordsFiltered attribute.
        # It represents the number of records found before applying a sql LIMIT
        selected + [count().over().label('filtered')]
    ).select_from(
        table
    ).group_by(
        *grouped
    ).where(
        auth_user_table.c.email == email
    )
    if where is not None:
        query = query.where(where)
    return query