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.

We've moved the forum!

Please use forum.silverstripe.org for any new questions (announcement).
The forum archive will stick around, but will be read only.

You can also use our Slack channel or StackOverflow to ask for help.
Check out our community overview for more options to contribute.

Data Model Questions /

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

Retrieving a list of distinct entries from a DataObject


Go to End


6 Posts   4392 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, 74 Posts

1 July 2010 at 1:27am

Avatar
bummzack

Community Member, 904 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.