SeaTable API: Get Rows returns just 10.000 rows?

Developer Talk API
1

Hi all,

I just installed 3.1.13 enterprise of SeaTable - great so far!

However I noticed that the API call for “Get Rows” now just returns 10.000 rows instead of 50.000 as it is mentioned in the API documentation. Is this by purpose? Not sure whether this is new to 3.1.13…

Thanks,

Simon

2

Hey sikreutz,

I have to check the current limitations, but it sounds correct that is the current limits are max. 10,000 rows instead of 50,000.

BUT there is a reason and an easy fix

The reason for this change is that the Get Rows API-request needs many resources in terms of CPU and RAM of the SeaTable Server. Therefore we have to limit this request.

My recommendation is that you switch from Get Rows to the sql-query API-call. This API is much more efficient and you have a lot more possibilities to use SeaTable like an SQL-database.

Also the SQL-Query API-call supports many features like Order by , Limit and calculations. You get more details here:
https://seatable.github.io/seatable-scripts/python/sql/

do you want some examples?

In some days I will publish a tutorial how I used SeaTable to build a custom feedback/idea solution like nolt.io or canny.io. I can already spoiler that I use intensively this sql-endpoint like this. Here are just some examples:

$sql = "select * from Requests WHERE _id = '". $args['rID'] ."' LIMIT 1";
$sql = "select * from Comments WHERE `Feature Request` = '". $items->results[0]->Title ."' ORDER BY `Creation` ". ( $_SESSION['comment_sort'] ?? "DESC" );
$sql = "select * from Requests WHERE Title ILIKE '%". $searchTerm ."%' OR Description ILIKE '%". $searchTerm ."%'";

Best regards
Christoph

3

Thanks for the quick response.

The main reason for me to use “Get Rows” instead of the sql-query call is that using “Get Rows” I can point to certain views and thereby use the filters etc. that are set in a specific view. Is there also a way to use Views in the sql call?

BTW: There is a bug in using Views in “Get Rows”: Filters are applied, but hiding columns is ignored. You have to remove them manually after the call.

Best,

Simon

4

Hey sikreutz,

“views” can not be used in the sql-query. The sql-query always expects a table. So you are right.

Still if you work with many rows (>10.000) you should use the sql-query to reduce the workload of your server.
Or you stick with the get_rows and execute this call multiple times.

Christoph

5

The API returns at maximum of 10.000 rows at once. But you can call the API multiple times using different start and limit parameters to get all rows.

6

Hi Daniel, hi Christoph,

the 10.000 row limit does also seem to apply to sql queries - even when I include “LIMT 20000” for example. :frowning: At least when I tested it…

So I have to do multiple requests in any way. Or is there a way to raise that limit in conf files?

Thanks a lot!

Simon

7

There is no such a configuration. From our own experience with database (MySQL or SeaTable), querying too much data at once from database is a bad way, often leading to performance issues or memory usage issues.