Jump to:

23379 Posts in 18298 Topics by 2867 members

General Questions

SilverStripe Forums » General Questions » [SOLVED] SQL Query with WHERE-Clause in LeftJoin

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

Page: 1
Go to End
Author Topic: 215 Views
  • Bereusei
    Avatar
    Community Member
    94 Posts

    [SOLVED] SQL Query with WHERE-Clause in LeftJoin Link to this post

    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?

  • ajshort
    Avatar
    Community Member
    244 Posts

    Re: [SOLVED] SQL Query with WHERE-Clause in LeftJoin Link to this post

    You should be able to do:

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

  • Bereusei
    Avatar
    Community Member
    94 Posts

    Re: [SOLVED] SQL Query with WHERE-Clause in LeftJoin Link to this post

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

    215 Views
Page: 1
Go to Top

Want to know more about the company that brought you SilverStripe? Then check out SilverStripe.com

Comments on this website? Please give feedback.