[found_rows]

Description

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.

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.

Download

> ' 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

Please log in to comment