Adding a custom query to the model

Question

For simplicity's sake, I have two tables: projects and tasks. A project can have many tasks, and a task belongs to a project. I've set up the database, created the associations and used cake bake to generate the models, controllers and views and all is perfect. When I look at a the index view for projects, I see a table listing all the projects as expected. What I want to do is really simple: I want a column in that table that shows a count of all the tasks assigned to that project.

The SQL query for this is trivial (returning a list of project names and a count of the tasks):

SELECT projects.name,
(SELECT COUNT(*) FROM tasks WHERE project_id = projects.id) as taskCount
FROM projects

So how do I achieve this in CakePHP? The index method in the projects controller looks like this at the moment:

function index() {
    $this->Project->recursive = 0;
    $this->set('projects', $this->paginate());
}

Empty · Accepted Answer · 2011-06-28 09:53:45Z

1

You can use the virtualFields or counterCache, but it is another way.

answered Jun 28, 2011 at 9:53

Empty

4572 silver badges6 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ben Over a year ago

Added this to the project model: var $virtualFields = array ( 'taskcount' => 'SELECT COUNT(*) FROM tasks WHERE project_id = Project.id'); and all is good!

Collectives™ on Stack Overflow

Adding a custom query to the model

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related