17488 Posts in 4473 Topics by 1978 members
|
Page:
1
|
Go to End | |
| Author | Topic: | 1168 Views |
-
Help with sql-queries...
14 September 2007 at 1:11pm
How would I do something like this using DataObject::get() ?
SELECT category.title, COUNT(article_categories.ArticleID) as totalArticles FROM article_categories, category WHERE article_categories.CategoryID = category.ID GROUP BY category.title
The purpose of this is to display the categories and the amount of related articles to them.
I know how to do it in normal php & mysql but I'm a bit stuck in how to go with it the ss-way...
thnx
-
Re: Help with sql-queries...
14 September 2007 at 2:45pm
Usually you shouldn't have to deal with many_many-tables at all on sql-level,
and use the provided dynamic methods such as "Articles()".$categories = DataObject::get('Category');
if($categories) foreach($categories as $category) {
echo $category->Articles()->Count();
}
Not the most performance-optimized solution, as its creating objects for each result - but that would be the "clean silverstripe-way". we've got some ideas on how to make the getters smarter with late querying (just do a simple select with "count" if nothing else is called on the set), but nothing coded yet... -
Re: Help with sql-queries...
14 September 2007 at 7:52pm
Ok, thanks.
I'm not completely understanding yet how you can say
$category->Articles()->Count();
I can understand Articles()->Count(); but don't really get the $category part on it...
If I get it right, than it takes 1 category - result and fetches the amount of articles on it, so exactly what I wanted. But I could never have made up this myself as I didn't understand that I can 'do' this articles-method ON a category result....Is there a part in de documentation where I can learn to grasp this 'dynamic' stuff?
Thanks a lot so far..
-
Re: Help with sql-queries...
14 September 2007 at 9:43pm Last edited: 14 September 2007 9:59pm
$category->Articles() refers to a DataObjectSet of Articles data objects, this can be produced on returning DataObject::get of Article data objects on an Articles() method. Example:
[code php]
class Category extends Page {function Articles() {
return DataObject::get('Article', "ParentID = $this->ID");
}function NumArticles() {
if($articles = $this->Articles()) {
return $articles->Count();
} else {
return '0';
}
}}
OR, it could be a has_many relationship, or a many_many to an Article class rather than just linking them by ParentID.
Some reading on the data model:
http://doc.silverstripe.com/doku.php?id=datamodel - general SilverStripe data model write up.
http://doc.silverstripe.com/doku.php?id=many_many-example - this example could be what you're trying to achieve!
Hope this helps!
Cheers,
Sean -
Re: Help with sql-queries...
14 September 2007 at 10:30pm
Hi, thanks.
I understand all that, I just can't seem to get how you can do
'$category->' part in it. It would never have come up in me to put
'$category->' in front of Articles()->count(); to get the amount of results per category...Indeed, I come from the many_many example
| 1168 Views | ||
|
Page:
1
|
Go to Top |
