Hi there,

Ok taking my first steps into the world of SS3 (just when i start to get to grips with 2.4) i have a need to list all the items from one table [bScore that is linked to the table [bProject with a many many link, and show all the fields in the table 'Score' and if they have been associated with the table [bProject.
The function below ProjectScore is similar to a query i did a while back in 2.4 and it worked fine, i'm now trying to use similar SQL code and in SS3 it doesnt seem to like it, i get a [Warning] mysqli::query() [mysqli.query]: Empty query message. Am i still able to run this kinda function under SS3 or do i need to tweak, or is there a better way of doing it?

Below is the code and i have attached a picture with what I'm trying to do, each of the boxes are part of table Score, i want to show them all and then also show the ones that are associated with the project(the ones with 'Yes' and in red).

	class Score extends DataObject {
	
	public static $db = array(
		'Title' => 'Varchar',
		'Value' => "Enum('0,1,2,3,4,5','0')",
	);
	public static $belongs_many_many = array(
			'Projects' => 'Project'
	); 

class Project extends DataObject  {
   
 	public static $db = array( 
   		'Title' => 'Varchar()',
		'ShortDescription' => 'Varchar(70)',
		'Description' => 'HTMLText',
		'Status' => "Enum('Active, Completed, Cancelled, Suspended, Standby, Deleted', 'Active')",
		'Type' => "Enum('Client, Internal, Private, Idea, N.A.', 'N.A.')",
		'Priority' => "Enum('A. (High), B. (Medium), C. (Low), N.A.', 'N.A.')",
		'DueDate' => 'Date'	
	);
	public static $has_one = array(
		'Owner' => 'Member',
		'Requester' => 'Member'
		
	);
	public static $has_many = array(
		'Tasks' => 'Task'
	);
	public static $many_many = array(
		'Scores' => 'Score'
	); 
}




public function ProjectScore() {
	
		$sqlQuery = new SQLQuery(); 
		$sqlQuery->selectField = array('*');
		$sqlQuery->setFrom = array("Project LEFT OUTER JOIN Project_scores on Project.ID = Project_scores.ProjectID LEFT JOIN Score on Project_scores.ScoreID = Score.ID ");
		$sqlQuery->setGroupby = ''; 
		$rawSQL = $sqlQuery->sql(); 
		// execute and return a Query-object 
		$result = $sqlQuery->execute(); 
		//setup our blank DataObjectSet to push SQL result data into it. 
		$dos = singleton('Scores')->buildDataObjectSet($result);
		return $dos;  
		
	}

Any help or pointers?

Regards

Mick

Ok i have got a bit further, i know have the data i want but i think i need to get it into a dataobjectset so i can display it nicely grouped in the template, but buildDataObjectSet seems to be depreciated in SS3 my current code is below, anyone able to help me work out how to make it a dataobjectset or have the ability to group the results of the array? The standard GroupedBy(Foo) then a loop Children doesnt seem to work and i'm wondering if that is because this is an Array list? Obviously i could just be doing it wrong.....

public function ProjectScore() { 
		$ProjID = $this->ID;
      $results = DB::query('SELECT  sc.Title AS STitle,  sc.ClassName AS SClassName,
(
  select count(Project.ID) from Project 
  LEFT JOIN Project_scores on Project_scores.ProjectID = Project.ID 
  where sc.ID = Project_scores.ScoreID
  and Project.ID = ' . $ProjID . '
) as Score
FROM Score sc'); 
      $score = ArrayList::create(); 
      for ($i = 0; $i < $results->numRecords(); $i++) { 
   $record = $results->nextRecord(); 
         $score->add(new ArrayData( array('STitle' => $record['STitle'], 'SClassName' => $record['SClassName'],'Score' => $record['Score'] ) )); 
      } 
      
      return $score; 
	  
   }

Cheers

Mick

See http://doc.silverstripe.org/framework/en/howto/grouping-dataobjectsets for how to group by in 3.0.

You should just need to wrap your ArrayList in a GroupedList class to gain groupby properties.

Hi Will,

Thanks for the response, I've looked at that and just haven't been able to make it work. I'm afraid this is where my limitation in understanding kicks in as I'm not really sure how to do that :o(

I shall keep trying till i get the right syntax, I've tried creating a new function but that just throws out a syntax error.

public function getGroupedScores() {
        return GroupedList::create($this->ProjectScore::get()->sort('SClassName'));
    }

Regards

Mick

$this->ProjectScore::get() part doesn't seem right. If you can't rewrite your SQL query into the new ORM [1]. Then use your sql query code to build an ArrayList of data as you had in the post) and then simply return GroupedList::create($arrayList)

[1] http://doc.silverstripe.org/framework/en/topics/datamodel#querying-data

You star! Cheers for that, i have spent ages trying to get that sorted.... Bit of a learning curve for me going from 2.x to 3 but getting there. many thanks for your help much appreciated.

Mick.

Final Code.

public function ProjectScore() { 
		$ProjID = $this->ID;
      $results = DB::query('SELECT  sc.Title AS STitle,  sc.ClassName AS SClassName,
(
  select count(Project.ID) from Project 
  LEFT JOIN Project_scores on Project_scores.ProjectID = Project.ID 
  where sc.ID = Project_scores.ScoreID
  and Project.ID = ' . $ProjID . '
) as Score
FROM Score sc '); 
      $thescore = ArrayList::create(); 
      for ($i = 0; $i < $results->numRecords(); $i++) { 
   $record = $results->nextRecord(); 
         $thescore->add(new ArrayData( array('STitle' => $record['STitle'], 'SClassName' => $record['SClassName'],'Score' => $record['Score'] ) )); 
    	} 
            
           return GroupedList::create($thescore) ;
      
  		}