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

Sorting on joined tables


Go to End


2 Posts   1117 Views

Avatar
blackduck

Community Member, 13 Posts

22 October 2016 at 5:12pm

Edited: 24/10/2016 3:56pm

Hi All,
I am having problems with sorting data using a joined table.
The page is here: http://aneala.lochac.sca.org/people/show/321
I want to be able to sort on two fields from the jponed tables. I can do this on a single field with one using ->sort('field ...
but not using ->sort(array('field... which I need to add the second field for sorting.

Thanks for your help :)

This code works:

	public function AwardList() {
		// Combine the awards this person has received with the Award details for display plus the Precedence level to allow sorting.
			return $this->Awards()
			->leftJoin('Award', 'AwardReceived.AwardReceivedID = Award.ID')
			->leftJoin('PrecedenceLevel', 'Award.PrecedenceID=PrecedenceLevel.ID')
			->Sort('PrecedenceLevel.SortOrder ASC');

This doesn't:
	public function AwardList() {
		// Combine the awards this person has received with the Award details for display plus the Precedence level to allow sorting.
			return $this->Awards()
			->leftJoin('Award', 'AwardReceived.AwardReceivedID = Award.ID')
			->leftJoin('PrecedenceLevel', 'Award.PrecedenceID=PrecedenceLevel.ID')
			->Sort(array('PrecedenceLevel.SortOrder'=>'ASC'));

What I want to be able to do is:

	public function AwardList() {
		// Combine the awards this person has received with the Award details for display plus the Precedence level to allow sorting.
			return $this->Awards()
			->leftJoin('Award', 'AwardReceived.AwardReceivedID = Award.ID')
			->leftJoin('PrecedenceLevel', 'Award.PrecedenceID=PrecedenceLevel.ID')
			->Sort(array('PrecedenceLevel.SortOrder'=>'ASC', 'AwardReceived.ReceivedDate'=>'ASC'));

The pieces are:

class Person extends DataObject {
...
	private static $has_many = array(
		'Awards' => 'AwardReceived', //List of awards given to person
	);
...
	public function AwardList() {
		// Combine the awards this person has received with the Award details for display plus the Precedence level to allow sorting.
			return $this->Awards()
			->leftJoin('Award', 'AwardReceived.AwardReceivedID = Award.ID')
			->leftJoin('PrecedenceLevel', 'Award.PrecedenceID=PrecedenceLevel.ID')
			->Sort(array('PrecedenceLevel.SortOrder'=>'ASC'));
	}

 class AwardReceived extends DataObject {
	private static $db = array(
		'ReceivedDate' 	=> 'Date', 	//Date award given
	);

	private static $has_one = array(
		'Person' 		=> 'Person',//Link back to person receiving award
		'AwardReceived'	=> 'Award',	//Award Person has Received
		'GivenBy'		=> 'Reign',	//Link back to Reign for who gave out the award
		'ReceivedEvent'	=> 'Event',	//Link to the event where the award was given
		);

class Award extends DataObject {
	private static $db = array(
		'SortOrder' => 'Int',			//For display order in cms
		'Name'		=> 'Varchar(100)',	//Award Name
		'Abbrev' 	=> 'Varchar(20)',	//Abbreviation
		'Description' => 'HTMLText',
	);

	private static $has_one = array(
		'TokenPic'	=> 'Image',		//Image of award token
		'Group'		=> 'SCAGroup',	//Group this award belongs to
		'Title'		=> 'Title',		//Title conveyed by award. Contains both male and female honorific
		'Precedence' =>	'PrecedenceLevel',	//Ranking amoungst other awards
		'Parent'	=> 'Populace',	//Link back to parent object
	);

Avatar
blackduck

Community Member, 13 Posts

27 October 2016 at 5:53pm

So it seems all I had to do was list both fields int the original sort. It's not how I read the API docs but it's working.

->Sort('PrecedenceLevel.SortOrder,AwardReceived.ReceivedDate', 'ASC');

This doesn't really make sense to me as how to do.

If i'm doing this wrong feel free to let me know.