Jump to:

17452 Posts in 4473 Topics by 1971 members

Archive

SilverStripe Forums » Archive » Help with sql-queries...

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

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

Page: 1
Go to End
Author Topic: 1305 Views
  • dio5
    Avatar
    Community Member
    501 Posts

    Help with sql-queries... Link to this post

    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

  • Ingo
    Avatar
    Forum Moderator
    801 Posts

    Re: Help with sql-queries... Link to this post

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

  • dio5
    Avatar
    Community Member
    501 Posts

    Re: Help with sql-queries... Link to this post

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

  • Sean
    Avatar
    Forum Moderator
    921 Posts

    Re: Help with sql-queries... Link to this post

    $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

  • dio5
    Avatar
    Community Member
    501 Posts

    Re: Help with sql-queries... Link to this post

    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

    1305 Views
Page: 1
Go to Top

Want to know more about the company that brought you SilverStripe? Then check out SilverStripe.com

Comments on this website? Please give feedback.