We are currently solving the following issue within our repository. Here is the issue text:
--- BEGIN ISSUE ---
Remove unused classifiers from filter list
We currently show all trove classifiers in the search filter panel, despite the fact that some are not applied to any projects in the DB.

It would be better to only show those classifiers that are actually applied to projects, so we avoid filtering by a classifier and returning an empty result.


--- END ISSUE ---

Below are some code segments, each from a relevant file. One or more of these files may contain bugs.
--- BEGIN FILES ---
Path: `warehouse/views.py`
Content:
```
1 # Licensed under the Apache License, Version 2.0 (the "License");
2 # you may not use this file except in compliance with the License.
3 # You may obtain a copy of the License at
4 #
5 # http://www.apache.org/licenses/LICENSE-2.0
6 #
7 # Unless required by applicable law or agreed to in writing, software
8 # distributed under the License is distributed on an "AS IS" BASIS,
9 # WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
10 # See the License for the specific language governing permissions and
11 # limitations under the License.
12 
13 import collections
14 
15 from pyramid.httpexceptions import (
16     HTTPException, HTTPSeeOther, HTTPMovedPermanently, HTTPNotFound,
17     HTTPBadRequest,
18 )
19 from pyramid.view import (
20     notfound_view_config, forbidden_view_config, view_config,
21 )
22 from elasticsearch_dsl import Q
23 from sqlalchemy import func
24 from sqlalchemy.orm import aliased, joinedload
25 
26 from warehouse.accounts import REDIRECT_FIELD_NAME
27 from warehouse.accounts.models import User
28 from warehouse.cache.origin import origin_cache
29 from warehouse.cache.http import cache_control
30 from warehouse.classifiers.models import Classifier
31 from warehouse.packaging.models import Project, Release, File
32 from warehouse.utils.row_counter import RowCount
33 from warehouse.utils.paginate import ElasticsearchPage, paginate_url_factory
34 
35 
36 SEARCH_FIELDS = [
37     "author", "author_email", "description", "download_url", "home_page",
38     "keywords", "license", "maintainer", "maintainer_email", "normalized_name",
39     "platform", "summary",
40 ]
41 SEARCH_BOOSTS = {
42     "normalized_name": 10,
43     "description": 5,
44     "keywords": 5,
45     "summary": 5,
46 }
47 SEARCH_FILTER_ORDER = (
48     "Programming Language",
49     "License",
50     "Framework",
51     "Topic",
52     "Intended Audience",
53     "Environment",
54     "Operating System",
55     "Natural Language",
56     "Development Status",
57 )
58 
59 
60 @view_config(context=HTTPException)
61 @notfound_view_config(append_slash=HTTPMovedPermanently)
62 def httpexception_view(exc, request):
63     return exc
64 
65 
66 @forbidden_view_config()
67 def forbidden(exc, request):
68     # If the forbidden error is because the user isn't logged in, then we'll
69     # redirect them to the log in page.
70     if request.authenticated_userid is None:
71         url = request.route_url(
72             "accounts.login",
73             _query={REDIRECT_FIELD_NAME: request.path_qs},
74         )
75         return HTTPSeeOther(url)
76 
77     # If we've reached here, then the user is logged in and they are genuinely
78     # not allowed to access this page.
79     # TODO: Style the forbidden page.
80     return exc
81 
82 
83 @view_config(
84     route_name="robots.txt",
85     renderer="robots.txt",
86     decorator=[
87         cache_control(1 * 24 * 60 * 60),         # 1 day
88         origin_cache(
89             1 * 24 * 60 * 60,                    # 1 day
90             stale_while_revalidate=6 * 60 * 60,  # 6 hours
91             stale_if_error=1 * 24 * 60 * 60,     # 1 day
92         ),
93     ],
94 )
95 def robotstxt(request):
96     request.response.content_type = "text/plain"
97     return {}
98 
99 
100 @view_config(
101     route_name="index",
102     renderer="index.html",
103     decorator=[
104         origin_cache(
105             1 * 60 * 60,                      # 1 hour
106             stale_while_revalidate=10 * 60,   # 10 minutes
107             stale_if_error=1 * 24 * 60 * 60,  # 1 day
108             keys=["all-projects"],
109         ),
110     ]
111 )
112 def index(request):
113     project_names = [
114         r[0] for r in (
115             request.db.query(File.name)
116                    .group_by(File.name)
117                    .order_by(func.sum(File.downloads).desc())
118                    .limit(5)
119                    .all())
120     ]
121     release_a = aliased(
122         Release,
123         request.db.query(Release)
124                   .distinct(Release.name)
125                   .filter(Release.name.in_(project_names))
126                   .order_by(Release.name, Release._pypi_ordering.desc())
127                   .subquery(),
128     )
129     top_projects = (
130         request.db.query(release_a)
131                .options(joinedload(release_a.project))
132                .order_by(func.array_idx(project_names, release_a.name))
133                .all()
134     )
135 
136     latest_releases = (
137         request.db.query(Release)
138                   .options(joinedload(Release.project))
139                   .order_by(Release.created.desc())
140                   .limit(5)
141                   .all()
142     )
143 
144     counts = dict(
145         request.db.query(RowCount.table_name, RowCount.count)
146                   .filter(
147                       RowCount.table_name.in_([
148                           Project.__tablename__,
149                           Release.__tablename__,
150                           File.__tablename__,
151                           User.__tablename__,
152                       ]))
153                   .all()
154     )
155 
156     return {
157         "latest_releases": latest_releases,
158         "top_projects": top_projects,
159         "num_projects": counts.get(Project.__tablename__, 0),
160         "num_releases": counts.get(Release.__tablename__, 0),
161         "num_files": counts.get(File.__tablename__, 0),
162         "num_users": counts.get(User.__tablename__, 0),
163     }
164 
165 
166 @view_config(
167     route_name="search",
168     renderer="search/results.html",
169     decorator=[
170         origin_cache(
171             1 * 60 * 60,                      # 1 hour
172             stale_while_revalidate=10 * 60,   # 10 minutes
173             stale_if_error=1 * 24 * 60 * 60,  # 1 day
174             keys=["all-projects"],
175         )
176     ],
177 )
178 def search(request):
179 
180     q = request.params.get("q", '')
181 
182     if q:
183         should = []
184         for field in SEARCH_FIELDS:
185             kw = {"query": q}
186             if field in SEARCH_BOOSTS:
187                 kw["boost"] = SEARCH_BOOSTS[field]
188             should.append(Q("match", **{field: kw}))
189 
190         # Add a prefix query if ``q`` is longer than one character.
191         if len(q) > 1:
192             should.append(Q('prefix', normalized_name=q))
193 
194         query = request.es.query("dis_max", queries=should)
195         query = query.suggest("name_suggestion", q, term={"field": "name"})
196     else:
197         query = request.es.query()
198 
199     if request.params.get("o"):
200         query = query.sort(request.params["o"])
201 
202     if request.params.getall("c"):
203         query = query.filter("terms", classifiers=request.params.getall("c"))
204 
205     try:
206         page_num = int(request.params.get("page", 1))
207     except ValueError:
208         raise HTTPBadRequest("'page' must be an integer.")
209 
210     page = ElasticsearchPage(
211         query,
212         page=page_num,
213         url_maker=paginate_url_factory(request),
214     )
215 
216     if page.page_count and page_num > page.page_count:
217         return HTTPNotFound()
218 
219     available_filters = collections.defaultdict(list)
220 
221     for cls in request.db.query(Classifier).order_by(Classifier.classifier):
222         first, *_ = cls.classifier.split(' :: ')
223         available_filters[first].append(cls.classifier)
224 
225     def filter_key(item):
226         try:
227             return 0, SEARCH_FILTER_ORDER.index(item[0]), item[0]
228         except ValueError:
229             return 1, 0, item[0]
230 
231     return {
232         "page": page,
233         "term": q,
234         "order": request.params.get("o", ''),
235         "available_filters": sorted(available_filters.items(), key=filter_key),
236         "applied_filters": request.params.getall("c"),
237     }
238 
239 
240 @view_config(
241     route_name="includes.current-user-indicator",
242     renderer="includes/current-user-indicator.html",
243     uses_session=True,
244 )
245 def current_user_indicator(request):
246     return {}
247 
248 
249 @view_config(route_name="health", renderer="string")
250 def health(request):
251     # This will ensure that we can access the database and run queries against
252     # it without doing anything that will take a lock or block other queries.
253     request.db.execute("SELECT 1")
254 
255     # Nothing will actually check this, but it's a little nicer to have
256     # something to return besides an empty body.
257     return "OK"
258
```
--- END FILES ---

Please first localize the bug based on the issue statement, and then generate a patch according to the `git diff` format fenced by three backticks.

Here is an example:

```diff
diff --git a/examples/server_async.py b/examples/server_async.py
--- a/examples/server_async.py
+++ b/examples/server_async.py
@@ -313,4 +313,4 @@
 
 
 if __name__ == "__main__":
-    asyncio.run(run_async_server("."), debug=True)
+    asyncio.run(run_async_server(), debug=True)
diff --git a/examples/server_sync.py b/examples/server_sync.py
--- a/examples/server_sync.py
+++ b/examples/server_sync.py
@@ -313,5 +313,5 @@
 
 
 if __name__ == "__main__":
-    server = run_sync_server(".")
+    server = run_sync_server()
     server.shutdown()

```

diff --git a/warehouse/views.py b/warehouse/views.py
--- a/warehouse/views.py
+++ b/warehouse/views.py
@@ -22,13 +22,16 @@
 from elasticsearch_dsl import Q
 from sqlalchemy import func
 from sqlalchemy.orm import aliased, joinedload
+from sqlalchemy.sql import exists
 
 from warehouse.accounts import REDIRECT_FIELD_NAME
 from warehouse.accounts.models import User
 from warehouse.cache.origin import origin_cache
 from warehouse.cache.http import cache_control
 from warehouse.classifiers.models import Classifier
-from warehouse.packaging.models import Project, Release, File
+from warehouse.packaging.models import (
+    Project, Release, File, release_classifiers,
+)
 from warehouse.utils.row_counter import RowCount
 from warehouse.utils.paginate import ElasticsearchPage, paginate_url_factory
 
@@ -218,7 +221,17 @@
 
     available_filters = collections.defaultdict(list)
 
-    for cls in request.db.query(Classifier).order_by(Classifier.classifier):
+    classifiers_q = (
+        request.db.query(Classifier)
+        .with_entities(Classifier.classifier)
+        .filter(
+            exists([release_classifiers.c.trove_id])
+            .where(release_classifiers.c.trove_id == Classifier.id)
+        )
+        .order_by(Classifier.classifier)
+    )
+
+    for cls in classifiers_q:
         first, *_ = cls.classifier.split(' :: ')
         available_filters[first].append(cls.classifier)

{"golden_diff": "diff --git a/warehouse/views.py b/warehouse/views.py\n--- a/warehouse/views.py\n+++ b/warehouse/views.py\n@@ -22,13 +22,16 @@\n from elasticsearch_dsl import Q\n from sqlalchemy import func\n from sqlalchemy.orm import aliased, joinedload\n+from sqlalchemy.sql import exists\n \n from warehouse.accounts import REDIRECT_FIELD_NAME\n from warehouse.accounts.models import User\n from warehouse.cache.origin import origin_cache\n from warehouse.cache.http import cache_control\n from warehouse.classifiers.models import Classifier\n-from warehouse.packaging.models import Project, Release, File\n+from warehouse.packaging.models import (\n+    Project, Release, File, release_classifiers,\n+)\n from warehouse.utils.row_counter import RowCount\n from warehouse.utils.paginate import ElasticsearchPage, paginate_url_factory\n \n@@ -218,7 +221,17 @@\n \n     available_filters = collections.defaultdict(list)\n \n-    for cls in request.db.query(Classifier).order_by(Classifier.classifier):\n+    classifiers_q = (\n+        request.db.query(Classifier)\n+        .with_entities(Classifier.classifier)\n+        .filter(\n+            exists([release_classifiers.c.trove_id])\n+            .where(release_classifiers.c.trove_id == Classifier.id)\n+        )\n+        .order_by(Classifier.classifier)\n+    )\n+\n+    for cls in classifiers_q:\n         first, *_ = cls.classifier.split(' :: ')\n         available_filters[first].append(cls.classifier)\n", "issue": "Remove unused classifiers from filter list\nWe currently show all trove classifiers in the search filter panel, despite the fact that some are not applied to any projects in the DB.\n\nIt would be better to only show those classifiers that are actually applied to projects, so we avoid filtering by a classifier and returning an empty result.\n\n", "before_files": [{"content": "# Licensed under the Apache License, Version 2.0 (the \"License\");\n# you may not use this file except in compliance with the License.\n# You may obtain a copy of the License at\n#\n# http://www.apache.org/licenses/LICENSE-2.0\n#\n# Unless required by applicable law or agreed to in writing, software\n# distributed under the License is distributed on an \"AS IS\" BASIS,\n# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.\n# See the License for the specific language governing permissions and\n# limitations under the License.\n\nimport collections\n\nfrom pyramid.httpexceptions import (\n    HTTPException, HTTPSeeOther, HTTPMovedPermanently, HTTPNotFound,\n    HTTPBadRequest,\n)\nfrom pyramid.view import (\n    notfound_view_config, forbidden_view_config, view_config,\n)\nfrom elasticsearch_dsl import Q\nfrom sqlalchemy import func\nfrom sqlalchemy.orm import aliased, joinedload\n\nfrom warehouse.accounts import REDIRECT_FIELD_NAME\nfrom warehouse.accounts.models import User\nfrom warehouse.cache.origin import origin_cache\nfrom warehouse.cache.http import cache_control\nfrom warehouse.classifiers.models import Classifier\nfrom warehouse.packaging.models import Project, Release, File\nfrom warehouse.utils.row_counter import RowCount\nfrom warehouse.utils.paginate import ElasticsearchPage, paginate_url_factory\n\n\nSEARCH_FIELDS = [\n    \"author\", \"author_email\", \"description\", \"download_url\", \"home_page\",\n    \"keywords\", \"license\", \"maintainer\", \"maintainer_email\", \"normalized_name\",\n    \"platform\", \"summary\",\n]\nSEARCH_BOOSTS = {\n    \"normalized_name\": 10,\n    \"description\": 5,\n    \"keywords\": 5,\n    \"summary\": 5,\n}\nSEARCH_FILTER_ORDER = (\n    \"Programming Language\",\n    \"License\",\n    \"Framework\",\n    \"Topic\",\n    \"Intended Audience\",\n    \"Environment\",\n    \"Operating System\",\n    \"Natural Language\",\n    \"Development Status\",\n)\n\n\n@view_config(context=HTTPException)\n@notfound_view_config(append_slash=HTTPMovedPermanently)\ndef httpexception_view(exc, request):\n    return exc\n\n\n@forbidden_view_config()\ndef forbidden(exc, request):\n    # If the forbidden error is because the user isn't logged in, then we'll\n    # redirect them to the log in page.\n    if request.authenticated_userid is None:\n        url = request.route_url(\n            \"accounts.login\",\n            _query={REDIRECT_FIELD_NAME: request.path_qs},\n        )\n        return HTTPSeeOther(url)\n\n    # If we've reached here, then the user is logged in and they are genuinely\n    # not allowed to access this page.\n    # TODO: Style the forbidden page.\n    return exc\n\n\n@view_config(\n    route_name=\"robots.txt\",\n    renderer=\"robots.txt\",\n    decorator=[\n        cache_control(1 * 24 * 60 * 60),         # 1 day\n        origin_cache(\n            1 * 24 * 60 * 60,                    # 1 day\n            stale_while_revalidate=6 * 60 * 60,  # 6 hours\n            stale_if_error=1 * 24 * 60 * 60,     # 1 day\n        ),\n    ],\n)\ndef robotstxt(request):\n    request.response.content_type = \"text/plain\"\n    return {}\n\n\n@view_config(\n    route_name=\"index\",\n    renderer=\"index.html\",\n    decorator=[\n        origin_cache(\n            1 * 60 * 60,                      # 1 hour\n            stale_while_revalidate=10 * 60,   # 10 minutes\n            stale_if_error=1 * 24 * 60 * 60,  # 1 day\n            keys=[\"all-projects\"],\n        ),\n    ]\n)\ndef index(request):\n    project_names = [\n        r[0] for r in (\n            request.db.query(File.name)\n                   .group_by(File.name)\n                   .order_by(func.sum(File.downloads).desc())\n                   .limit(5)\n                   .all())\n    ]\n    release_a = aliased(\n        Release,\n        request.db.query(Release)\n                  .distinct(Release.name)\n                  .filter(Release.name.in_(project_names))\n                  .order_by(Release.name, Release._pypi_ordering.desc())\n                  .subquery(),\n    )\n    top_projects = (\n        request.db.query(release_a)\n               .options(joinedload(release_a.project))\n               .order_by(func.array_idx(project_names, release_a.name))\n               .all()\n    )\n\n    latest_releases = (\n        request.db.query(Release)\n                  .options(joinedload(Release.project))\n                  .order_by(Release.created.desc())\n                  .limit(5)\n                  .all()\n    )\n\n    counts = dict(\n        request.db.query(RowCount.table_name, RowCount.count)\n                  .filter(\n                      RowCount.table_name.in_([\n                          Project.__tablename__,\n                          Release.__tablename__,\n                          File.__tablename__,\n                          User.__tablename__,\n                      ]))\n                  .all()\n    )\n\n    return {\n        \"latest_releases\": latest_releases,\n        \"top_projects\": top_projects,\n        \"num_projects\": counts.get(Project.__tablename__, 0),\n        \"num_releases\": counts.get(Release.__tablename__, 0),\n        \"num_files\": counts.get(File.__tablename__, 0),\n        \"num_users\": counts.get(User.__tablename__, 0),\n    }\n\n\n@view_config(\n    route_name=\"search\",\n    renderer=\"search/results.html\",\n    decorator=[\n        origin_cache(\n            1 * 60 * 60,                      # 1 hour\n            stale_while_revalidate=10 * 60,   # 10 minutes\n            stale_if_error=1 * 24 * 60 * 60,  # 1 day\n            keys=[\"all-projects\"],\n        )\n    ],\n)\ndef search(request):\n\n    q = request.params.get(\"q\", '')\n\n    if q:\n        should = []\n        for field in SEARCH_FIELDS:\n            kw = {\"query\": q}\n            if field in SEARCH_BOOSTS:\n                kw[\"boost\"] = SEARCH_BOOSTS[field]\n            should.append(Q(\"match\", **{field: kw}))\n\n        # Add a prefix query if ``q`` is longer than one character.\n        if len(q) > 1:\n            should.append(Q('prefix', normalized_name=q))\n\n        query = request.es.query(\"dis_max\", queries=should)\n        query = query.suggest(\"name_suggestion\", q, term={\"field\": \"name\"})\n    else:\n        query = request.es.query()\n\n    if request.params.get(\"o\"):\n        query = query.sort(request.params[\"o\"])\n\n    if request.params.getall(\"c\"):\n        query = query.filter(\"terms\", classifiers=request.params.getall(\"c\"))\n\n    try:\n        page_num = int(request.params.get(\"page\", 1))\n    except ValueError:\n        raise HTTPBadRequest(\"'page' must be an integer.\")\n\n    page = ElasticsearchPage(\n        query,\n        page=page_num,\n        url_maker=paginate_url_factory(request),\n    )\n\n    if page.page_count and page_num > page.page_count:\n        return HTTPNotFound()\n\n    available_filters = collections.defaultdict(list)\n\n    for cls in request.db.query(Classifier).order_by(Classifier.classifier):\n        first, *_ = cls.classifier.split(' :: ')\n        available_filters[first].append(cls.classifier)\n\n    def filter_key(item):\n        try:\n            return 0, SEARCH_FILTER_ORDER.index(item[0]), item[0]\n        except ValueError:\n            return 1, 0, item[0]\n\n    return {\n        \"page\": page,\n        \"term\": q,\n        \"order\": request.params.get(\"o\", ''),\n        \"available_filters\": sorted(available_filters.items(), key=filter_key),\n        \"applied_filters\": request.params.getall(\"c\"),\n    }\n\n\n@view_config(\n    route_name=\"includes.current-user-indicator\",\n    renderer=\"includes/current-user-indicator.html\",\n    uses_session=True,\n)\ndef current_user_indicator(request):\n    return {}\n\n\n@view_config(route_name=\"health\", renderer=\"string\")\ndef health(request):\n    # This will ensure that we can access the database and run queries against\n    # it without doing anything that will take a lock or block other queries.\n    request.db.execute(\"SELECT 1\")\n\n    # Nothing will actually check this, but it's a little nicer to have\n    # something to return besides an empty body.\n    return \"OK\"\n", "path": "warehouse/views.py"}], "after_files": [{"content": "# Licensed under the Apache License, Version 2.0 (the \"License\");\n# you may not use this file except in compliance with the License.\n# You may obtain a copy of the License at\n#\n# http://www.apache.org/licenses/LICENSE-2.0\n#\n# Unless required by applicable law or agreed to in writing, software\n# distributed under the License is distributed on an \"AS IS\" BASIS,\n# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.\n# See the License for the specific language governing permissions and\n# limitations under the License.\n\nimport collections\n\nfrom pyramid.httpexceptions import (\n    HTTPException, HTTPSeeOther, HTTPMovedPermanently, HTTPNotFound,\n    HTTPBadRequest,\n)\nfrom pyramid.view import (\n    notfound_view_config, forbidden_view_config, view_config,\n)\nfrom elasticsearch_dsl import Q\nfrom sqlalchemy import func\nfrom sqlalchemy.orm import aliased, joinedload\nfrom sqlalchemy.sql import exists\n\nfrom warehouse.accounts import REDIRECT_FIELD_NAME\nfrom warehouse.accounts.models import User\nfrom warehouse.cache.origin import origin_cache\nfrom warehouse.cache.http import cache_control\nfrom warehouse.classifiers.models import Classifier\nfrom warehouse.packaging.models import (\n    Project, Release, File, release_classifiers,\n)\nfrom warehouse.utils.row_counter import RowCount\nfrom warehouse.utils.paginate import ElasticsearchPage, paginate_url_factory\n\n\nSEARCH_FIELDS = [\n    \"author\", \"author_email\", \"description\", \"download_url\", \"home_page\",\n    \"keywords\", \"license\", \"maintainer\", \"maintainer_email\", \"normalized_name\",\n    \"platform\", \"summary\",\n]\nSEARCH_BOOSTS = {\n    \"normalized_name\": 10,\n    \"description\": 5,\n    \"keywords\": 5,\n    \"summary\": 5,\n}\nSEARCH_FILTER_ORDER = (\n    \"Programming Language\",\n    \"License\",\n    \"Framework\",\n    \"Topic\",\n    \"Intended Audience\",\n    \"Environment\",\n    \"Operating System\",\n    \"Natural Language\",\n    \"Development Status\",\n)\n\n\n@view_config(context=HTTPException)\n@notfound_view_config(append_slash=HTTPMovedPermanently)\ndef httpexception_view(exc, request):\n    return exc\n\n\n@forbidden_view_config()\ndef forbidden(exc, request):\n    # If the forbidden error is because the user isn't logged in, then we'll\n    # redirect them to the log in page.\n    if request.authenticated_userid is None:\n        url = request.route_url(\n            \"accounts.login\",\n            _query={REDIRECT_FIELD_NAME: request.path_qs},\n        )\n        return HTTPSeeOther(url)\n\n    # If we've reached here, then the user is logged in and they are genuinely\n    # not allowed to access this page.\n    # TODO: Style the forbidden page.\n    return exc\n\n\n@view_config(\n    route_name=\"robots.txt\",\n    renderer=\"robots.txt\",\n    decorator=[\n        cache_control(1 * 24 * 60 * 60),         # 1 day\n        origin_cache(\n            1 * 24 * 60 * 60,                    # 1 day\n            stale_while_revalidate=6 * 60 * 60,  # 6 hours\n            stale_if_error=1 * 24 * 60 * 60,     # 1 day\n        ),\n    ],\n)\ndef robotstxt(request):\n    request.response.content_type = \"text/plain\"\n    return {}\n\n\n@view_config(\n    route_name=\"index\",\n    renderer=\"index.html\",\n    decorator=[\n        origin_cache(\n            1 * 60 * 60,                      # 1 hour\n            stale_while_revalidate=10 * 60,   # 10 minutes\n            stale_if_error=1 * 24 * 60 * 60,  # 1 day\n            keys=[\"all-projects\"],\n        ),\n    ]\n)\ndef index(request):\n    project_names = [\n        r[0] for r in (\n            request.db.query(File.name)\n                   .group_by(File.name)\n                   .order_by(func.sum(File.downloads).desc())\n                   .limit(5)\n                   .all())\n    ]\n    release_a = aliased(\n        Release,\n        request.db.query(Release)\n                  .distinct(Release.name)\n                  .filter(Release.name.in_(project_names))\n                  .order_by(Release.name, Release._pypi_ordering.desc())\n                  .subquery(),\n    )\n    top_projects = (\n        request.db.query(release_a)\n               .options(joinedload(release_a.project))\n               .order_by(func.array_idx(project_names, release_a.name))\n               .all()\n    )\n\n    latest_releases = (\n        request.db.query(Release)\n                  .options(joinedload(Release.project))\n                  .order_by(Release.created.desc())\n                  .limit(5)\n                  .all()\n    )\n\n    counts = dict(\n        request.db.query(RowCount.table_name, RowCount.count)\n                  .filter(\n                      RowCount.table_name.in_([\n                          Project.__tablename__,\n                          Release.__tablename__,\n                          File.__tablename__,\n                          User.__tablename__,\n                      ]))\n                  .all()\n    )\n\n    return {\n        \"latest_releases\": latest_releases,\n        \"top_projects\": top_projects,\n        \"num_projects\": counts.get(Project.__tablename__, 0),\n        \"num_releases\": counts.get(Release.__tablename__, 0),\n        \"num_files\": counts.get(File.__tablename__, 0),\n        \"num_users\": counts.get(User.__tablename__, 0),\n    }\n\n\n@view_config(\n    route_name=\"search\",\n    renderer=\"search/results.html\",\n    decorator=[\n        origin_cache(\n            1 * 60 * 60,                      # 1 hour\n            stale_while_revalidate=10 * 60,   # 10 minutes\n            stale_if_error=1 * 24 * 60 * 60,  # 1 day\n            keys=[\"all-projects\"],\n        )\n    ],\n)\ndef search(request):\n\n    q = request.params.get(\"q\", '')\n\n    if q:\n        should = []\n        for field in SEARCH_FIELDS:\n            kw = {\"query\": q}\n            if field in SEARCH_BOOSTS:\n                kw[\"boost\"] = SEARCH_BOOSTS[field]\n            should.append(Q(\"match\", **{field: kw}))\n\n        # Add a prefix query if ``q`` is longer than one character.\n        if len(q) > 1:\n            should.append(Q('prefix', normalized_name=q))\n\n        query = request.es.query(\"dis_max\", queries=should)\n        query = query.suggest(\"name_suggestion\", q, term={\"field\": \"name\"})\n    else:\n        query = request.es.query()\n\n    if request.params.get(\"o\"):\n        query = query.sort(request.params[\"o\"])\n\n    if request.params.getall(\"c\"):\n        query = query.filter(\"terms\", classifiers=request.params.getall(\"c\"))\n\n    try:\n        page_num = int(request.params.get(\"page\", 1))\n    except ValueError:\n        raise HTTPBadRequest(\"'page' must be an integer.\")\n\n    page = ElasticsearchPage(\n        query,\n        page=page_num,\n        url_maker=paginate_url_factory(request),\n    )\n\n    if page.page_count and page_num > page.page_count:\n        return HTTPNotFound()\n\n    available_filters = collections.defaultdict(list)\n\n    classifiers_q = (\n        request.db.query(Classifier)\n        .with_entities(Classifier.classifier)\n        .filter(\n            exists([release_classifiers.c.trove_id])\n            .where(release_classifiers.c.trove_id == Classifier.id)\n        )\n        .order_by(Classifier.classifier)\n    )\n\n    for cls in classifiers_q:\n        first, *_ = cls.classifier.split(' :: ')\n        available_filters[first].append(cls.classifier)\n\n    def filter_key(item):\n        try:\n            return 0, SEARCH_FILTER_ORDER.index(item[0]), item[0]\n        except ValueError:\n            return 1, 0, item[0]\n\n    return {\n        \"page\": page,\n        \"term\": q,\n        \"order\": request.params.get(\"o\", ''),\n        \"available_filters\": sorted(available_filters.items(), key=filter_key),\n        \"applied_filters\": request.params.getall(\"c\"),\n    }\n\n\n@view_config(\n    route_name=\"includes.current-user-indicator\",\n    renderer=\"includes/current-user-indicator.html\",\n    uses_session=True,\n)\ndef current_user_indicator(request):\n    return {}\n\n\n@view_config(route_name=\"health\", renderer=\"string\")\ndef health(request):\n    # This will ensure that we can access the database and run queries against\n    # it without doing anything that will take a lock or block other queries.\n    request.db.execute(\"SELECT 1\")\n\n    # Nothing will actually check this, but it's a little nicer to have\n    # something to return besides an empty body.\n    return \"OK\"\n", "path": "warehouse/views.py"}]}