docs/documentation/aggregates/facets.mdx
A common pattern in search is to query for both an aggregate and a set of search results. For example, "find the top 10 results, and also count the total number of results."
Instead of issuing two separate queries -- one for the search results, and another for the aggregate -- pdb.agg allows for
these results to be returned in a single "faceted" query. This can significantly improve read throughput, since issuing a single
query uses less CPU and disk I/O.
For example, this query returns the top 3 search results alongside the total number of results found.
<CodeGroup> ```sql SQL SELECT id, description, rating, pdb.agg('{"value_count": {"field": "id"}}') OVER () FROM mock_items WHERE category === 'electronics' ORDER BY rating DESC LIMIT 3; ```from django.db.models import Window
from paradedb import Agg, ParadeDB, Term
MockItem.objects.filter(
category=ParadeDB(Term('electronics'))
).values(
'id', 'description', 'rating'
).annotate(
agg=Window(expression=Agg('{"value_count": {"field": "id"}}'))
).order_by('-rating')[:3]
from sqlalchemy import select
from sqlalchemy.orm import Session
from paradedb.sqlalchemy import facets, search
base = (
select(MockItem.id, MockItem.description, MockItem.rating)
.where(
search.all(MockItem.id),
search.term(MockItem.category, "electronics"),
)
.order_by(MockItem.rating.desc())
.limit(3)
)
stmt = facets.with_rows(base, agg=facets.value_count(field="id"), key_field=MockItem.id)
with Session(engine) as session:
rows = session.execute(stmt).all()
facets.extract(rows)
relation = MockItem.search(:category)
.term("electronics")
.with_agg(agg: ParadeDB::Aggregations.value_count(:id))
.select(:id, :description, :rating)
.order(rating: :desc)
.limit(3)
rows = relation.to_a
aggregates = relation.aggregates
id | description | rating | agg
----+-----------------------------+--------+----------------
12 | Innovative wireless earbuds | 5 | {"value": 5.0}
1 | Ergonomic metal keyboard | 4 | {"value": 5.0}
2 | Plastic Keyboard | 4 | {"value": 5.0}
(3 rows)