Product Inventory Query System

Build a product inventory query system that allows filtering, sorting, and paginating products from a database.

Requirements

Create a Python module that provides functions to query product inventory data. The system should support:

1. Filtering products by various criteria:

   • Products above a certain price threshold
   • Products within a specific price range
   • Products with stock below a threshold
   • Products in specific categories

2. Sorting:

   • Sort products by price (ascending or descending)

3. Pagination:

   • Retrieve a specific page of results with configurable page size

4. Combining filters:

   • Apply multiple conditions simultaneously (e.g., price range AND category AND stock level)
   • Support OR conditions for category filters

Product Model

Define a Product database model with these fields:

• id: Integer, primary key (auto-generated)
• name: String, required
• category: String, required (e.g., "Electronics", "Books", "Clothing")
• price: Float, required (product price in dollars)
• stock_quantity: Integer, required (number of items in stock)

Implementation

@generates

Create a module with the following query functions:

1. get_products_above_price(session, min_price) - Returns products priced above the threshold
2. get_products_in_price_range(session, min_price, max_price) - Returns products within price range
3. get_low_stock_products(session, threshold) - Returns products with stock below threshold
4. get_products_by_categories(session, categories) - Returns products in specified categories (list)
5. get_products_sorted_by_price(session, descending=False) - Returns products sorted by price
6. get_paginated_products(session, page, page_size) - Returns a specific page of products
7. get_filtered_products(session, min_price=None, max_price=None, categories=None, min_stock=None) - Returns products matching all provided filters

Test Cases

• Querying products with price greater than $50 returns only products above that price @test
• Querying products in price range $20-$100 returns only products within that range @test
• Querying products with low stock (below 10) returns only low-stock items @test
• Querying products in multiple categories returns products from any of those categories @test
• Sorting products by price in descending order returns highest-priced products first @test
• Paginating results with page size 5 on page 2 returns records 6-10 @test
• Combining multiple filters (price range, category, stock level) returns only products matching all criteria @test

API

from typing import Optional
from sqlmodel import SQLModel, Field

class Product(SQLModel, table=True):
    """Product inventory model."""
    id: Optional[int] = Field(default=None, primary_key=True)
    name: str
    category: str
    price: float
    stock_quantity: int

def get_products_above_price(session, min_price: float) -> list[Product]:
    """Return all products with price greater than min_price."""
    pass

def get_products_in_price_range(session, min_price: float, max_price: float) -> list[Product]:
    """Return all products with price between min_price and max_price (inclusive)."""
    pass

def get_low_stock_products(session, threshold: int) -> list[Product]:
    """Return all products with stock_quantity less than threshold."""
    pass

def get_products_by_categories(session, categories: list[str]) -> list[Product]:
    """Return all products belonging to any of the specified categories."""
    pass

def get_products_sorted_by_price(session, descending: bool = False) -> list[Product]:
    """Return all products sorted by price."""
    pass

def get_paginated_products(session, page: int, page_size: int) -> list[Product]:
    """Return a specific page of products (page is 1-indexed)."""
    pass

def get_filtered_products(
    session,
    min_price: Optional[float] = None,
    max_price: Optional[float] = None,
    categories: Optional[list[str]] = None,
    min_stock: Optional[int] = None
) -> list[Product]:
    """Return all products matching all provided filter criteria."""
    pass

Dependencies { .dependencies }

sqlmodel { .dependency }

Provides database ORM and query building capabilities.