Jump to:

3373 Posts in 998 Topics by 712 members

Data Model Questions

SilverStripe Forums » Data Model Questions » sql query problem

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

Page: 1
Go to End
Author Topic: 1105 Views
  • snaip
    Avatar
    Community Member
    180 Posts

    sql query problem Link to this post

    hi

    i need to construct sql query in SilverStripe but i dont know how

    i have two tables

    EnToursPage
    - ID
    - ExtraContent
    (more ...)

    and

    EnToursHolder
    - ID
    - HighlightTour
    - HighlightYES

    in EnToursHolder (in CMS) i have dropdownlist where i can choose which EnToursPage i want to highlight

    then in template i want to show ID,Ttile,Content of highlights product (EnToursPage)

    so i need to do something like this
    SELECT * FROM EnToursHolder, EnToursPage WHERE EnToursHolder.HighlightTourID = EnToursPage.ID AND EnToursHolder.HighlightYES = 1;

    how to do it by DataObject::get ?

    $doSet = DataObject::get(
    $callerClass = "",
    $filter = "",
    $sort = "",
    $join = "",
    $limit = ""
    );
    return $doSet;

  • Willr
    Avatar
    Forum Moderator
    5462 Posts

    Re: sql query problem Link to this post

    Well you already have the query - you just need to fill in the blanks on DataObject get. Though it looks like you're doing a query over multiple tables so you'll have to use the JOIN paramter..

    SELECT * FROM EnToursHolder, EnToursPage WHERE EnToursHolder.HighlightTourID = EnToursPage.ID AND EnToursHolder.HighlightYES = 1;

    // note the join joins based on the parents ID.
    DataObject::get('EnToursPage', 'EnToursHolder.HighlightYES = 1 AND EnToursPage.ID = EnToursHolder.HighlightTourID ', '', 'LEFT JOIN EnToursHolder ON EnToursPage.ParentID = EnToursHolder.ID');

    Or if you want to use the ORM you can do something like below. Noting that you would need a has_one relationship between a tour and a holder rather then a plain DB field.

    // get all the holders
    $featuredHolders = DataObject::get('EnToursHolder', 'HighlightYES = 1');

    return ($featuredHolders) ? $featuredHolder->HighlightTour() : false;

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