this is follow up of this question. count number of foreign keys
i am using doctrine 1.2 and i want to count the number of business that belongs to subcategory.
following are the mysql tables.
1.fi_category
+----+-----------------+-----------------+
| id | name | slug |
+----+-----------------+-----------------+
2.fi_subcategory
+----+-----------------+-----------------+-------------+
| id | name | slug | category_id |
+----+-----------------+-----------------+-------------+
3.fi_business_subcategory
+----+-------------+----------------+
| id | business_id | subcategory_id |
+----+-------------+----------------+
i am using this DQL.
$q = Doctrine_Query::create()
->select('c.name, c.slug, sc.name, sc.slug')
->from('Model_Category c')
->leftJoin('c.Subcategory sc')
->leftJoin('sc.BusinessSubcategory bsc');
which gives me something like this.
Array
(
[0] => Array
(
[id] => 1
[name] => Entertainment & Lifestyle
[slug] => entertainment-lifestyle
[Subcategory] => Array
(
[0] => Array
(
[id] => 1
[name] => Arts and Crafts
[slug] => arts-and-crafts
)
[1] => Array
(
[id] => 2
[name] => Family
[slug] => family
)
[2] => Array
(
[id] => 3
[name] => Fashion
[slug] => fashion
)
)
)
)
i am looking to fetch the number of business, i.e the returned result should be something like this depending on the business it belongs.
Array
(
[0] => Array
(
[id] => 1
[name] => Entertainment & Lifestyle
[slug] => entertainment-lifestyle
[Subcategory] => Array
(
[0] => Array
(
[id] => 1
[name] => Arts and Crafts
[slug] => arts-and-crafts
[business_count] => 35
)
[1] => Array
(
[id] => 2
[name] => Family
[slug] => family
[business_count] => 10
)
[2] => Array
(
[id] => 3
[name] => Fashion
[slug] => fashion
[business_count] => 27
)
)
)
)
tried various ways using DQL, but nothing seems to work out. any idea how should i go with what i want?
