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

You could use SQLQuery 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` ....