I want to select values from two tables.

I was trying to use the DataObject::get syntax.

DataObject::get("ccProjects", "ccProjects.memberID = $memberID",''," left join ccImages on ccProjects.ID = ccImages.ProjectId"))

The page runs but I'm not sure why I can't get all values to output in template. The only value that outputs is the Name field from the ccProjects table. The ccImages table has a column named Name too. How do I output that field in the template?

This is all going on in a method of the ccProjects class and is referenced in the template by:

<% control getccProjects %>

<a href="#">$Name</a> 
		
<% end_control %>

In that case, you might wanna use SQLQuery (http://doc.silverstripe.com/doku.php?id=sqlquery) instead because with DataObject you can only retrieve records from one model even thought you can join it with other tables.

Thanks for responding. So if I use sqlquery how does that work with the template? It's not quite clear to me. I return a result set? How do I select each field in the returned rows.
Thanks.

Hi G,

It's hard to explain so I'll put it in a code snippet instead.

In page controller:

<?php
        ...
	function getccProjects()
	{
		...
		$sqlQuery = new SQLQuery();
		$sqlQuery->select = array("*");
		$sqlQuery->from = array("ccProjects LEFT JOIN ccImages ON ccProjects.ID = ccImages.ProjectId");
		$sqlQuery->where = array("ccProjects.memberID = $memberID");
		$sqlQuery->orderby = "Sent DESC";
		$sqlQuery->groupby = array("SurveyMember.ID");
	
		$res = $sqlQuery->execute();
		$dos = new DataObjectSet();
		foreach ($res as $record) {
			$dos->push( new DataObject( array("ProjectName"=>$record['ccProjects.Name'], "ImageName"=>$record['ccImages.Name']) ));
		}
		...
		return $dos;
	}
	...
?>

In template:

...
<% control getccProjects %>
	<a href="#">$ProjectName</a> 
	<a href="#">$ImageName</a> 
<% end_control %>
...

I haven't tested it yet so i can't guarantee it works properly but just to give you a sample of how it works. Feel free to leave more comment if it doesn't work.

Thanks for that. I'm not getting any data returned though. The links have blank text.

Try

Debug::show( $sqlQuery->slq() ); 

copy the output sql and run it against the database see if it returns anyways records.