AXV

Filtrlash, qidiruv va tartiblash

query parametrlar, Query, where, ilike, or_, order_by, limit, offset, sahifalash

10-sinfAdvanced Python Back-end5-hafta · 15-dars

Bugun nimalarni o'rganamiz?

Filtrlash

Query va where

Qidiruv va tartib

ilike, or_, order_by

Pagination

limit, offset, total

Takrorlash

Qisman yangilash uchun qaysi metod ishlatiladi?

PATCH faqat yuborilgan maydonlarni o'zgartiradi.
01

Filtrlash

Query va where

Filtr qurish

Query parametr Bo'sh emasmi? where(...)qo'shish Natija Faqat berilgan parametrlar filtrga aylanadi

Dinamik filtr


@router.get("/", response_model=list[BookRead])
async def list_books(
    author: str | None = None,
    min_pages: int | None = Query(None, ge=1),
    published_year: int | None = None,
    db: AsyncSession = Depends(get_db),
):
    stmt = select(Book).where(Book.is_published.is_(True))
    if author:
        stmt = stmt.where(Book.author == author)
    if min_pages:
        stmt = stmt.where(Book.pages >= min_pages)
    if published_year:
        stmt = stmt.where(Book.published_year == published_year)
    return (await db.scalars(stmt)).all()
GET /books?min_pages=100&published_year=2020 ikkala shartni birga (AND) qo'llaydi. min_pages=0 bo'lsa if min_pages: shart o'tmaydi: aniq tekshiruv uchun is not None ishlating.

Mini-test

Ketma-ket yozilgan ikkita where(...) qanday birikadi?

SQLAlchemy ularni AND bilan birlashtiradi.
02

Qidiruv va tartib

ilike, or_, order_by

Qidiruv zanjiri

q=python ilike %python% or_title/author order_by Qidiruv shartlari OR, filtrlar AND

Qidiruv va sort


from sqlalchemy import or_

ALLOWED_SORT = {"title": Book.title, "created_at": Book.created_at, "pages": Book.pages}

def apply_search(stmt, q: str | None):
    if q:
        like = f"%{q}%"
        stmt = stmt.where(or_(Book.title.ilike(like), Book.author.ilike(like)))
    return stmt

def apply_sort(stmt, sort: str):
    desc = sort.startswith("-")
    column = ALLOWED_SORT.get(sort.lstrip("-"))
    if column is None:
        raise HTTPException(400, "Noto'g'ri sort maydoni")
    return stmt.order_by(column.desc() if desc else column.asc())
Qidiruvda % va _ belgilari LIKE da maxsus. Katta jadvalda boshida % bo'lgan ilike indeksdan foydalanmaydi (keyingi bosqich: pg_trgm).

Mini-test

sort=-pages nimani bildiradi?

Boshidagi - kamayish tartibini bildiradi.
03

Pagination

limit, offset, total

Sahifalash

Filtr +qidiruv count(*)= total order_by limit + offset Avval filtr, keyin count, oxirida limit

Page javobi


class BookPage(BaseModel):
    items: list[BookRead]
    total: int
    limit: int
    offset: int

@router.get("/", response_model=BookPage)
async def list_books(q: str | None = None, sort: str = "-created_at",
                     limit: int = Query(10, ge=1, le=100),
                     offset: int = Query(0, ge=0),
                     db: AsyncSession = Depends(get_db)):
    stmt = apply_search(select(Book).where(Book.is_published.is_(True)), q)
    total = await db.scalar(select(func.count()).select_from(stmt.subquery()))
    stmt = apply_sort(stmt, sort).limit(limit).offset(offset)
    items = (await db.scalars(stmt)).all()
    return BookPage(items=items, total=total, limit=limit, offset=offset)
limit ga yuqori chegara (masalan, 100) qo'yish shart: aks holda limit=1000000 bilan serverni yuklash mumkin. GET /books?q=python&sort=title&limit=5&offset=10.

Mini-test

limit=10, offset=20 qaysi yozuvlarni qaytaradi?

20 tasi o'tkazib yuboriladi, keyingi 10 tasi qaytadi.

Filtr va qidiruv

Filtrlash

Aniq shart: muallif, yil, sahifalar soni; AND bilan birikadi
VS

Qidiruv

Matn bo'yicha qisman moslik: ilike, bir nechta maydon, or_

Aniq shartlar uchun filtr, matn uchun qidiruv.

Atamalar. Bosing

Query parametr

bosing

URL dagi ?kalit=qiymat qismi

ilike

bosing

Katta-kichik harfni farqlamaydigan qidiruv

limit / offset

bosing

Sahifa hajmi va o'tkazib yuboriladigan yozuvlar

total

bosing

Filtrga mos jami yozuvlar soni

Ro'yxat endpointini qurish

1. Filtr

where bilan shartlarni qo'shing.

2. Qidiruv va tartib

ilike, or_, ruxsat etilgan sort.

3. Sahifa

count, limit, offset va BookPage.

Query parametrlar misoli

URLMa'nosi
?author=Navoiymuallif bo'yicha filtr
?q=pythonsarlavha yoki muallifda qidiruv
?sort=-pagessahifalar bo'yicha kamayish
?limit=5&offset=103-sahifa, 5 tadan
?min_pages=100kamida 100 bet

Pastga bosing: Swagger da sinash

Swagger da sinash

  1. Bir nechta kitob yarating (14-dars).
  2. GET /books da q va sort ni to'ldiring.
  3. limit=2&offset=2 bilan sahifalashni tekshiring.

Tartiblash


stmt = select(Book)

Tartiblash


stmt = select(Book).order_by(Book.created_at.desc())

Pagination raqamlarda

0
limit yuqori chegarasi
0
standart limit
0
standart offset

Standart qiymatlar va chegara serverni himoya qiladi.

Amaliy topshiriq 20 daqiqa

OsonFiltr: published_year bo'yicha filtrni list_books ga qo'shing.
O'rtaQidiruv: q parametri sarlavha va muallifda registrni farqlamay qidirsin.
QiyinSort va sahifa: sort (ruxsat etilgan maydonlar) va limit/offset ni yozing, total qaytaring.

Dars xulosasi

Filtr

where + Query; AND bilan birikadi.

Qidiruv va tartib

ilike, or_, order_by va ruxsat etilgan maydonlar.

Pagination

limit, offset, total.

Uyga vazifa: filtr, qidiruv, tartib va pagination bilan GET /books.

Keyingi dars: Fayllar bilan ishlash

UploadFile, media, yuklash va yuklab olish

6-hafta · 16-dars