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
$filter = "Category.Title = 'Test',
$join= "LEFT JOIN NewsPage_Categories ON NewsPage_Categories.NewsPageID = $this->ID
LEFT JOIN Category ON Category.ID = NewsPage_Categories.CategoryID",
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?
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...
AND EXISTS (SELECT 1 FROM MyDataObject_OtherDataObject mdo_odo WHERE MyDataObject.ID = mdo_odo.MyDataObjectID)
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...
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...
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...