[found_rows] at tagSwap.net: Open Source Lasso Code

[found_rows]

Description

Link: [found_rows]

Author: Johan Sölve

Category: Database

Version: 8.x

License: Public Domain

Posted: Jun. 09, 2006

Updated: Jan. 24, 2007

More by this author...

When using -uselimit in a MySQL search, [found_count] will return the value of -maxrecords (at most) instead of the true count of found records.

This tag always returns the true count of found records also when using -uselimit in an inline, as well as when using LIMIT in an -sql inline. Use the tag as a replacement for [found_count].

The tag is only intended for MySQL versions 4.0 or higher. The tag requires Lasso 8.0 or later.

Lasso 8.5 and later returns the correct value for found_count when using -uselimit with regular inlines, but when using -sql inlines this tag is still needed to get the correct count of found records when having LIMIT in the query.

Credit to Jason Huck for ideas for this tag.

Update: changed default method to COUNT(*) since SQL_CALC_FOUND_ROWS appears to be much slower.

Parameters

none

Sample Usage

inline: -database='mydatabase',
	-table='mytable',
	-op='gte', 'id'=22,
	-maxrecords=10,
	-skiprecords=50,
	-uselimit,
	-search;
	found_rows + ' found records';
/inline;

Source Code

Click the "Download" button below to retrieve a copy of this tag, including the complete documentation and sample usage shown on this page. Place the downloaded ".inc" file in your LassoStartup folder, restart Lasso, and you can begin using this tag immediately.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

<?LassoScript

define_tag: 'found_rows';
	  local: 'sql'= action_statement;
  if: #sql !>> ' LIMIT ';
    // found_count must be accurate
    return: found_count;
  /if;
  if: #sql !>> ' GROUP BY ';
    // Default method, usually the fastest. Can not be used with GROUP BY for example. 
    #sql = 'SELECT COUNT(*) AS found_rows ' + #sql -> (substring: (#sql -> (find: ' FROM ')) + 1) ;
    #sql = (string_replaceregexp: #sql, -find='LIMIT\\s[0-9,]+', -replace='');
  else; // query contains GROUP BY so use SQL_CALC_FOUND_ROWS which can be much slower, see http://bugs.mysql.com/bug.php?id=18454
    #sql -> (removeleading: 'SELECT ');
    #sql = 'SELECT SQL_CALC_FOUND_ROWS ' + #sql + ';SELECT FOUND_ROWS() AS found_rows';
    #sql = (string_replaceregexp: #sql, -find='LIMIT\\s+[0-9,]+', -replace='LIMIT 1', -ignorecase);
  /if;
  inline: -sql=#sql;
    if: (field: 'found_rows') > 0;
      return: integer: (field: 'found_rows'); // exit here normally
    /if;
  /inline;
  // fallback
  return: found_count;
/define_tag;


?>

Related Tags

[row_count]

Comments

01/24/2007, Johan Sölve
Tag updated
I updated the tag to use COUNT(*) instead of SQL_CALC_FOUND_ROWS as long as the query does not contain GROUP BY. Hopefully this will make the tag much faster when possible.

01/24/2007, Johan Sölve
Performance warning
A bit of warning when using this tag - SQL_CALC_FOUND_ROWS can be really slow, in many situations COUNT(*) is several times faster. See for example http://bugs.mysql.com/bug.php?id=18454 However, for complex queries for example with GROUP statements it can be difficult to use COUNT(*) to return a proper result.