Jump to:

17452 Posts in 4473 Topics by 1971 members

Archive

SilverStripe Forums » Archive » Joins

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

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

Page: 1
Go to End
Author Topic: 1891 Views
  • g
    Avatar
    Community Member
    22 Posts

    Joins Link to this post

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

  • Phalkunz
    Avatar
    69 Posts

    Re: Joins Link to this post

    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.

  • g
    Avatar
    Community Member
    22 Posts

    Re: Joins Link to this post

    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.

  • Phalkunz
    Avatar
    69 Posts

    Re: Joins Link to this post

    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.

  • g
    Avatar
    Community Member
    22 Posts

    Re: Joins Link to this post

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

  • Phalkunz
    Avatar
    69 Posts

    Re: Joins Link to this post

    Try

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


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

    1891 Views
Page: 1
Go to Top

Want to know more about the company that brought you SilverStripe? Then check out SilverStripe.com

Comments on this website? Please give feedback.