9d45201bf1
* feat: Add API references * docs: Add links to API reference, add new pages * fix: Remove src/index.ts * fix: Update typedoc configs
2.5 KiB
| description |
|---|
| Learn how to use custom SQL queries in Kottster to fetch data for your tables, including pagination and count queries. |
Raw SQL queries
For table pages in Kottster, you can define custom SQL queries to fetch data directly from your database. This is useful for complex queries or when you want to optimize performance by fetching only the necessary data.
To set up custom SQL queries, create a page using the visual builder. Choose the "Raw SQL Query" as a fetch strategy.
Basic SQL queries
Here are examples of simple SQL queries for different database systems:
::: code-group
SELECT id, name, email, created_at
FROM users
WHERE status = 'active'
ORDER BY created_at DESC
SELECT id, name, email, created_at
FROM users
WHERE status = 'active'
ORDER BY created_at DESC
SELECT id, name, email, created_at
FROM users
WHERE status = 'active'
ORDER BY created_at DESC
SELECT id, name, email, created_at
FROM users
WHERE status = 'active'
ORDER BY created_at DESC
:::
Pagination support
If pagination is being used, your query needs to be modified to use :limit and :offset parameters:
::: code-group
SELECT id, name, email, created_at
FROM users
WHERE status = 'active'
ORDER BY created_at DESC
LIMIT :limit OFFSET :offset
SELECT id, name, email, created_at
FROM users
WHERE status = 'active'
ORDER BY created_at DESC
LIMIT :limit OFFSET :offset
SELECT id, name, email, created_at
FROM users
WHERE status = 'active'
ORDER BY created_at DESC
LIMIT :limit OFFSET :offset
SELECT id, name, email, created_at
FROM users
WHERE status = 'active'
ORDER BY created_at DESC
OFFSET :offset ROWS
FETCH NEXT :limit ROWS ONLY
:::
Count queries for pagination
When using pagination, you also need to provide a count query to calculate the total number of records. This is required for proper pagination controls:
::: code-group
SELECT COUNT(*)
FROM users
WHERE status = 'active'
SELECT COUNT(*)
FROM users
WHERE status = 'active'
SELECT COUNT(*)
FROM users
WHERE status = 'active'
SELECT COUNT(*)
FROM users
WHERE status = 'active'
:::
Column configuration
When using raw SQL queries, Kottster automatically detects the columns based on the properties of the records returned.