17488 Posts in 4473 Topics by 1978 members
|
Page:
1
|
Go to End | |
| Author | Topic: | 1823 Views |
-
Joins
4 November 2008 at 12:29pm Last edited: 4 November 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 %> -
Re: Joins
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.
-
Re: Joins
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. -
Re: Joins
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.
-
Re: Joins
5 November 2008 at 5:27am
Thanks for that. I'm not getting any data returned though. The links have blank text.
-
Re: Joins
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.
| 1823 Views | ||
|
Page:
1
|
Go to Top |
