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.

We've moved the forum!

Please use forum.silverstripe.org for any new questions (announcement).
The forum archive will stick around, but will be read only.

You can also use our Slack channel or StackOverflow to ask for help.
Check out our community overview for more options to contribute.

Data Model Questions /

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

sql query problem


Go to End


2 Posts   1828 Views

Avatar
snaip

Community Member, 181 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, 5523 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;