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


Reply


6 Posts   1962 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.