Apply LIMIT to a query

[arnor@icetips.com]

Hi all,

Is it possible to apply mySQL LIMIT to a list? We have a user query situation which might return too many rows to be practical so to limit traffic we'd like to set a limit to say 500 or 1000 rows. I've figured out with help from the forum, how I can construct the WHERE clause, but I can't see a way to plug a LIMIT clause in there. It needs to go AFTER the ORDER BY clause if there is one, so I don't think I can just tack it onto the WHERE clause. Is there a way to handle this via the PHP plugin?

Or is there a way in Fabrik to set the max number of rows?

Best regards,

 

[juuser]

Hi,

Based on what do you need to restrict the loaded rows? If there is something like "record date" field in your table, you can use list prefilter to load only rows from last x days.

 

[arnor@icetips.com]

Hi juuser,

Already using prefilters all over. I need to simply set a hard coded maximum for this particular list. I just need to implement a LIMIT clause. I'm already constructing the WHERE clause using PHP, but the table for this particular list can have up to say a hundred thousand rows and even with filtering on multiple columns it IS possible that users may trigger a selection of data set that is way too big to be of any practical use.

I basically want to be able to set a maximum number of rows returned. I could do a count when I construct the WHERE clause and if there are too many rows, I can set the WHERE to 1=2 to fail it and throw an error. That may be sufficient, but it would be nice to know if and then how the LIMIT clause can be implemented in Fabrik

 

[startpoint]

https://fabrikar.com/forums/index.php?threads/applying-dynamic-where-to-list.51121/#post-266980 ?

 

[troester]

Fabrik usually restricts the queries
by the list's or menu's "Rows per page" settings
by "Filter list max" in Fabrik options

Where do you get the unrestricted query?

 

[arnor@icetips.com]

Hi Troester,

So on an unfiltered list the query would basically translate to SELECT * FROM #__mytable and if the table has 100,000 rows, only 20 rows would be fetched if the list is set to show 20 rows. Am I understanding this correctly?

When I look at the query submitted by Fabrik (with fabrikdebug=1) there is no LIMIT statement, so I *assumed* that it wasn't limiting it. It also seems that the loading time is related to the number of total records loaded for the list, hence my concern for performance if the user ended up with a large dataset.

My concern is both performance and also guiding the users to submit "reasonable" filters.

Best regards,