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 /

Retrieving a list of distinct entries from a DataObject


Reply


6 Posts   2171 Views

Avatar
nekranox

Community Member, 31 Posts

30 June 2010 at 3:11am

Hey guys,

I have a DataObject called "Job" here's what it looks like:

   static $db = array(
      'Title' => 'Varchar(24)',
      'Description' => 'Text',
      'HoursPerWeek' => 'Text',
      'Location' => 'Varchar(24)',
      'RequiredRoutewayCourse' => 'Text',
      'HourlyWageOrSalary' => 'Currency',
      'ExpiryDate' => 'Date',
   );

In "JobCategory.php" which is a page that lists all of my jobs, I would like to retrieve a list of all unique "RequiredRoutewayCourse" entries from the Job table in the database.

What is the best way to do this?

Thanks!

Avatar
nekranox

Community Member, 31 Posts

1 July 2010 at 12:04am

sorry guys but bumping this one because i really cant find the solution anywhere else. if i haven't supplied enough information then please let me know.

Avatar
Matty Balaam

Community Member, 69 Posts

1 July 2010 at 1:27am

Not sure if it is the same, but does this help?

http://silverstripe.org/dataobjectmanager-module-forum/show/262250#post262250

Avatar
banal

Community Member, 901 Posts

1 July 2010 at 3:39am

You could always use the SQLQuery Class to build your own SQL query for that purpose.
Something along these lines:

$sqlQuery = new SQLQuery();
$sqlQuery->select = array('DISTINCT RequiredRoutewayCourse');
$sqlQuery->from = array('Job');
$sqlQuery->where = array($this->ClassName . 'ID = '. $this->ID);
$result = $sqlQuery->execute();
foreach($result as $row){
   // do something meaningful with the data... not just print
   print($row['RequiredRoutewayCourse']);
}

Avatar
TotalNet

Community Member, 181 Posts

1 July 2010 at 9:59am

Is RequiredRoutewayCourse unique for each record or is there a many-to-one relationship there?

I'm guessing there is otherwise you could just list all entries, have you considered making RequiredRoutewayCourse a DataObject itself? Then you could set up the relationship and just look at the RequiredRoutewayCourse DataObject for your answer, of course, that will affect the Admin use-case.

Just another way of approaching it.

Rich

Avatar
nekranox

Community Member, 31 Posts

1 July 2010 at 8:04pm

thanks guys,

TotalNet is right that I should make RequiredRoutewayCourse a seperate dataObject of its own with a many-to-one relationship. That would be the most sensible way of modeling the data.

In this case though I have just used SQLQuery to select all the distinct values from Job and then constructed a dataSet object to pass to a template controller.