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

[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

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