Database
Search
Add case-insensitive search to one paginated plugin table with withPagination and the shared ContentDataTable search input.
withPagination can filter a list by a search term. List the text columns in its search option, and a ?search= query parameter keeps only rows where any of those columns contains the term, ignoring case. In this guide the paged notes route of the site-notes plugin gets a search parameter, and an AdminCP-style table gets a search box.
This page filters one table. For search across content types, with an index and ranking, see Search.
Before you begin
- The paged notes route
GET /api/site-notes/notes/pagedfrom Pagination.
Accept search in the route query
zodPaginationQuery has no search key, so extend it in the route's request:
request: {
query: zodPaginationQuery.extend({
search: z.string().max(100).optional(),
}),
},List the searchable columns
Pass the columns in search. Keep everything else from the pagination route, including offset:
const data = await withPagination({
c,
params: { query: c.req.valid('query') },
table: site_notes,
primaryCursor: site_notes.id,
orderBy: { column: site_notes.createdAt, order: 'desc' },
search: [site_notes.title, site_notes.body],
query: async ({ cursorSelection, limit, offset, where, orderBy }) =>
await c
.get('db')
.select({
...cursorSelection,
id: site_notes.id,
title: site_notes.title,
body: site_notes.body,
})
.from(site_notes)
.where(where)
.orderBy(orderBy)
.limit(limit)
.offset(offset),
})The search condition arrives in where, so pass it to Drizzle unchanged. totalCount counts only the matching rows.
Show the search input in a table
ContentDataTable renders rows, a toolbar, sorting and the pager, but never fetches. With search set it shows a search box that writes ?search= to the URL; the page's loader then fetches the matching rows. The table reads and writes the URL through a DataTableNavigationProvider:
import type { ColumnDef } from '@vitnode/core/components/table/data-table-content'
import type { DataTableNavigation } from '@vitnode/core/components/table/navigation'
import { ContentDataTable } from '@vitnode/core/components/table/content'
import { DataTableNavigationProvider } from '@vitnode/core/components/table/navigation'
import { useMemo } from 'react'
type Note = BrowseData['edges'][number]
const columns: ColumnDef<Note>[] = [
{ accessorKey: 'title', header: 'Title' },
{ accessorKey: 'body', header: 'Body' },
]
const BrowsePage = ({
loaderData,
navigate,
search,
}: PluginRoutePageProps<BrowseData, BrowseSearch>) => {
const navigation = useMemo<DataTableNavigation>(
() => ({
searchParams: new URLSearchParams(search),
navigate: async nextSearch => {
await navigate({
resetScroll: false,
search: Object.fromEntries(new URLSearchParams(nextSearch)),
})
},
}),
[navigate, search],
)
return (
<DataTableNavigationProvider value={navigation}>
<ContentDataTable
id="site-notes-table"
columns={columns}
edges={loaderData.edges}
pageInfo={loaderData.pageInfo}
order={{ defaultOrder: { column: 'id', order: 'desc' } }}
search
searchPlaceholder="Search notes..."
/>
</DataTableNavigationProvider>
)
}BrowseData, BrowseSearch and the loader come from the page in Pagination. Make parseSearch keep search as well as cursor, and pass both to the route: args: { query: search }.
Check the result
Open /site-notes/browse and type at least three letters of a note's title into the search box. Half a second after you stop typing, the URL gets ?search=... and the table shows only the matching notes. Clear the box and every note comes back.