Wrapping Red Bean to enforce filtering/restricting a SELECT

[Mike]

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