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   1227 Views

Avatar
snaip

Community Member, 180 Posts

13 January 2010 at 11:37pm

Edited: 13/01/2010 11:38pm

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

Forum Moderator, 5513 Posts

14 January 2010 at 9:43am

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;