Приступим к реализации функции product_by_category, отвечающую за вывод товаров из определенной категории.

@router.get('/{category_slug}')
async def product_by_category(db: Annotated[Session, Depends(get_db)], category_slug: str):
    category = db.scalar(select(Category).where(Category.slug == category_slug))
    if category is None:
        raise HTTPException(
            status_code=status.HTTP_404_NOT_FOUND,
            detail='Category not found'
        )
    subcategories = db.scalars(select(Category).where(Category.parent_id == category.id)).all()
    categories_and_subcategories = [category.id] + [i.id for i in subcategories]
    products_category = db.scalars(
        select(Product).where(Product.category_id.in_(categories_and_subcategories), Product.is_active == True,
                              Product.stock > 0)).all()
    return products_category

Разберем логику работы. Первым делом нам нужно проверить наличие данной категории, в случае отсутствия вызвать соответствующую ошибку.

    category = db.scalar(select(Category).where(Category.slug == category_slug))
    if category is None:
        raise HTTPException(
            status_code=status.HTTP_404_NOT_FOUND,
            detail='Category not found'
        )

Так как таблица Category является самоссылающаяся, то есть внутри нее есть ключ, который ссылается на эту таблицу, нам нужно получить ID всех ее подкатегорий.

subcategories = db.scalars(select(Category).where(Category.parent_id == category.id)).all()

В результате выполнения мы получаем список с ID подкатегорий, например [2, 3, 4]. Далее нам необходимо сделать так, чтобы в одном списке была наша категория и ее подкатегории:

categories_and_subcategories = [category.id] + [i.id for i in subcategories]

В случае, если мы получаем в переменной category_slug категорию или подкатегорию, у которой не содержится еще подкатегорий. То в данном списке будет только ID категории с переменной category_slug.

И в заключении мы выполняем запрос, где фильтруем наш товар через метод IN , а также получаем только активные товары с остатком более 0.

    products_category = db.scalars(
        select(Product).where(Product.category_id.in_(categories_and_subcategories), Product.is_active == True,
                              Product.stock > 0)).all()

Метод IN работает следующим образом:

stmt = select(User.id).where(User.id.in_([1, 2, 3]))
>>> result = db.execute(stmt)

Подробнее о методе IN вы можете посмотреть в документации. Запустим сервер и проверим работу. На данном этапе у меня в бд хранятся следующие категории и товары:

Я добавлю еще одну подкатегорию IPhone, которая будет подкатегорией у Apple и несколько товаров в нее:

Проверим эту логику работы:

Мы видим что логика данной конечной точки у нас прекрасно работает, в следующем шаге продолжим разработку проекта.