Cross-Table Search and Pagination: A FastAPI + PostgreSQL Query Refactor

Search in a business list is most likely to look correct when there is little data. Filter the array fetched for the current page, type an identifier, and a result appears. Once the records span multiple pages, searching for a filename on another page produces “No results.”

In one inquiry-list refactor, search needed to cover the main record’s identifier as well as part numbers and filenames in related uploads. The real change belonged in the database query’s scope: search the target record set first, then return one page of matches.

This article explains the approach using simplified tables and synthetic data. The code retains SQLAlchemy expressions but omits the FastAPI route, authorization, and response models. It cannot be deployed directly as a complete endpoint.

Define what the search returns

Suppose the system has two tables:

Table	Main fields	Relationship
inquiry	id, bill_no, created_at	One inquiry record
upload	id, inquiry_id, pn, file_names	An inquiry can have multiple upload records

The user is looking for an inquiry; filenames and part numbers are clues for finding it. The endpoint should still return main records, and two matching attachments should not make the same inquiry appear twice.

A useful test dataset has 25 main records, with 10 per page, and an older record associated with demo_100%_rev_a.zip. Give that same record two attachments containing sensor. This checks matches beyond the current page, literal wildcards, and duplicate matches together, rather than validating the feature with three examples already on the current page.

Express the existence of a related record with EXISTS

A direct JOIN can filter across tables, but a one-to-many relationship expands result rows. If a main record has three matching attachments, it may appear three times in the SQL result. Deduplicating in application code after LIMIT can break both page sizes and totals.

This scenario only needs to answer “Does a matching upload exist?”, so a correlated EXISTS fits the requirement more closely. Here is a simplified query:

from sqlalchemy import exists, func, or_, select

def filtered_inquiries(term: str):
    stmt = select(Inquiry)
    term = term.strip()
    if not term:
        return stmt

    upload_matches = exists(
        select(Upload.id).where(
            Upload.inquiry_id == Inquiry.id,
            or_(
                Upload.pn.icontains(term, autoescape=True),
                func.array_to_string(Upload.file_names, "\n")
                    .icontains(term, autoescape=True),
            ),
        )
    )
    return stmt.where(or_(
        Inquiry.bill_no.icontains(term, autoescape=True),
        upload_matches,
    ))

Inquiry and Upload are illustrative ORM models, and file_names is assumed to be a PostgreSQL text array. Converting the array into searchable text explains the present requirement; a separate filename table is another option if filenames need independent permissions, more attributes, or dedicated indexes.

autoescape=True treats % and _ in user input as literal characters. It has a different job from bound parameters: binding handles the transmission of SQL values, while LIKE escaping handles wildcard semantics. Avoiding string concatenation does not remove the need to define what a user searching for 100% expects to match. SQLAlchemy’s string containment and escaping documentation explains the API’s behavior.

Define identifier rules separately

Some interfaces display identifiers such as Q-002455, while the underlying primary key is an integer. Decide whether a prefixed identifier, one without a prefix, and one with leading zeros should all match.

If identifier recognition is supported, convert only an explicit format within a reasonable range, then add the primary-key condition to the search. Do not interpret every filename containing digits as a primary key, or let an excessively long number trigger an unbounded type conversion.

The real endpoint must first restrict main records to those the current user can access, then add search conditions within that same permission scope. EXISTS prevents row expansion; it does not provide access control automatically.

Filter, sort, then paginate

Add ordering and pagination after constructing the filtered query:

filtered = filtered_inquiries(search)
page_stmt = (
    filtered
    .order_by(Inquiry.created_at.desc(), Inquiry.id.desc())
    .offset((page - 1) * page_size)
    .limit(page_size)
)
count_stmt = select(func.count()).select_from(filtered.subquery())

The API layer should require positive page and page_size values and cap page size. The count and list queries must reuse the same filters and permissions to avoid inconsistencies such as reporting matches while showing none on the page.

Sorting only by creation time is insufficient. Bulk imports may give several records the same timestamp, so add the unique id as a secondary sort key. The PostgreSQL LIMIT / OFFSET documentation likewise requires ordering that determines result order when paginating.

This ordering resolves nondeterministic order within the same dataset. If new records keep arriving while the user pages through it, OFFSET positions still shift, potentially repeating or skipping a record. Stronger continuity may call for cursor pagination based on time and ID. Snapshot consistency over a query session also involves transaction isolation; a unique sort order does not solve everything.

Use counterexamples to verify search scope

I first place the older record outside the default first page, then search for its filename. Next, I make multiple attachments match and verify that the list still contains one main record and a total of one. I try %, _, Chinese text, surrounding whitespace, and an empty string, checking that results follow the agreed rules. Finally, I verify identifier recognition and inaccessible records.

Performance requires separate measurement. Contains matching, array-to-text conversion, and cross-table conditions can become costly as data grows. Inspect the real data distribution and query plan before deciding on trigram indexes, a normalized filename table, or another search approach. EXISTS makes the semantics clearer here, but this article has no controlled benchmark and cannot claim a speedup factor.

This refactor made me pay closer attention to an endpoint’s set semantics: search scope, returned objects, deduplication, and pagination order must be designed together. A search box on the current page is backed by a complete query contract.