Counting records using whole SQL string in RedBeanPHP

Question

It appears that RedBeanPHP cannot take in an SQL query string like other functions can, and I'm trying to find out the best way to work around it.

Lets say I have the following query (simplified)

SELECT id, name, email FROM tbluser WHERE id > 5000;

Rather than return all the data and then count it, I want to ONLY fetch the count first (for pagination purposes). I cannot simply pass the above SQL into R::count() because it does not work with whole strings.

//valid 
$count = R::count('tbluser', 'WHERE id > 5000');

//not valid
$sql = "SELECT id, name, email FROM tbluser WHERE id > 5000";
$count = R::count($sql);

I strongly prefer my query as a whole SQL string to reduce complexity in reusing it, but it doesn't seem possible. I can accept only changing the SELECT field, but I cannot break apart the body as it's too complex.

So next I was thinking to try just replacing the select header with a COUNT(*) SQL

$sql = "SELECT COUNT(*) FROM tbluser WHERE id > 5000";

//this doesn't exactly work, it returns 1 rather than the actual count
$count = R::exec($sql);

//however this DOES work, but is pretty sloppy
$count = R::getAll($sql)[0]["COUNT(*)"];

Any ideas on how I can either get R::count() to work (seems unsupported for whole SQL strings), or how I can get R::exec() to return the actual count?

Answer 1

You can use R::getCell() to fetch a single column of the first row of results.

$sql = "SELECT COUNT(*) FROM tbluser WHERE id > 5000";
$count = R::getCell($sql)

You can also convert the result of a query to a bean with R::findFromSQL(). Then you can use its countOwn() method.

$users = R::findFromSQL(""SELECT id, name, email FROM tbluser WHERE id > 5000");
$count = $users->countOwn('id');

This allows you to use $users to fetch the data as well, so you don't have to do two queries.