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.

Data Model Questions

sql query problem


Reply

2 Posts   1152 Views

Avatar
snaip

13 January 2010 at 11:37pm (Last edited: 13 January 2010 11:38pm), Community Member, 180 Posts

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;

Avatar
Willr

14 January 2010 at 9:43am Forum Moderator, 5511 Posts

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;