Jump to:

3430 Posts in 1057 Topics by 734 members

Data Model Questions

SilverStripe Forums » Data Model Questions » DataObject Groups by linked has_many field

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

Page: 1
Go to End
Author Topic: 1671 Views
  • Graphicator
    Avatar
    Community Member
    62 Posts

    DataObject Groups by linked has_many field Link to this post

    I've been having a tough time finding an easier way to pull distinct items off a has_many inside my DObject, and then listing all of the DObjects inside. I have formed together a foreach statement, dataobjectset, and sqlquery.

    Expected Results Demo here
    -------------------------
    Group Name
    Client 1 · Client 2
    Group Name
    Client 3
    Group Name
    Client Client Client

    The submitted code works as it should, however...
    The reason for this post is to share what has worked for myself, and finding out if there was an easier way for myself, and others.

    My Models Look Like

    class Client extends DataObject {
       static $has_one = array(
          'ClientGroup' => 'ClientGroup'
    }
    }

    ClientGroup.php

    class ClientGroup extends DataObject {
       static $db = array(
          'Title' => 'Varchar(255)',
          'Nice' => 'Varchar(255)'
       );
       static $has_many = array(
          'Clients' => 'Client'
       );
    }

    The full Query function

          public function DisplayClientGroups(){
             $sqlQuery = new SQLQuery();
             $sqlQuery->select = array(
                 'clientgroup.Title AS ClientGroup',
                 'clientgroup.ID AS ClientGroupID',
             );

             $sqlQuery->from = array("clientgroup");
             $sqlQuery->where = "";
             $sqlQuery->distinct = true;
             
             $rawSQL = $sqlQuery->sql();
             
             // execute and return a Query-object
             $result = $sqlQuery->execute();
             
             $doSet = new DataObjectSet();
                   
             foreach($result as $row) {
                   $row['Group'] = $row['ClientGroup'];
                   $row['ID'] = $row['ClientGroupID'];
                      $getID = $row['ClientGroupID'];
                   $row['Clients'] = DataObject::get("Client","ClientGroupID = '$getID'");
                   
                   $doSet->push(new ArrayData($row));
                }
             
                return $doSet;
          }

    The following is controlled on the template with

       <% control DisplayClientGroups %>
          <h1>$Group</h1>
          <ul class='client'>
          <% control Clients %>
             <li>$Name <% if Location %>($Location)<% end_if %></li>
             <% if Last %><% else %><li>&middot;</li><% end_if %>
          <% end_control %>
          </ul>
       <% end_control %>

    And it works how it should. I would've like to see enumValues() work, but I kept getting a Varchar errors.
    My method was discovered from the following link I found
    http://tybarho.com/blog/posts/archives-dataobjectset-and-sqlquery

  • martimiz
    Avatar
    Forum Moderator
    1067 Posts

    Re: DataObject Groups by linked has_many field Link to this post

    To get a distinct query from a DataTable you do need to use the SQLQuery object, you're right there. There is an easier way to convert it to a DataObjectSet:

    $result = $sqlQuery->execute();
    $doSet = singleton('ClientGroup')->buildDataObjectSet($result);

    Unfortunately in your setup this won't work since you're working with field aliases. I wonder why really..? Also why would you need a distinct query on on ClientGroups, when you are in fact selecting on ID, that is always unique anyway? Would something elementary like this not work for you:

    public function DisplayClientGroups(){
       return DataObject::get('ClientGroup');
    }

    <% control DisplayClientGroups %>
       <h1>$Title</h1>
       <ul class='client'>
       <% control Clients %>
          <li>$Name <% if Location %>($Location)<% end_if %></li>
          <% if Last %><% else %><li>&middot;</li><% end_if %>
       <% end_control %>
       </ul>
    <% end_control %>

  • Graphicator
    Avatar
    Community Member
    62 Posts

    Re: DataObject Groups by linked has_many field Link to this post

    martimiz, your solution is the simplistic answer I needed for my problem. I thank you for your quick reply. And you were right about distinct not being needed.

    1671 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.