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.

General Questions /

General questions about getting started with SilverStripe that don't fit in any of the categories above.

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

sqlQuery problem in SS3 [solved]


Reply


6 Posts   803 Views

Avatar
cumquat

Community Member, 198 Posts

5 September 2012 at 10:36pm

Edited: 06/09/2012 1:49am

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

Attached Files
Avatar
cumquat

Community Member, 198 Posts

7 September 2012 at 1:05am

Edited: 07/09/2012 7:43pm

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

Avatar
Willr

Forum Moderator, 5513 Posts

7 September 2012 at 10:09pm

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.

Avatar
cumquat

Community Member, 198 Posts

7 September 2012 at 10:47pm

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

Avatar
Willr

Forum Moderator, 5513 Posts

7 September 2012 at 11:16pm

$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

Avatar
cumquat

Community Member, 198 Posts

7 September 2012 at 11:40pm

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) ;

      }