does CakePHPs Containable-behavior support custom expressions as conditions?

Question

Community,

I'm currently facing an issue with the containable-behavior setting conditions based on the datasources expression-builder. I'm using CakePHP 2.6.2 with a PostgreSQL database.

What works so far:

I wrote a behavior that dynamically adds conditions to find-operations to restrict the results based on a privileges table. Im using subqueries with the buildstatement() and expression() functions provided by cake. I followed this article from the CakeBook:

http://book.cakephp.org/2.0/en/models/retrieving-your-data.html

Here is a simplyfied code-snippet, in fact its two OR-statements:

$conditionsSubQueryRecord = array(
  'Privilege.objecttable' => $model->table,
  'Privilege.objectid = '.$model->alias.'.'.$model->primaryKey,
  'Privilege.read' => true,
  'Privilege.id' => $this->recordPermissions
);
$dsPrivilege = $this->privilegeModel->getDataSource();
$subQueryRecordPrivs = $dsPrivilege->buildStatement(
array(
  'fields'     => array('"'.$this->privilegeModel->alias.'"."id"'),
  'table'      => $dsPrivilege->fullTableName($this->privilegeModel),
  'alias'      => $this->privilegeModel->alias,
  'limit'      => null,
  'offset'     => null,
  'joins'      => array(),
  'conditions' => $conditionsSubQueryRecord,
  'order'      => null,
  'group'      => null
),
$this->privilegeModel
);
$subQueryRecordPrivs = ' EXISTS (' . $subQueryRecordPrivs . ') ';
$subQueryRecordPrivsExpression = $dsPrivilege->expression($subQueryRecordPrivs);

I'm adding the statement to my condition array then in my behaviors beforeFind()-hook. This works all very well so far. The condition is added, the results are filtered.

The conditions are ignored for my contained models:

My problem is now to use this condition on contained models. I wrote an recursive algorithm that walks along all the contained modelsand if the model actsAs my behavior I am attaching the same conditions to its conditions-array. But when I execute my search, the condition is ignored on the contained models and only attached to the primary model.

This is the complete condition string I'm executing:

array(
    'conditions' => array(
        'Requestinstance.id' => (int) 4,
        (int) 0 => object(stdClass) {
            type => 'expression'
            value => ' EXISTS (SELECT "Privilege"."id" FROM "core"."privileges" AS "Privilege"   WHERE "Privilege"."objecttable" = 'requestinstances' AND "Privilege"."objectid" = "Requestinstance"."id" AND "Privilege"."read" = 'TRUE' AND "Privilege"."id" = (8))  OR  EXISTS (SELECT "Privilege"."id" FROM "core"."privileges" AS "Privilege"   WHERE "Privilege"."objecttable" = 'requestinstances' AND "Privilege"."read" = 'TRUE' AND "Privilege"."id" IN (7, 13, 6, 9, 10, 12) AND "Privilege"."objectid" IS NULL) '
        }
    ),
    'fields' => null,
    'joins' => array(),
    'limit' => (int) 1,
    'offset' => null,
    'order' => array(
        (int) 0 => null
    ),
    'page' => (int) 1,
    'group' => null,
    'callbacks' => true,
    'contain' => array(
        'Requesttype' => array(
            'Steptype' => array(
                'order' => array(
                    (int) 0 => 'RequesttypesSteptype.phase ASC'
                ),
                'conditions' => object(stdClass) {
                    type => 'expression'
                    value => ' EXISTS (SELECT "Privilege"."id" FROM "core"."privileges" AS "Privilege"   WHERE "Privilege"."objecttable" = 'steptypes' AND "Privilege"."objectid" = "Steptype"."id" AND "Privilege"."read" = 'TRUE' AND "Privilege"."id" = (8))  OR  EXISTS (SELECT "Privilege"."id" FROM "core"."privileges" AS "Privilege"   WHERE "Privilege"."objecttable" = 'steptypes' AND "Privilege"."read" = 'TRUE' AND "Privilege"."id" IN (7, 13, 6, 9, 10, 12) AND "Privilege"."objectid" IS NULL) '
                }
            ),
            (int) 0 => 'RequesttypesSteptype',
            'conditions' => object(stdClass) {
                type => 'expression'
                value => ' EXISTS (SELECT "Privilege"."id" FROM "core"."privileges" AS "Privilege"   WHERE "Privilege"."objecttable" = 'requesttypes' AND "Privilege"."objectid" = "Requesttype"."id" AND "Privilege"."read" = 'TRUE' AND "Privilege"."id" = (8))  OR  EXISTS (SELECT "Privilege"."id" FROM "core"."privileges" AS "Privilege"   WHERE "Privilege"."objecttable" = 'requesttypes' AND "Privilege"."read" = 'TRUE' AND "Privilege"."id" IN (7, 13, 6, 9, 10, 12) AND "Privilege"."objectid" IS NULL) '
            }
        ),
        'Stepinstance' => array(
            (int) 0 => 'Steptype',
            (int) 1 => 'Stepdatainstance',
            (int) 2 => 'Sectioninstance'
        ),
        'Requestdatainstance' => array(),
        'Taskinstance' => array()
    ),
    'recursive' => (int) 2
)

As you can see, the condition was correctly added to some of the contained models. However, the executed SQL-query, i.e. for the "Steptype"-Model, is generated without the condition:

SELECT "Steptype"."id" AS "Steptype__id", "Steptype"."name" AS "Steptype__name", "Steptype"."description" AS "Steptype__description", "Steptype"."subscribe" AS "Steptype__subscribe", "RequesttypesSteptype"."id" AS "RequesttypesSteptype__id", "RequesttypesSteptype"."phase" AS "RequesttypesSteptype__phase", "RequesttypesSteptype"."endsphase" AS "RequesttypesSteptype__endsphase", "RequesttypesSteptype"."endsrequest" AS "RequesttypesSteptype__endsrequest", "RequesttypesSteptype"."usertype_id" AS "RequesttypesSteptype__usertype_id", "RequesttypesSteptype"."requesttype_id" AS "RequesttypesSteptype__requesttype_id", "RequesttypesSteptype"."steptype_id" AS "RequesttypesSteptype__steptype_id" FROM "core"."steptypes" AS "Steptype" JOIN "core"."requesttypes_steptypes" AS "RequesttypesSteptype" ON ("RequesttypesSteptype"."requesttype_id" = 6 AND "RequesttypesSteptype"."steptype_id" = "Steptype"."id") ORDER BY "RequesttypesSteptype"."phase" ASC

Direct use of the buildStatement does not work either

I also tried to use the statement itself directly, without building an expression from it. This actually creates exactly the SQL-query I want to have, but does not add the quotes of the table alias in the FROM-clause correctly and therefore causes postgreSQL to throw an error:

SELECT "Requestinstance"."id" AS "Requestinstance__id", "Requestinstance"."user_id" AS "Requestinstance__user_id", "Requestinstance"."created" AS "Requestinstance__created", "Requestinstance"."requesttype_id" AS "Requestinstance__requesttype_id", "Requestinstance"."currentphase" AS "Requestinstance__currentphase", "Requestinstance"."selfsolving" AS "Requestinstance__selfsolving", "User"."username" AS "User__username", "User"."id" AS "User__id", "User"."company_id" AS "User__company_id", "User"."usertype_id" AS "User__usertype_id", "Requesttype"."id" AS "Requesttype__id", "Requesttype"."name" AS "Requesttype__name", "Requesttype"."subtitle" AS "Requesttype__subtitle", "Requesttype"."description" AS "Requesttype__description", "Requesttype"."order" AS "Requesttype__order", "Requesttype"."selfsolving" AS "Requesttype__selfsolving" FROM "core"."requestinstances" AS "Requestinstance" LEFT JOIN "core"."users" AS "User" ON ("Requestinstance"."user_id" = "User"."id") LEFT JOIN "core"."requesttypes" AS "Requesttype" ON ("Requestinstance"."requesttype_id" = "Requesttype"."id") WHERE EXISTS (SELECT "Privilege"."id" FROM "core"."privileges" AS Privilege WHERE "Privilege"."objecttable" = 'requestinstances' AND "Privilege"."objectid" = "Requestinstance"."id" AND "Privilege"."read" = 'TRUE' AND "Privilege"."id" = (8)) OR EXISTS (SELECT "Privilege"."id" FROM "core"."privileges" AS Privilege WHERE "Privilege"."objecttable" = 'requestinstances' AND "Privilege"."read" = 'TRUE' AND "Privilege"."id" IN (7, 13, 6, 9, 10, 12) AND "Privilege"."objectid" IS NULL) LIMIT 1

Adding the quotes manually to the alias-string while building the statement does not help either, since the framework strips the quotes.

So finally my question(s):

Does anybody know, if the containable-behavior supports expressions at all? I already digged into the DboSource, PdoSource and Postgresql-datasource but could not find anything wrong here. The Containable behavior looks pretty straight forward as well. Am I doing something wrong here?

Or is there another way I could acchieve what I want?

I'm glad for any help in this matter! Thanks in advance!

score 0 · Answer 1 · answered Apr 22 '15 at 13:27

I finally figured it out!

To formally answer the question:

Yes, the containable behavior does support expression-syntax!

My problem was with the processing order: The framework processes the behaviors the configured order, I accidently loaded the containable before my custom behavior, thats why it never received my modified conditions...

Working with the correct order, the manipulated condition-string was processed fine by the containable behavior.

To be absolutely sure about the behaviors order, I moved the behavior-loading to the AppModels __construct() method:

// unload any configured Containable behavior
if($this->Behaviors->loaded('Containable')) {
    $this->Behaviors->unload('Containable');    
}
// load the PrivilegeItem behavior
if($this->alias !== 'Privilege') {
    $this->Behaviors->load('PrivilegeItem');
}
// and finally (re-)attach the Containable behavior     
$this->Behaviors->load('Containable');

Maybe it helps others avoiding two days of debugging headache...

does CakePHPs Containable-behavior support custom expressions as conditions?

1 Answers1