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?
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.