Skip to main content

This site requires you to update your browser. Your browsing experience maybe affected by not having the most up to date version.

Archive

Our old forums are still available as a read-only archive.

Moderators: martimiz, Sean, biapar, Willr, Ingo, simon_w

Help with sql-queries...


Reply

5 Posts   1318 Views

Avatar
dio5

14 September 2007 at 1:11pm Community Member, 501 Posts

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

Avatar
Ingo

14 September 2007 at 2:45pm Forum Moderator, 801 Posts

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...

Avatar
dio5

14 September 2007 at 7:52pm Community Member, 501 Posts

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.. :-)

Avatar
Sean

14 September 2007 at 9:43pm (Last edited: 14 September 2007 9:59pm), Forum Moderator, 921 Posts

$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:

[url]http://doc.silverstripe.com/doku.php?id=datamodel[/url] - general SilverStripe data model write up.
[url]http://doc.silverstripe.com/doku.php?id=many_many-example[/url] - this example could be what you're trying to achieve! :-)

Hope this helps!

Cheers,
Sean

Avatar
dio5

14 September 2007 at 10:30pm Community Member, 501 Posts

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 :-)