What about paging?

You are correct. I haven't figured out how to do paging in a database driver agnostic way.

Mostly I've been working around this by designing appropriate drill down structures for reports and limiting database queries with limit when I need to. I realize it's not an optimal solution, but that's where we are. My users really don't like paging anyway ;), and it also really messes with export functionality. (You don't ever want to export to csv just one page of a table).

Javascript paging is now supported via datatables juery plugin.

Two questions related to paging and the discussion above...

1) I looked at datatables jquery plugin. It appears that all results are loaded and the display of the results is paged. As opposed to, limiting the number of results returned and then fetch more when the next page is requested. My query typically returns over 150,000 records if all results are fetched. Trying to use an unlimited query in Forena as the data block fails to display with the Preview. I get a white page with nothing on it. I'm assuming Forena is timing out. Is my interpretation of datatables correct?

2) I tried janeks suggestion of using parameters to add LIMIT to my SQL.

The following query works with hardcoded LIMIT to show only the first 10 records...

-- ACCESS=access content
SELECT 
    e.id, e.event_type
  FROM project_event e
  INNER JOIN dataset_project_events dpe
  ON dpe.project_event_id=e.event_id AND dpe.dataset_id=:dataset
  ORDER BY e.event_type
  LIMIT 0, 10;

The next query DOES NOT work: The same query as above with LIMIT using parameters.

-- ACCESS=access content
SELECT 
    e.id, e.event_type
  FROM project_event e
  INNER JOIN dataset_project_events dpe
  ON dpe.project_event_id=e.event_id AND dpe.dataset_id=:dataset
  ORDER BY e.event_type
  LIMIT :start, :row_count;

When I set parameters start to 0 and row_count to 10, the SQL produced is...

-- ACCESS=access content
SELECT 
    e.id, e.event_type
  FROM project_event e
  INNER JOIN dataset_project_events dpe
  ON dpe.project_event_id=e.event_id AND dpe.dataset_id=:dataset
  ORDER BY e.event_type
  LIMIT '0', '10';

Fails with "Error in your SQL" near LIMIT '0', '10'. SQL fails due to the quotes around the 0 and 10 in the LIMIT part of the statement.

Is there a way to make parameters substitute as integers? I wasn't able to find any documentation describing this.

BTW, your video tutorials ROCK!!!

I don't have a way yet to force types on SQL parameters. That opens a pretty big can of worms for me.... lots of database dependencies, etc. If I could think of a predictable and stable way to enforce types I would certainly be willing to try. If we try that lets discuss in on another issue, cause that's really bigger than the limit queue.

IMHO, if your querying 150,000 records, its time to redesign the UI. The idea that you'd have a user scrolling through that many pages of data means that the user isn't really looking at the data anyway. Rather it would make more sense to limit the number of records selected based on summary data, and have the user browse this based on other data blocks that provide links to the report.

For date based stuff, I typically provide filters based on years or years/ months to limit the queries, or start dates and end dates. Then you can build a block that shows the list of possible years along the bottom, kind of like a pager, but make the report display data filtered on Years or years and months. It largely depends on the data domain.

Anyway, in the hundreds of reports that I've developed with Forena this approach has never failed me, the users have always been happy with a data domain based paging system rather than the arbitrary break up a huge data set by rownumbers, because it helps them find the rows they are actually looking for. You could try putting a LIMIT 1000 or limit 5000 or something like that on your queries and then tell the user that only the first 5000 records have been displayed as a failsafe.

I'm reading more about datatables to see if I can figure this out. I saw a page in the datatables doc dealing with server side control of paging. Not sure yet how this might fit with Forena.

Thanks for your observations about large number of records. We have been discussing ways to filter in a meaningful way, but even those results will continue to be very large. We are looking at navigating through from the list of datasets (100s of records) to a summary of detail records from which the user can select a smaller subset of details to view (hopefully 1000s of records or less).

I saw in one of the sample queries (promoted_content.sql) for Drupal Administration, where SQL functions are used to change a parameter to an integer. You use...
LIMIT COALESCE(CAST(:limit AS integer), 10)

Unfortunately, I could not get COALESCE or CAST or CONVERT functions to work in MySQL even though CAST and CONVERT are documented in MySQL dev reference manual.

I tried testing CAST and CONVERT in phpMyAdmin with a very simple query to try and get them to work. Both failed as syntax errors. I also tried creating a report with your promoted_content.sql as the data block, but it fails too. I thought I was onto something for a brief glimmer in time.

I have just pushed a commit that makes it possible to use integer typed limit statements. It is documented in the Creating Custom Report blocks report. After updating to the latest code, you can Revert all reports to the latest revision, and check out the documentation for more information. I'll be adding some videos on setting data types for data blocks hopefully soon. This should be in the next (7.x-3.9) release, or you can try the dev version sometime tomorrow.

just found out that the -- in the sql file is quite formatting sensitive.

example from the help documentation:

  --ACCESS=access content
  SELECT nid FROM node 
    WHERE promote=1
      AND status=1
    ORDER BY sticky DESC, created 
  --IF=:limit
  LIMIT :limit 
  --ELSE 
  LIMIT 10
  --END
  --INFO
  type[limit]=int

make sure there is no leading space in front of the --