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.

General Questions /

General questions about getting started with SilverStripe that don't fit in any of the categories above.

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

[SOLVED] SQL Query with WHERE-Clause in LeftJoin


Reply


3 Posts   281 Views

Avatar
Bereusei

Community Member, 94 Posts

24 January 2014 at 5:19am

Hey guys,

I need your help. I´ve got a problem, that really freak me out.

I have two DataObjects: "Page" and "Book". One "Book" has many "Pages".
The "Book" have a variable called "Active".

Now I want to get the IDs from "Page", but only from the pages which has an "active" Book.
So I need something like this:

SELECT Page.ID FROM Page
LEFT JOIN Book ON Page.BookID = Book.ID
WHERE Book.Active = 1

Now have tried to

$sqlQuery = new SQLQuery();
$sqlQuery->setFrom("Page");
$sqlQuery->selectField("Page.ID", "ID");
$sqlQuery->addLeftJoin('Book','"Page.BookID" = "Book.ID"');
$sqlQuery->addWhere(' "Book.Active"=1 ');

But with this code, I get the error "Unknown column Book.Active". Any idea, how to fix this?

Avatar
ajshort

Community Member, 244 Posts

24 January 2014 at 3:57pm

You should be able to do:

Page::get()->filter('Book.Active', true)->column('ID');

Avatar
Bereusei

Community Member, 94 Posts

24 January 2014 at 9:51pm

GREAT! It works, thanks!
So easy and simple.