When dealing with an abstraction that outputs dynamic SQL “behind your back”, it’s often easy to forget that some of the queries are… too greedy and grab unneeded data. Here is how to ask for less data, using CakePHP.
Today, I listed only name and id fields of a Model, and to restrict the findAll() to those things:
`$data = $this->Thing->findAll( aa("Thing.user_id", $user_id), a("id", "name"), null, null, null, -1 );`
Breaking down the call, we have:
aa() is a CakePHP convenience function to make an associative array. Odd parameters are keys, even params values. The parameters here creates a WHERE clause that matches the current user’s Things.
a() does the same for regular arrays. The params list the two fields we need returned.
A couple of null settings, and then the crucial -1, which turns off the Model associations completely.
Grab less data!
Update: here is how to use unbindModel. First take a look at the Thing model definition, courtesy of Mladen Mihajlovic:
array(
‘className’ => ‘AnotherThing’
),
‘SomeOtherThing’ => array(
‘className’ => ‘SomeOtherThing’
),
‘YetAnotherThing’ => array(
‘className’ => ‘YetAnotherThing’
)
);
}
?>
Now, in our controller, before calling findAll, we remove two of the associated Models:
$this->Thing->unbindModel( array( "hasMany" => array( "AnotherThing", "SomeOtherThing" ) ) );
[tags]cakephp[/tags]
The other suggestion would be to use $this->Thing->unbindModel(…) to remove any unneeded associations.
Whoops, missed the -1 paramter there. Still the unbindModel stands as a suggestion in case you need to remove some assications and leave others.
Thanks, Mladen. I could not figure out how unbindModel wanted its parameters, so after some digging, I asked the IRC hivemind about the thing, and AD7six piped up about the final param set to -1.
If you have an
unbindModelexample handy, I’d be pleased to update the post with it.Heh, yeah I struggled with it a bit in the beginning too.
$this->Thing->unbindModel( array( ‘hasMany’ => array(‘AnotherThing, ‘SomeOtherThing’) ) );
Basically the first array holds the keywords ‘hasMany’, ‘belongsTo’ etc.. each array within that would hold the names you gave the associations in your model.
Hmm, maybe I should be a bit more rhobust in my example.
class Thing {
var $hasMany = array(
‘AnotherThing’ => array(
‘className’ => ‘AnotherThing’
),
‘SomeOtherThing’ => array(
‘className’ => ‘SomeOtherThing’
),
‘YetAnotherThing’ => array(
‘className’ => ‘YetAnotherThing’
)
);
}
so the above unbind model would remove the bindings for the 1st 2 associations and leave the third one.
: to get ride of null, null, null, -1.
It’s important to note that aa() and a() are slow functions so probably a little bit extra code wouldn’t harm:
$this->Thing->recursive = -1;
$data = $this->Thing->findAll( array(“Thing.userid” => $userid), array(“id”, “name”));
problem with conditions is, it doesn’t reach associations other than belongTo and you can’t go deep in the association, same thing for the second param.
See:
https://trac.cakephp.org/ticket/633
https://trac.cakephp.org/ticket/1037