In a web application I have many queries where the results set is supposed to be restricted to a subset. This subset depends on which user is logged in. In pure MySQL, it comes down to restricting the number of entities, checking that they are related to a set of
projects.
For example if I write a query:
R::getAll("SELECT project.shortname AS project, DATE_FORMAT(reportedtime,"%Y-%m") AS month, count(1) AS hazardcards
FROM hazardcard
JOIN project ON hazardcard.project_id = project.id
GROUP BY project, month"
this works, but what I often forget to do, when developing new queries to make sure not to include anything the user is not entitled to see: The 'correct' form of the query is, in my case:
R::getAll('SELECT project.shortname AS project, DATE_FORMAT(reportedtime,"%Y-%m") AS month, count(1) AS hazardcards
FROM hazardcard
JOIN project ON hazardcard.project_id = project.id
WHERE project.id IN ('. R::genSlots($projIds) .
') GROUP BY project, month', $projIds);
where the $projIds are set by other code, based on which user is logged in.
OK, so far so good.
This post is about finding a way to 'overlay' this
WHERE project.id IN ( R:: genSlots()) on any query, so that all queries are 'imbued' with an awareness of this requirement. This is what I have come up with so far,
class RedBeanWrapper {
protected $projectIds;
public function __construct($projIds){
$this->projectIds = $projIds;
}
/*
* imbueQuery() ensures that the results are restricted to the projects that
* the logged-in user is entitled to see, by adding a
*
* WHERE project.id IN (?,?,?, ... )
*
* with the projectIds supplied on the construction of RedBeanWrapper
*
*/
public function imbueQuery($sql) {
// Is there a GROUP BY in the query?
if (preg_match('/GROUP BY/', $sql)) {
return preg_replace('/GROUP BY/', 'WHERE project.id IN ('. R::genSlots($this->projectIds) . ') GROUP BY', $sql);
}
}
public function getAll($sql) {
$sql = $this->imbueQuery($sql);
return R::getAll($sql,$this->projectIds);
}
}
What does this do?
1. It forces/reminds me to get and provide this list of $projIds by requiring it in the constructor.
2. If I pass a MySQL query, it adds in the
WHERE project.id IN ( R:: genSlots()) so I never forget to do it.
Obviously it needs to me more developed to cope with (at a minimum) queries without a GROUP BY, but this is just a minimal example.
It is used as follows:
$rbw = new RedBeanWrapper($projIds);
$sql = 'SELECT project.shortname AS project, DATE_FORMAT(reportedtime,"%Y-%m") AS month, count(1) AS hazardcards
FROM hazardcard JOIN project ON hazardcard.project_id = project.id GROUP BY project, month';
return $rbw->getAll($sql);
The idea is that Red Bean facade is not available to this code, so I must use the wrapper.
My question to more experienced Red Bean users is: is there a better way to do this:
- would you, rather than using a wrapper, extend the Red Bean facade and impose a ->withCondition() on all calls to R::find()**
- can this be done with a plugin? How would you implement this in a R::imbue()???
** this whole this is a search for the best way to implement a ->withCondition() on a getAll query.
All ideas welcome!
Thank you
Mike