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.

Data Model Questions /

DataObject Groups by linked has_many field


Reply


3 Posts   1803 Views

Avatar
Graphicator

Community Member, 62 Posts

17 September 2011 at 1:08pm

Edited: 17/09/2011 1:13pm

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 [url=http://bit.ly/oNyzR2]Demo here[/url]
-------------------------
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

Avatar
martimiz

Forum Moderator, 1132 Posts

17 September 2011 at 11:19pm

Edited: 17/09/2011 11:20pm

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

Avatar
Graphicator

Community Member, 62 Posts

19 September 2011 at 4:05pm

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.