SQL Plugin : 2 arrays response

[Fix]

Hello everyone,

I am using the SQL Plugin of Weweb.

When I run my query, I get two tables in response

One containing all the records and the other the metadata. Is this normal? If so, how can I get only my records?

Thank you in advance for your help.

[Quentin]

In any binding, you could select only the array having the actual records

[Fix]

Thank you @Quentin

But if I want to do pagination I can’t…So how can I do ? (I know is better to pagination on the backend )

[Quentin]

You’ll need to do it using SQL variables to offset data, and then send back these variables from WeWeb in the SQL query.

Here’s actually a great response from ChatGPT

Paginating a SQL call involves dividing the results of a query into discrete “pages” and retrieving only a specific subset of these results at a time. This is particularly useful for improving performance and user experience when dealing with large datasets. The approach can vary slightly depending on the SQL database you are using (like MySQL, PostgreSQL, SQL Server, etc.). Here’s a general guide on how to implement pagination in SQL:

1. Using OFFSET and LIMIT (Common in MySQL, PostgreSQL)

This is the most common method used for pagination in SQL databases like MySQL and PostgreSQL.

SELECT * FROM your_table
ORDER BY column
LIMIT [number of rows to return] OFFSET [start row];

• LIMIT specifies the number of rows to return.
• OFFSET specifies the number of rows to skip before starting to return rows.

For example, to get the third page of results with 10 results per page:

SELECT * FROM your_table
ORDER BY column
LIMIT 10 OFFSET 20;

2. Using ROW_NUMBER() and a Subquery (Common in SQL Server)

In SQL Server, you often use ROW_NUMBER() along with a subquery.

WITH OrderedTable AS (
    SELECT *, ROW_NUMBER() OVER (ORDER BY column) AS 'RowNumber'
    FROM your_table
)
SELECT * FROM OrderedTable
WHERE RowNumber BETWEEN [start row] AND [end row];

3. Using FETCH and OFFSET (SQL:2011 standard, SQL Server 2012 onwards, PostgreSQL 9.5 onwards)

This is a newer standard and is supported by SQL Server 2012 onwards and PostgreSQL 9.5 onwards.

SELECT * FROM your_table
ORDER BY column
OFFSET [start row] ROWS 
FETCH NEXT [number of rows to return] ROWS ONLY;

Key Considerations:

• Performance: For large datasets, especially, consider the performance implications of your pagination strategy. OFFSET can be slow on very large tables because it still scans the rows to be skipped.
• Consistency: If the underlying data can change (inserts, updates, deletes), the same offset can lead to skipping rows or viewing rows twice. Sometimes it’s better to paginate based on a unique key.
• Indexing: Ensure that the column you’re ordering by is indexed, as this can greatly improve the performance of your pagination queries.

Remember to replace your_table and column with your actual table and column names, and adjust the limit and offset values according to your needs.