def _query(query):
     last_week = datetime.now() - timedelta(days=7)
     return query.options(undefer('post_count'),
                          undefer('posted_at')). \
                  filter(or_(Topic.status == "open",
                             and_(Topic.status != "open",
                                  Topic.posted_at >= last_week)))

    def _get_eagerloaded_query(self, *args, **kwargs):
        """Eager hostnames loading.

        This is too complex to get_joinedloads so I have to
        override the function
        """
        query = super(VulnerabilityView, self)._get_eagerloaded_query(
            *args, **kwargs)
        joinedloads = [
            joinedload(Vulnerability.host)
            .load_only(Host.id)  # Only hostnames are needed
            .joinedload(Host.hostnames),

            joinedload(Vulnerability.service)
            .joinedload(Service.host)
            .joinedload(Host.hostnames),

            joinedload(VulnerabilityWeb.service)
            .joinedload(Service.host)
            .joinedload(Host.hostnames),
            joinedload(VulnerabilityGeneric.update_user),
            undefer(VulnerabilityGeneric.creator_command_id),
            undefer(VulnerabilityGeneric.creator_command_tool),
            undefer(VulnerabilityGeneric.target_host_ip),
            undefer(VulnerabilityGeneric.target_host_os),
            joinedload(VulnerabilityGeneric.evidence),
            joinedload(VulnerabilityGeneric.tags),
        ]
        return query.options(selectin_polymorphic(
            VulnerabilityGeneric,
            [Vulnerability, VulnerabilityWeb]
        ), *joinedloads)

    def index(self, page=1, **kwargs):
        """List podcasts and podcast media.

        Our custom paginate decorator allows us to have fewer podcast episodes
        display on the first page than on the rest with the ``items_first_page``
        param. See :class:`mediacore.lib.custompaginate.CustomPage`.

        :param page: Page number, defaults to 1.
        :type page: int
        :rtype: dict
        :returns:
            podcasts
                The :class:`~mediacore.model.podcasts.Podcast` instance
            episodes
                The list of :class:`~mediacore.model.media.Media` instances
                for this page.

        """
        episodes = (
            DBSession.query(Media)
            .filter(Media.podcast_id != None)
            .order_by(Media.publish_on.desc())
            .options(orm.undefer("comment_count_published"))
        )
        episodes = self._filter(episodes)

        podcasts = DBSession.query(Podcast).options(orm.undefer("published_media_count")).all()

        return dict(podcasts=podcasts, episodes=episodes)

def stocklinelist(request, info, session):
    regular = (
        session.query(StockLine)
        .order_by(StockLine.dept_id, StockLine.name)
        .filter(StockLine.linetype == "regular")
        .options(joinedload("stockonsale"))
        .options(joinedload("stockonsale.stocktype"))
        .all()
    )
    display = (
        session.query(StockLine)
        .filter(StockLine.linetype == "display")
        .order_by(StockLine.name)
        .options(joinedload("stockonsale"))
        .options(undefer("stockonsale.used"))
        .all()
    )
    continuous = (
        session.query(StockLine)
        .filter(StockLine.linetype == "continuous")
        .order_by(StockLine.name)
        .options(undefer("stocktype.remaining"))
        .all()
    )
    return ("stocklines.html", {"regular": regular, "display": display, "continuous": continuous})

    def index(self, page=1, search=None, podcast_filter=None, **kwargs):
        """List media with pagination and filtering.

        :param page: Page number, defaults to 1.
        :type page: int
        :param search: Optional search term to filter by
        :type search: unicode or None
        :param podcast_filter: Optional podcast to filter by
        :type podcast_filter: int or None
        :rtype: dict
        :returns:
            media
                The list of :class:`~mediacore.model.media.Media` instances
                for this page.
            search
                The given search term, if any
            search_form
                The :class:`~mediacore.forms.admin.SearchForm` instance
            podcast_filter
                The given podcast ID to filter by, if any
            podcast_filter_title
                The podcast name for rendering if a ``podcast_filter`` was specified.
            podcast_filter_form
                The :class:`~mediacore.forms.media.PodcastFilterForm` instance.


        """
        media = DBSession.query(Media)\
            .filter(Media.status.excludes('trash'))\
            .options(orm.undefer('comment_count_published'))\
            .options(orm.undefer('comment_count_unreviewed'))\
            .order_by(Media.status.desc(),
                      Media.publish_on.desc(),
                      Media.modified_on.desc())

        if search is not None:
            like_search = '%' + search + '%'
            media = media.filter(sql.or_(
                Media.title.like(like_search),
                Media.description.like(like_search),
                Media.notes.like(like_search),
                Media.tags.any(Tag.name.like(like_search)),
            ))

        podcast_filter_title = podcast_filter
        if podcast_filter == 'Unfiled':
            media = media.filter(~Media.podcast.has())
        elif podcast_filter is not None and podcast_filter != 'All Media':
            media = media.filter(Media.podcast.has(Podcast.id == podcast_filter))
            podcast_filter_title = DBSession.query(Podcast.title).get(podcast_filter)
            podcast_filter = int(podcast_filter)

        return dict(
            media = media,
            podcast_filter = podcast_filter,
            podcast_filter_title = podcast_filter_title,
            podcast_filter_form = podcast_filter_form,
            search = search,
            search_form = search_form,
        )

 def _process(self):
     q = request.args["q"].lower()
     query = Category.query.filter(Category.title_matches(q)).options(
         undefer("deep_children_count"),
         undefer("deep_events_count"),
         undefer("has_events"),
         joinedload("acl_entries"),
     )
     if session.user:
         # Prefer favorite categories
         query = query.order_by(
             Category.favorite_of.any(favorite_category_table.c.user_id == session.user.id).desc()
         )
     # Prefer exact matches and matches at the beginning, then order by category title and if
     # those are identical by the chain titles
     query = query.order_by(
         (db.func.lower(Category.title) == q).desc(),
         db.func.lower(Category.title).startswith(q).desc(),
         db.func.lower(Category.title),
         Category.chain_titles,
     )
     total_count = query.count()
     query = query.limit(10)
     return jsonify_data(
         categories=[serialize_category(c, with_favorite=True, with_path=True) for c in query],
         total_count=total_count,
         flash=False,
     )

def get_related_categories(user, detailed=True):
    """Gets the related categories of a user for the dashboard"""
    favorites = set()
    if user.favorite_categories:
        favorites = set(Category.query
                        .filter(Category.id.in_(c.id for c in user.favorite_categories))
                        .options(undefer('chain_titles'))
                        .all())
    managed = set(Category.query
                  .filter(Category.acl_entries.any(db.and_(CategoryPrincipal.type == PrincipalType.user,
                                                           CategoryPrincipal.user == user,
                                                           CategoryPrincipal.has_management_role())),
                          ~Category.is_deleted)
                  .options(undefer('chain_titles')))
    if not detailed:
        return favorites | managed
    res = {}
    for categ in favorites | managed:
        res[(categ.title, categ.id)] = {
            'categ': categ,
            'favorite': categ in favorites,
            'managed': categ in managed,
            'path': truncate_path(categ.chain_titles[:-1], chars=50)
        }
    return OrderedDict(sorted(res.items(), key=itemgetter(0)))

    def test_state_deferred_to_col(self):
        """Behavioral test to verify the current activity of loader callables."""

        users, User = self.tables.users, self.classes.User

        mapper(User, users, properties={"name": deferred(users.c.name)})

        sess = create_session()
        u1 = sess.query(User).options(undefer(User.name)).first()
        assert "name" not in attributes.instance_state(u1).callables

        # mass expire, the attribute was loaded,
        # the attribute gets the callable
        sess.expire(u1)
        assert isinstance(attributes.instance_state(u1).callables["name"], state.InstanceState)

        # load it, callable is gone
        u1.name
        assert "name" not in attributes.instance_state(u1).callables

        # mass expire, attribute was loaded but then deleted,
        # the callable goes away - the state wants to flip
        # it back to its "deferred" loader.
        sess.expunge_all()
        u1 = sess.query(User).options(undefer(User.name)).first()
        del u1.name
        sess.expire(u1)
        assert "name" not in attributes.instance_state(u1).callables

        # single attribute expire, the attribute gets the callable
        sess.expunge_all()
        u1 = sess.query(User).options(undefer(User.name)).first()
        sess.expire(u1, ["name"])
        assert isinstance(attributes.instance_state(u1).callables["name"], state.InstanceState)

    def render(self, session, **arguments):
        q = session.query(Switch)

        q = q.options(subqueryload('location'),
                      subqueryload('interfaces'),
                      joinedload('interfaces.assignments'),
                      joinedload('interfaces.assignments.dns_records'),
                      joinedload('interfaces.assignments.network'),
                      subqueryload('observed_macs'),
                      undefer('observed_macs.creation_date'),
                      subqueryload('observed_vlans'),
                      undefer('observed_vlans.creation_date'),
                      joinedload('observed_vlans.network'),
                      subqueryload('model'),
                      # Switches don't have machine specs, but the formatter
                      # checks for their existence anyway
                      joinedload('model.machine_specs'))

        # Prefer the primary name for ordering
        q = q.outerjoin(DnsRecord, (Fqdn, DnsRecord.fqdn_id == Fqdn.id),
                        DnsDomain)
        q = q.options(contains_eager('primary_name'),
                      contains_eager('primary_name.fqdn'),
                      contains_eager('primary_name.fqdn.dns_domain'))
        q = q.reset_joinpoint()
        q = q.order_by(Fqdn.name, DnsDomain.name, Switch.label)

        return q.all()

    def index(self, page=1, **kwargs):
        """List storage engines with pagination.

        :rtype: Dict
        :returns:
            engines
                The list of :class:`~mediacore.lib.storage.StorageEngine`
                instances for this page.

        """
        engines = DBSession.query(StorageEngine)\
            .options(orm.undefer('file_count'),
                     orm.undefer('file_size_sum'))\
            .all()
        engines = list(sort_engines(engines))
        existing_types = set(ecls.engine_type for ecls in engines)
        addable_engines = [
            ecls
            for ecls in StorageEngine
            if not ecls.is_singleton or ecls.engine_type not in existing_types
        ]

        return {
            'engines': engines,
            'addable_engines': addable_engines,
        }

 def render(self, session, **arguments):
     q = session.query(Domain)
     q = q.options(undefer('comments'),
                   joinedload('owner'),
                   undefer('tracked_branch.comments'))
     q = q.order_by(Domain.name)
     return q.all()

def sessionfinder(request, info, session):
    if request.method == "POST" and "submit_find" in request.POST:
        form = SessionFinderForm(request.POST)
        if form.is_valid():
            s = session.query(Session).get(form.cleaned_data["session"])
            if s:
                return HttpResponseRedirect(info["base"] + s.tillweb_url)
            form.add_error(None, "This session does not exist.")
    else:
        form = SessionFinderForm()
    if request.method == "POST" and "submit_sheet" in request.POST:
        rangeform = SessionRangeForm(request.POST)
        if rangeform.is_valid():
            cd = rangeform.cleaned_data
            return spreadsheets.sessionrange(
                session, start=cd["startdate"], end=cd["enddate"], tillname=info["tillname"]
            )
    else:
        rangeform = SessionRangeForm()
    recent = (
        session.query(Session)
        .options(undefer("total"))
        .options(undefer("actual_total"))
        .order_by(desc(Session.id))[:30]
    )
    return ("sessions.html", {"recent": recent, "form": form, "rangeform": rangeform})

 def render(self, session, **arguments):
     q = session.query(NetworkEnvironment)
     q = q.options(undefer('comments'),
                   joinedload('dns_environment'),
                   undefer('dns_environment.comments'),
                   joinedload('location'))
     q = q.order_by(NetworkEnvironment.name)
     return q.all()

    def news_query(self):
        query = object_session(self).query(News)
        query = query.filter(Page.parent == self)
        query = query.order_by(desc(Page.created))
        query = query.options(undefer('created'))
        query = query.options(undefer('content'))

        return query

    def render(self, session, network_environment, **arguments):
        options = [undefer("comments"),
                   joinedload("dns_environment"),
                   undefer("dns_environment.comments")]
        dbnet_env = NetworkEnvironment.get_unique(session, network_environment,
                                                  compel=True,
                                                  query_options=options)

        return dbnet_env

    def _get_current_posts(self, load_json=False):
        if load_json:
            results = self.db.query(FacebookPost).\
                options(undefer('imported_blob'),
                        undefer('attachment_blob')).\
                filter_by(source=self).all()
            return {x.source_post_id: x for x in results}

        results = self.db.query(FacebookPost).filter_by(
            source=self).all()
        return {x.source_post_id: x for x in results}

def serialize_category_chain(category, include_children=False, include_parents=False):
    data = {'category': serialize_category(category, with_path=True)}
    if include_children:
        data['subcategories'] = [serialize_category(c, with_path=True, parent_path=data['category']['path'])
                                 for c in category.children]
    if include_parents:
        query = (category.parent_chain_query
                 .options(undefer('deep_events_count'), undefer('deep_children_count')))
        data['supercategories'] = [serialize_category(c, with_path=True, child_path=data['category']['path'])
                                   for c in query]
    return data

def session_transactions(request, info, session, sessionid):
    s = session\
        .query(Session)\
        .options(undefer('transactions.total'),
                 undefer('transactions.discount_total'),
                 joinedload('transactions.payments'))\
        .get(int(sessionid))
    if not s:
        raise Http404

    return ('session-transactions.ajax', {'session': s})

def transaction(request, info, session, transid):
    t = session\
        .query(Transaction)\
        .options(subqueryload_all('payments'),
                 joinedload('lines.department'),
                 joinedload('lines.user'),
                 undefer('total'),
                 undefer('discount_total'))\
        .get(int(transid))
    if not t:
        raise Http404
    return ('transaction.html', {'transaction': t, 'tillobject': t})

 def render(self, session, network_environment, **arguments):
     q = session.query(NetworkEnvironment)
     q = q.options(undefer('comments'),
                   joinedload('dns_environment'),
                   undefer('dns_environment.comments'),
                   joinedload('location'))
     if network_environment:
         q = q.filter_by(name=network_environment)
     location = get_location(session, **arguments)
     if location:
         q = q.filter_by(location=location)
     q = q.order_by(NetworkEnvironment.name)
     return q.all()