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   1932 Views

Avatar
g

4 November 2008 at 12:29pm (Last edited: 4 November 2008 12:30pm), Community Member, 22 Posts

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

4 November 2008 at 3:22pm 69 Posts

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

4 November 2008 at 5:53pm Community Member, 22 Posts

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

5 November 2008 at 12:22am 69 Posts

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

5 November 2008 at 5:27am Community Member, 22 Posts

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

Avatar
Phalkunz

9 November 2008 at 5:21pm 69 Posts

Try

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


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