CakePHP: COUNT function and model joining help

Question

Hi,

I'm trying to join tables to get a count on a field but am having trouble figuring out how to do it. here are my tables:

tags
id     INT
tag    VARCHAR

project_tags
id         INT
project_id INT
tag_id     INT

projects
id     INT
...

I want show in my view something like this:

[tags.tag] x 23
[tags.tag] x 12
...

Now I'm no SQL expert but I think what I'd be aiming for in SQL would be performing count on tag_id where tag_id = tags.id grouped by tag_id. I think.

I'm just now so sure how to do this in a cake controller.

Can someone help?

Answer 1

Perhaps send this query to the database:

 SELECT t.tag, COUNT(*) AS NumOccurrances
 FROM project_tags pt 
 INNER JOIN tags t ON t.id = pt.tag_id
 GROUP BY  t.tag
 ORDER BY 2 DESC  --sorting by count.

In your controller, execute ad-hoc SQL:

$results = $this->Tag->query("select ...");

More on CakePHP querying a database. Perhaps roll this up into a stored procedure in MySQL, and call from your Cake controller.

$results = $this->query("CALL MyStoredProc");