Jump to:

3373 Posts in 998 Topics by 712 members

Data Model Questions

SilverStripe Forums » Data Model Questions » DataObject::get Filter Problem

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

Page: 1
Go to End
Author Topic: 3154 Views
  • sashion
    Avatar
    Community Member
    21 Posts

    DataObject::get Filter Problem Link to this post

    Hey guys,

    i´ve a question.
    I´ve got a many to many relationship: One Category can have many news pages and a news page can have several categories
    i´ve basically the following peace of code

    return DataObject::get(
        $obj="NewsPage",
        $filter = "Category.Title = 'Test',
        $sort="",
        $join= "LEFT JOIN NewsPage_Categories ON NewsPage_Categories.NewsPageID = $this->ID
               LEFT JOIN Category ON Category.ID = NewsPage_Categories.CategoryID",
        $limit="{$SQL_start},{$limit}"
        );
       }

    The result doesn´t show one object ALTHOUGH there is a Category with the Title "Element".
    For example if I use for the filter the following $filter = "Headline = 'Test Headline'" a result is shown.

    I have the opinion that the filter variable does ONLY accept the table defined in $obj.. in this case "NewsPage".
    But i need this double join to combine the three tables of the many-to-many relationsship and do a filter in the CATEGORY table and NOT the newsPage table.

    Does anyone have an idea? Is this possible in silverstripe?

    Thanx in advance,
    sash

  • sashion
    Avatar
    Community Member
    21 Posts

    Re: DataObject::get Filter Problem Link to this post

    oh no... 20 views and no idea

    this can´t be too difficult but i didn´t really find any similar example.
    but actually it´s just a query with 2 left joins and a WHERE clause filter on the second obejct.

    has nobody anything silmilar to this? .. or maybe a better solution?

    cheers

  • swaiba
    Avatar
    Forum Moderator
    1769 Posts

    Re: DataObject::get Filter Problem Link to this post

    oh no... 20 views and no idea

    try 546 and no idea (http://www.silverstripe.org/data-model-questions/show/14403)

    Is this possible in silverstripe?

    yes, I often have some very complex SQL in my where clause, although because I am "old skool" I still make all my joins without the LEFT OUTER JOIN business (although I would for an INNER join, but I rarely need to worry about that). Anyway...

    DataObject::get('MyDataObject','field=1
    AND EXISTS (SELECT 1 FROM MyDataObject_OtherDataObject mdo_odo WHERE MyDataObject.ID = mdo_odo.MyDataObjectID)
    AND...
    ');

    hope this helps...

  • sashion
    Avatar
    Community Member
    21 Posts

    Re: DataObject::get Filter Problem Link to this post

    Hey swaiba,

    is there something missing in your code? i´m not sure if i get it right.
    I thought the WHERE statement (the filter) is the second argument.

    Can you see if there is anything wrong with my code or could you use it as a base for your code?

    return SiteTree::get(
    $obj="NewsPage",
    $filter = "Category.Title = 'Test',
    $sort="",
    $join= "LEFT JOIN NewsPage_Categories ON NewsPage_Categories.NewsPageID = $this->ID
    LEFT JOIN Category ON Category.ID = NewsPage_Categories.CategoryID",
    $limit="{$SQL_start},{$limit}"
    );
    }

    cheers

  • swaiba
    Avatar
    Forum Moderator
    1769 Posts

    Re: DataObject::get Filter Problem Link to this post

    Hi sashion, I was explaining how you *could* do a join within the where clause - however bad practice. I don't follow what you want the filter to do - could you rephrase the question, maybe with an example, and I'll have a go at the SQL...

  • danzzz
    Avatar
    Community Member
    175 Posts

    Re: DataObject::get Filter Problem Link to this post

    hi

    may this helps to, a snipped I'm using ...

    $filter = 'Program.active = 1 AND
        Program.featured = 1 AND
        CategoryPage_Programs.CategoryPageID = '.$this->ID.' AND
        CategoryPage_Programs.ProgramID = Program.ID';
       $sort = 'name ASC';
       $join = 'JOIN CategoryPage_Programs';
       $limit = "{$SQL_start},{$limit}";
       $Items = Dataobject::get('Program', $filter, $sort, $join, $limit);

  • sashion
    Avatar
    Community Member
    21 Posts

    Re: DataObject::get Filter Problem Link to this post

    Hey guys,

    thanks for your support. I got it fixed now (see code below).
    It seems to be important to write `NewsPage`.ID instead of NewsPage.ID else Silverstripe returns me an error that it wouldn´t know the Column...

    $sitetree = DataObject::get(
        $obj="NewsPage"
        $filter = "NewsPage_Categories.CategoryID = $_GET['catID'],
        $sort="Datum DESC",
        $join= "LEFT JOIN NewsPage_Categories ON NewsPage_Categories.NewsPageID = `NewsPage`.ID",
        $limit="{$SQL_start},{$limit}"
        );

    Cheers

  • martimiz
    Avatar
    Forum Moderator
    1035 Posts

    Re: DataObject::get Filter Problem Link to this post

    Sometimes in situations like this it helps to put your site in dev mode and then add ?showqueries=1 to the url you're calling. Then check out the query that doesn't work and try it on your MySQL database (PHPMyAdmin or ...)

    You might have to replace " by ` for that to work, but it helped me quite a number of times...

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