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.

Archive /

Our old forums are still available as a read-only archive.

Moderators: martimiz, Sean, biapar, Willr, Ingo, simon_w

Joins


Go to End
Reply


6 Posts   1967 Views

Avatar
g

Community Member, 22 Posts

4 November 2008 at 12:29pm

Edited: 04/11/2008 12:30pm

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 %>

Avatar
Phalkunz

69 Posts

4 November 2008 at 3:22pm

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.

Avatar
g

Community Member, 22 Posts

4 November 2008 at 5:53pm

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.

Avatar
Phalkunz

69 Posts

5 November 2008 at 12:22am

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.

Avatar
g

Community Member, 22 Posts

5 November 2008 at 5:27am

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

Avatar
Phalkunz

69 Posts

9 November 2008 at 5:21pm

Try

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

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