Data Model Questions

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

Moderators: martimiz, Howard, Sean, Ryan M., biapar, Willr, Ingo, swaiba, simon_w

Page: 1	Go to End
Author	Topic:	2341 Views

sashion

Community Member
16 Posts

DataObject::get Filter Problem

6 June 2011 at 8:58am

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

Community Member
16 Posts

Re: DataObject::get Filter Problem

7 June 2011 at 2:15am

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

Forum Moderator
1689 Posts

Re: DataObject::get Filter Problem

7 June 2011 at 3:21am

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

Community Member
16 Posts

Re: DataObject::get Filter Problem

7 June 2011 at 6:20am

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

Forum Moderator
1689 Posts

Re: DataObject::get Filter Problem

8 June 2011 at 6:32pm

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

Community Member
175 Posts

Re: DataObject::get Filter Problem

8 June 2011 at 11:17pm

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

Community Member
16 Posts

Re: DataObject::get Filter Problem

9 June 2011 at 1:52am

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

Forum Moderator
880 Posts

Re: DataObject::get Filter Problem

9 June 2011 at 4:27am

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