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.

We've moved the forum!

Please use forum.silverstripe.org for any new questions (announcement).
The forum archive will stick around, but will be read only.

You can also use our Slack channel or StackOverflow to ask for help.
Check out our community overview for more options to contribute.

Archive /

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

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

Help with sql-queries...


Go to End


5 Posts   1723 Views

Avatar
dio5

Community Member, 501 Posts

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

Avatar
Ingo

Forum Moderator, 801 Posts

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

Avatar
dio5

Community Member, 501 Posts

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

Avatar
Sean

Forum Moderator, 922 Posts

14 September 2007 at 9:43pm

Edited: 14/09/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

Avatar
dio5

Community Member, 501 Posts

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