Jonathan
486749a890
Full-stack Dutch supermarket price tracker with FastAPI backend, PostgreSQL/SQLAlchemy, Albert Heijn scraper, and Next.js frontend. Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>
58 lines
1.8 KiB
Python
58 lines
1.8 KiB
Python
from datetime import date, datetime
|
|
|
|
from fastapi import APIRouter, Depends, Query
|
|
from sqlalchemy import func, select
|
|
from sqlalchemy.orm import Session, selectinload
|
|
|
|
from ..database import get_db
|
|
from ..models import PriceSnapshot, Product
|
|
from ..schemas import CheapestProduct, Product as ProductSchema
|
|
|
|
router = APIRouter(prefix="/api/prices", tags=["prices"])
|
|
|
|
|
|
@router.get("/cheapest", response_model=list[CheapestProduct])
|
|
def get_cheapest(
|
|
date_filter: date = Query(default=None, alias="date"),
|
|
limit: int = Query(default=20, le=100),
|
|
db: Session = Depends(get_db),
|
|
):
|
|
target = date_filter or date.today()
|
|
day_start = datetime(target.year, target.month, target.day, 0, 0, 0)
|
|
day_end = datetime(target.year, target.month, target.day, 23, 59, 59)
|
|
|
|
min_per_product = (
|
|
select(
|
|
PriceSnapshot.product_id,
|
|
func.min(PriceSnapshot.price).label("min_price"),
|
|
)
|
|
.where(PriceSnapshot.timestamp.between(day_start, day_end))
|
|
.group_by(PriceSnapshot.product_id)
|
|
.subquery()
|
|
)
|
|
|
|
rows = db.execute(
|
|
select(PriceSnapshot, Product)
|
|
.join(
|
|
min_per_product,
|
|
(PriceSnapshot.product_id == min_per_product.c.product_id)
|
|
& (PriceSnapshot.price == min_per_product.c.min_price),
|
|
)
|
|
.join(Product, PriceSnapshot.product_id == Product.id)
|
|
.options(selectinload(Product.store))
|
|
.order_by(PriceSnapshot.price.asc())
|
|
.limit(limit)
|
|
).all()
|
|
|
|
return [
|
|
CheapestProduct(
|
|
product=ProductSchema.model_validate(product),
|
|
price=snapshot.price,
|
|
unit_price=snapshot.unit_price,
|
|
unit_description=snapshot.unit_description,
|
|
is_on_sale=snapshot.is_on_sale,
|
|
timestamp=snapshot.timestamp,
|
|
)
|
|
for snapshot, product in rows
|
|
]
|