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 /

DataObject::get() Problem: Not Able to get Fields from JOINed table...


Reply


2 Posts   2021 Views

Avatar
Garrett

Community Member, 245 Posts

11 August 2009 at 5:20am

Hi, I am having a problem using a DataObject::get() call whereby I am using a couple of LEFT JOINs to try to get some filenames of some images associated with a page (associated by a ComplexTableField which creates its own table in the DB). The JOIN seems to work fine, I guess, however there doesn't seem to be any way to get the FIELDS from the JOINed table since I cannot modify the SELECT itself. Is there any way to do this?? I don't see the point of the JOIN if I cannot get these fields. I don't want to do a DB:query() call because I need all the results paging methods which I cloned from the Blog module.

CODE:
return DataObject::get("Page","`ParentID` = $this->ID AND ShowInMenus = 1 $tagCheck $catCheck $dateCheck","`GalleryPage`.Date DESC",'LEFT JOIN GalleryImageAttachment GIA ON GalleryPage_Live.ID = GIA.GalleryPageID LEFT JOIN File F ON F.ID = GIA.ImageID',"$start, $limit");

Need the filenames from the File table.

Help?

Thanks,
Garrett

Avatar
Hamish

Community Member, 712 Posts

11 August 2009 at 6:31pm

You could use [url=http://doc.silverstripe.org/doku.php?id=sqlquery]SQLQuery[/url] instead - this allows you to define the query in more depth.

DataObject::get() forces you to use the object model. For example, it (quite rightly) ensues you don't get any duplicates of Page, so it would be meaningless to only provide the rest of the fields.

However - it looks like you should flip this around. You are grabbing the current page object then trying to drill down on the associated file fields. Since you are trying to get Files, why not use DataObject::get("File") with the appropriate left joins back to the page object? This way, you're using the object model 'nicely' and you get the usual helper methods.

eg:

return DataObject::get(
   'File',
   '`Page`.`ParentID` = '{$this->ID}',
   'LEFT JOIN `GalleryImageAttachment` ....