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.

Data Model Questions /

DataObject::get Filter Problem


Reply


8 Posts   3590 Views

Avatar
sashion

Community Member, 23 Posts

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

Avatar
sashion

Community Member, 23 Posts

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

Avatar
swaiba

Forum Moderator, 1805 Posts

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

Avatar
sashion

Community Member, 23 Posts

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

Avatar
swaiba

Forum Moderator, 1805 Posts

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

Avatar
danzzz

Community Member, 175 Posts

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

Avatar
sashion

Community Member, 23 Posts

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

Avatar
martimiz

Forum Moderator, 1132 Posts

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