Card Catalog

Database query walkthrough

Catalog browser query

Asks for filtered card variants, paginated card rows, and grouped book/shelf summaries.

Django path

frontend.services.catalog_service.list_card_page + list_catalog_set_summaries

Tables touched

CardVariant, Card, CardGame, CardSet, CardImage, MarketListing

1. Ask from CardVariant rows because each result represents an exact printing.
2. Ask for related Card, CardGame, CardSet, and CardImage rows through the variant relationship.
3. Apply sidebar filters in SQL: trigram identity search plus game, set, rarity, language, and value range.
4. When Only available is checked, ask EXISTS whether the same variant has an active listing with available quantity.
5. For card view, apply the selected sort order and ask for only the current page with LIMIT and OFFSET.
6. For book and shelf views, ask GROUP BY queries to calculate Count and Sum totals per set or game.
7. Ask PostgreSQL DISTINCT ON for one ordered cover card per book or shelf.

CardVariant.objects.select_related('card__game', 'set', 'image')
    .annotate(search_rank=Greatest(TrigramSimilarity(...)))
    .filter(search_rank__gt=0.1)
    .annotate(has_active_listing=Exists(MarketListing.objects.filter(...)))
    .order_by(...)
    # Paginator applies LIMIT/OFFSET for card view
queryset.values('set_id', 'set__name').annotate(Count('id'), Sum('current_value'))
queryset.order_by('set_id', ...).distinct('set_id')