In cakePHP, how to retrieve joined result from multiple tables
Posted
by Manish Sharma
on Stack Overflow
See other posts from Stack Overflow
or by Manish Sharma
Published on 2009-04-30T12:25:30Z
Indexed on
2010/03/29
10:03 UTC
Read the original article
Hit count: 431
Hi, can anyone tell me, how to retrieve joined result from multiple tables in cakePHP ( using cakePHP mvc architecture). For example, I have three tables to join (tbl_topics, tbl_items, tbl_votes. Their relationship is defined as following: a topic can have many items and an item can have many votes. Now I want to retrieve a list of topics with the count of all votes on all items for each topic. The SQL query for this is written below:
SELECT Topic.*, count(Vote.id) voteCount
FROM
tbl_topics AS Topic
LEFT OUTER JOIN tbl_items AS Item
ON (Topic.id = Item.topic_id)
LEFT OUTER JOIN tbl_votes AS Vote
ON (Item.id = Vote.item_id);
My problem is I can do it easily using $this-><Model Name>->query
function, but this requires sql code to be written in the controller which I don't want. I'm trying to find out any other way to do this (like find()
).
© Stack Overflow or respective owner