def active_packages(self, load_eager=True):
     query = Session.query(Package).\
            filter_by(state=vdm.sqlalchemy.State.ACTIVE).\
            join('groups').filter_by(id=self.id)
     if load_eager:
         query = query.options(eagerload_all('package_tags.tag'))
         query = query.options(eagerload_all('package_resources_all'))
     return query

 def get(cls, reference):
     '''Returns a package object referenced by its id or name.'''
     query = Session.query(cls).filter(cls.id==reference)
     query = query.options(eagerload_all('package_tags.tag'))
     query = query.options(eagerload_all('resource_groups_all.resources_all'))
     pkg = query.first()
     if pkg == None:
         pkg = cls.by_name(reference)            
     return pkg

 def read(self, id):
     query = model.Session.query(model.Tag)
     query = query.filter(model.Tag.name == id)
     query = query.options(eagerload_all("package_tags.package"))
     query = query.options(eagerload_all("package_tags.package.package_tags.tag"))
     query = query.options(eagerload_all("package_tags.package.resource_groups_all.resources_all"))
     c.tag = query.first()
     if c.tag is None:
         abort(404)
     return render("tag/read.html")

 def read(self, id):
     query = model.Session.query(model.Tag)
     query = query.filter(model.Tag.name==id)
     query = query.options(eagerload_all('package_tags.package'))
     query = query.options(eagerload_all('package_tags.package.package_tags.tag'))
     query = query.options(eagerload_all('package_tags.package.package_resources_all'))
     c.tag = query.first()
     if c.tag is None:
         abort(404)
     return render('tag/read.html')

 def active_packages(self, load_eager=True):
     query = (
         Session.query(Package)
         .filter_by(state=vdm.sqlalchemy.State.ACTIVE)
         .join("package_group_all", "group")
         .filter_by(id=self.id)
     )
     if load_eager:
         query = query.options(eagerload_all("package_tags.tag"))
         query = query.options(eagerload_all("resource_groups_all.resources_all"))
     return query

    def _get_customer(self, session, customer_id):
        customer = (
            session.query(model.Customer).
            options(
                orm.eagerload_all('payments'),
                orm.eagerload_all('archived_payments')
            ).
            get(customer_id)
        )
        if not customer:
            raise exceptions.NoSuchResource('no customer with id %s' % customer_id)

        return customer

    def _process_delete(self, session, spec):
        query = (
            session.query(model.Invoice).join('manifest', 'plane').join('customer').join('item').
            filter(model.Manifest.manifest_id == spec['manifest_id']).
            filter(model.Plane.plane_id == spec['plane_id'])
        )

        if spec.get('customer_id'):
            query = query.filter(model.Customer.customer_id == spec['customer_id'])

        if spec.get('item_id'):
            query = query.filter(model.Item.item_id == spec['item_id'])

        # Note that model.maintain_balances is invoked as a
        # side-effect, which is why it's not just a delete-call here.
        invoices = query.all()
        for invoice in invoices:
            session.delete(invoice)


        # Delete the entire manifest if no customer was specified.
        if not (spec.get('customer_id') or spec.get('item_id')):
            session.execute(model.Manifest.__table__.delete().
                            where(model.Manifest.manifest_id == spec['manifest_id']).
                            where(model.Manifest.plane_id == spec['plane_id']))

        session.commit()

        if spec.get('customer_id'):
            return (
                session.query(model.Invoice).join('manifest').join('item').
                options(
                    orm.contains_eager('manifest'),
                    orm.contains_eager('item')
                ).
                filter(model.Manifest.manifest_id == spec['manifest_id']).
                filter(model.Invoice.customer_id == spec['customer_id'])
            ).all()

        else:
            return (
                session.query(model.Manifest).
                options(
                    orm.eagerload_all('invoices.item'),
                    orm.eagerload_all('invoices.customer')
                ).
                filter(model.Manifest.plane_id == spec['plane_id'])
            ).all()

def get_tests_query(filter_object=None):

    # get the candidate tests to page through
    query = tst.CandidateTest.query

    # leave out alta tests
    query = query.join('candidate_profile','account')
    #query = query.filter(tst.Account.id!=352)
    query = query.filter(tst.Account.id==957)

    # leave out archived
    query = query.join('test','test','type')
    query = query.join('version')
    query = query.filter(and_(tst.AccountTest.archived==False, tst.Test.archived==False, tst.TestVersion.archived==False))

    # get only reading comprehension
    query = query.filter(tst.TestType.id==6)

    # ensure there are responses
    query = query.join('sections','items')
    query = query.filter(and_(tst.CandidateTestItem.response!=None, tst.CandidateTestItem.response!=''))

    # order by test version
    query = query.order_by(asc(tst.Test.id),asc(tst.TestVersion.id),asc(tst.CandidateTest.local_date))

    # eagerload our responses
    query = query.options(eagerload_all('sections.items'), eagerload('sections.items.item.type.has_response'))

    return query.distinct()

def revision_detail(request, rev):
    q = Revision.q.filter(Revision.svn_id <= rev)
    revision, previous = q.order_by(Revision.svn_id.desc())[:2]

    etag = check_cache(request, 'revision_detail', revision, previous)

    fail_count = func.count(Result.fail)
    failing = (TestFile.failing(revision).group_by(TestFile.id).
               add_column(fail_count).order_by(fail_count.asc()))

    q = Assertion.q.join(Result).filter_by(fail=True, revision=revision)
    q = q.order_by(Assertion.test_id).options(eagerload_all('test.testfile'))
    # Explicit JOIN; otherwise SA generates horrid LEFT OUTER JOINs.
    q = q.join(Test).join(TestFile)

    # We get TestFile.name from the `failing` query above, so we want
    # to look up tests & assertions keyed by a testfile.
    failures = {}
    for test, assertions in itertools.groupby(q, attrgetter('test')):
        fail_list = failures.setdefault(test.testfile.id, [])
        fail_list.append((test, list(assertions)))

    return Context({'revision': revision, 'diff': revision.diff,
                    'failing': failing, 'failures': failures,
                    'broken': revision.broken_tests},
                   headers={'Etag': etag})

 def selectByUserAdresses(db_department, addresses):
     s = db_department.session()
     ret = (
         s.query(Mail).options(eagerload_all("statehistory")).filter(Mail.idreceiveruseraddress.in_(addresses)).all()
     )
     s.close()
     return ret

    def group_list(self, show_hidden=False, list_users=False):
        """Return a simple list of the available groups"""
        groups = query(Group).options(eagerload_all('users_assoc.user')).all()
        me = query(User).get(self.myself_id)

        out = []
        for grp in groups:
            myug = [ug for ug in grp.users_assoc if ug.user_id == me.id]

            res = {'id': grp.id,
                   'name': grp.name,
                   'member': me in grp.users,
                   'hidden': False,
                   'admin': False}

            if grp.hidden:
                # Only for global-admin or members, if we're asked to hide
                # hidden groups
                if not show_hidden and not me.is_admin and not myug:
                    continue
                res['hidden'] = True

            if len(myug):
                res['cryptgroupkey'] = myug[0].cryptgroupkey
                if myug[0].is_admin:
                    res['admin'] = True

            res['members'] = [(u.user_id, u.user.username, u.is_admin)
                              for u in grp.users_assoc]

            out.append(res)

        return vaultMsg(True, 'Here is the list of groups', {'list': out})

 def get(cls, reference):
     '''Returns a tag object referenced by its id or name.'''
     query = Session.query(cls).filter(cls.id==reference)
     query = query.options(eagerload_all('package_tags'))
     tag = query.first()
     if tag == None:
         tag = cls.by_name(reference)
     return tag

def active_folders(trans, folder):
    # Stolen from galaxy.web.controllers.library_common (importing from which causes a circular issues).
    # Much faster way of retrieving all active sub-folders within a given folder than the
    # performance of the mapper.  This query also eagerloads the permissions on each folder.
    return trans.sa_session.query(trans.app.model.LibraryFolder) \
                           .filter_by(parent=folder, deleted=False) \
                           .options(eagerload_all("actions")) \
                           .order_by(trans.app.model.LibraryFolder.table.c.name) \
                           .all()

    def index(self):
        from ckan.lib.helpers import Page

        query = ckan.authz.Authorizer().authorized_query(c.user, model.Group)
        query = query.order_by(model.Group.name.asc())
        query = query.order_by(model.Group.title.asc())
        query = query.options(eagerload_all("packages"))
        c.page = Page(collection=query, page=request.params.get("page", 1), items_per_page=20)
        return render("group/index.html")

    def _get_matching_planes_and_manifests(self, session, plane_id, manifest_id=None):
        query = (
            session.query(model.Plane).outerjoin(model.Manifest).
            filter(model.Plane.plane_id > 0). # There's a "non-manifest" manifest for counter sales, etc.
            filter(model.Plane.is_active == True).
            options(
                orm.eagerload_all('manifests.invoices.item'),
                orm.eagerload_all('manifests.invoices.customer')
            )
        )
        if plane_id:
            query = query.filter(model.Plane.plane_id==plane_id)

        # Not really needed?
        if manifest_id:
            query = query.filter(model.Manifest.manifest_id==manifest_id)

        return query.all()

def get_recent_pages():
    # TODO needs date filter for features
    return (
        session.query(ComicPage)
        .filter(~ ComicPage.is_queued)
        .order_by(ComicPage.order.desc())
        .options(
            eagerload_all(ComicPage.chapter, ComicChapter.comic)
        )
    )

    def index(self):
        from ckan.lib.helpers import Page

        query = ckan.authz.Authorizer().authorized_query(
            c.user, model.AuthorizationGroup)
        query = query.options(eagerload_all('users'))
        c.page = Page(
            collection=query,
            page=request.params.get('page', 1),
            items_per_page=20)
        return render('authorization_group/index.html')

 def get_history_datasets(self, trans, history):
     """
     Returns history's datasets.
     """
     query = (trans.sa_session.query(trans.model.HistoryDatasetAssociation)
              .filter(trans.model.HistoryDatasetAssociation.history == history)
              .join("dataset")
              .options(eagerload_all("dataset.actions"))
              .order_by(trans.model.HistoryDatasetAssociation.hid)
              .filter(trans.model.HistoryDatasetAssociation.deleted == expression.false())
              .filter(trans.model.Dataset.purged == expression.false()))
     return query.all()

    def all_q(cls, instance=None, include_deleted=False):
        from membership import Membership

        q = meta.Session.query(User)
        if not include_deleted:
            q = q.filter(or_(User.delete_time == None, User.delete_time > datetime.utcnow()))
        if instance:
            q = q.options(eagerload_all("memberships"))
            q = q.join(Membership)
            q = q.filter(or_(Membership.expire_time == None, Membership.expire_time > datetime.utcnow()))
            q = q.filter(Membership.instance == instance)
        return q

    def index(self):
        from ckan.lib.helpers import Page

        try:
            context = {"model": model, "user": c.user or c.author}
            check_access("site_read", context)
        except NotAuthorized:
            abort(401, _("Not authorized to see this page"))

        query = ckan.authz.Authorizer().authorized_query(c.user, model.AuthorizationGroup)
        query = query.options(eagerload_all("users"))
        c.page = Page(collection=query, page=request.params.get("page", 1), items_per_page=20)
        return render("authorization_group/index.html")