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.

We've moved the forum!

Please use forum.silverstripe.org for any new questions (announcement).
The forum archive will stick around, but will be read only.

You can also use our Slack channel or StackOverflow to ask for help.
Check out our community overview for more options to contribute.

Data Model Questions /

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

DataObject::get Filter Problem


Go to End


8 Posts   5784 Views

Avatar
sashion

Community Member, 25 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, 25 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, 1899 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, 25 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, 1899 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, 25 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, 1391 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...