SQLAlchemy 2.0 select with filters and pagination

<p>I need to implement a paginated list endpoint in FastAPI. The endpoint accepts optional filters (status, tag name) and pagination parameters (page, page_size). I want to avoid N+1 queries and get total count efficiently.</p>

<p>Use a single query with COUNT in a subquery for efficient pagination:</p> <div class="highlight"><pre><span></span><code><span class="kn">from</span><span class="w"> </span><span class="nn">sqlalchemy</span><span class="w"> </span><span class="kn">import</span> <span class="n">select</span><span class="p">,</span> <span class="n">func</span> <span class="kn">from</span><span class="w"> </span><span class="nn">sqlalchemy.orm</span><span class="w"> </span><span class="kn">import</span> <span class="n">selectinload</span> <span class="k">async</span> <span class="k">def</span><span class="w"> </span><span class="nf">list_traces</span><span class="p">(</span> <span class="n">session</span><span class="p">:</span> <span class="n">AsyncSession</span><span class="p">,</span> <span class="n">status</span><span class="p">:</span> <span class="nb">str</span> <span class="o">|</span> <span class="kc">None</span> <span class="o">=</span> <span class="kc">None</span><span class="p">,</span> <span class="n">tag</span><span class="p">:</span> <span class="nb">str</span> <span class="o">|</span> <span class="kc">None</span> <span class="o">=</span> <span class="kc">None</span><span class="p">,</span> <span class="n">page</span><span class="p">:</span> <span class="nb">int</span> <span class="o">=</span> <span class="mi">1</span><span class="p">,</span> <span class="n">page_size</span><span class="p">:</span> <span class="nb">int</span> <span class="o">=</span> <span class="mi">20</span><span class="p">,</span> <span class="p">)</span> <span class="o">-&gt;</span> <span class="nb">tuple</span><span class="p">[</span><span class="nb">list</span><span class="p">[</span><span class="n">Trace</span><span class="p">],</span> <span class="nb">int</span><span class="p">]:</span> <span class="c1"># Base query</span> <span class="n">base_q</span> <span class="o">=</span> <span class="n">select</span><span class="p">(</span><span class="n">Trace</span><span class="p">)</span><span class="o">.</span><span class="n">options</span><span class="p">(</span><span class="n">selectinload</span><span class="p">(</span><span class="n">Trace</span><span class="o">.</span><span class="n">tags</span><span class="p">))</span> <span class="k">if</span> <span class="n">status</span><span class="p">:</span> <span class="n">base_q</span> <span class="o">=</span> <span class="n">base_q</span><span class="o">.</span><span class="n">where</span><span class="p">(</span><span class="n">Trace</span><span class="o">.</span><span class="n">status</span> <span class="o">==</span> <span class="n">status</span><span class="p">)</span> <span class="k">if</span> <span class="n">tag</span><span class="p">:</span> <span class="n">base_q</span> <span class="o">=</span> <span class="n">base_q</span><span class="o">.</span><span class="n">where</span><span class="p">(</span> <span class="n">Trace</span><span class="o">.</span><span class="n">tags</span><span class="o">.</span><span class="n">any</span><span class="p">(</span><span class="n">Tag</span><span class="o">.</span><span class="n">name</span> <span class="o">==</span> <span class="n">normalize_tag</span><span class="p">(</span><span class="n">tag</span><span class="p">))</span> <span class="p">)</span> <span class="c1"># Count query</span> <span class="n">count_q</span> <span class="o">=</span> <span class="n">select</span><span class="p">(</span><span class="n">func</span><span class="o">.</span><span class="n">count</span><span class="p">())</span><span class="o">.</span><span class="n">select_from</span><span class="p">(</span><span class="n">base_q</span><span class="o">.</span><span class="n">subquery</span><span class="p">())</span> <span class="n">total</span> <span class="o">=</span> <span class="p">(</span><span class="k">await</span> <span class="n">session</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="n">count_q</span><span class="p">))</span><span class="o">.</span><span class="n">scalar_one</span><span class="p">()</span> <span class="c1"># Paginated results</span> <span class="n">offset</span> <span class="o">=</span> <span class="p">(</span><span class="n">page</span> <span class="o">-</span> <span class="mi">1</span><span class="p">)</span> <span class="o">*</span> <span class="n">page_size</span> <span class="n">result</span> <span class="o">=</span> <span class="k">await</span> <span class="n">session</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span> <span class="n">base_q</span><span class="o">.</span><span class="n">order_by</span><span class="p">(</span><span class="n">Trace</span><span class="o">.</span><span class="n">created_at</span><span class="o">.</span><span class="n">desc</span><span class="p">())</span> <span class="o">.</span><span class="n">offset</span><span class="p">(</span><span class="n">offset</span><span class="p">)</span> <span class="o">.</span><span class="n">limit</span><span class="p">(</span><span class="n">page_size</span><span class="p">)</span> <span class="p">)</span> <span class="n">traces</span> <span class="o">=</span> <span class="n">result</span><span class="o">.</span><span class="n">scalars</span><span class="p">()</span><span class="o">.</span><span class="n">unique</span><span class="p">()</span><span class="o">.</span><span class="n">all</span><span class="p">()</span> <span class="k">return</span> <span class="n">traces</span><span class="p">,</span> <span class="n">total</span> </code></pre></div> <p>Route:</p> <div class="highlight"><pre><span></span><code><span class="nd">@router</span><span class="o">.</span><span class="n">get</span><span class="p">(</span><span class="s1">'/traces'</span><span class="p">)</span> <span class="k">async</span> <span class="k">def</span><span class="w"> </span><span class="nf">list_traces_route</span><span class="p">(</span><span class="n">page</span><span class="p">:</span> <span class="nb">int</span> <span class="o">=</span> <span class="mi">1</span><span class="p">,</span> <span class="n">page_size</span><span class="p">:</span> <span class="nb">int</span> <span class="o">=</span> <span class="n">Query</span><span class="p">(</span><span class="mi">20</span><span class="p">,</span> <span class="n">le</span><span class="o">=</span><span class="mi">100</span><span class="p">)):</span> <span class="n">traces</span><span class="p">,</span> <span class="n">total</span> <span class="o">=</span> <span class="k">await</span> <span class="n">list_traces</span><span class="p">(</span><span class="n">session</span><span class="p">,</span> <span class="n">page</span><span class="o">=</span><span class="n">page</span><span class="p">,</span> <span class="n">page_size</span><span class="o">=</span><span class="n">page_size</span><span class="p">)</span> <span class="k">return</span> <span class="p">{</span><span class="s1">'items'</span><span class="p">:</span> <span class="n">traces</span><span class="p">,</span> <span class="s1">'total'</span><span class="p">:</span> <span class="n">total</span><span class="p">,</span> <span class="s1">'page'</span><span class="p">:</span> <span class="n">page</span><span class="p">,</span> <span class="s1">'pages'</span><span class="p">:</span> <span class="n">math</span><span class="o">.</span><span class="n">ceil</span><span class="p">(</span><span class="n">total</span> <span class="o">/</span> <span class="n">page_size</span><span class="p">)}</span> </code></pre></div> <p>Key points: - <code>.scalars().unique().all()</code> deduplicates rows from JOIN-based selectinload - <code>Query(20, le=100)</code> caps page size at 100 to prevent abuse - Build filter conditions dynamically by chaining <code>.where()</code> calls - Use <code>OFFSET/LIMIT</code> for simple cases; keyset pagination for &gt;10k rows</p>