Jump to:

23490 Posts in 18996 Topics by 2878 members

General Questions

SilverStripe Forums » General Questions » sqlQuery problem in SS3 [solved]

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

Page: 1
Go to End
Author Topic: 737 Views
  • cumquat
    Avatar
    Community Member
    198 Posts

    sqlQuery problem in SS3 [solved] Link to this post

    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
  • cumquat
    Avatar
    Community Member
    198 Posts

    Re: sqlQuery problem in SS3 [solved] Link to this post

    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

  • Willr
    Avatar
    Forum Moderator
    5508 Posts

    Re: sqlQuery problem in SS3 [solved] Link to this post

    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.

  • cumquat
    Avatar
    Community Member
    198 Posts

    Re: sqlQuery problem in SS3 [solved] Link to this post

    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

  • Willr
    Avatar
    Forum Moderator
    5508 Posts

    Re: sqlQuery problem in SS3 [solved] Link to this post

    $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

  • cumquat
    Avatar
    Community Member
    198 Posts

    Re: sqlQuery problem in SS3 [solved] Link to this post

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

          }

    737 Views
Page: 1
Go to Top

Want to know more about the company that brought you SilverStripe? Then check out SilverStripe.com

Comments on this website? Please give feedback.