Jump to:

3429 Posts in 1057 Topics by 734 members

Data Model Questions

SilverStripe Forums » Data Model Questions » SQL Query Trouble.

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

Page: 1
Go to End
Author Topic: 1015 Views
  • Tesla
    Avatar
    Community Member
    9 Posts

    SQL Query Trouble. Link to this post

    Hi All,

    The following 2 bits of code are part of a much larger function that returns drivers race results and adds them up to get season results. The parts of the function that are not included are working just fine, but I cannot get this to work and I'm hoping for some help.

    This first function works properly it adds up all of the drivers points for the season. No problems here.

    $sqlQuery = new SQLQuery("SUM(Points)", "Result", "ChampionshipID = '" . $this->ID ."' AND DriverID = ".$doDriver->ID);
    $spoint = $sqlQuery->execute()->value();

    For some championships only a certain number of results count towards the final season total. I have created a field on my championship dataobject 'ResultsForChampionship' that holds a numeric value for the number of results that are to be included, and then I pass that value to the query $limit.

    $ResultLimit = $this->ResultsForChampionship;
    $sqlBestQuery = new SQLQuery();
    $sqlBestQuery->select = array('SUM(Points)');
    $sqlBestQuery->from = array('Result');
    $sqlBestQuery->where = array("ChampionshipID = '" . $this->ID ."' AND DriverID = ".$doDriver->ID);
    $sqlBestQuery->orderby = "Points DESC";
    $sqlBestQuery->limit = $ResultLimit;
    $sbestpoint = $sqlBestQuery->execute()->value();

    This function returns the same value as the first function. Am I misunderstanding what $limit means?

    Stumped... Any help would be appreciated.

  • swaiba
    Avatar
    Forum Moderator
    1784 Posts
  • Tesla
    Avatar
    Community Member
    9 Posts

    Re: SQL Query Trouble. Link to this post

    Hi swaiba,

    Thanks for the reply, but I don't understand how that thread could help me.

    I'm beginning to think that the problem is this line.

    $sqlBestQuery->select = array('SUM(Points)');

    I think the query is executing the SUM before it is limiting the results, so there is always just one result, the SUM. If I take out the SUM and then echo each result like so.

    $sqlBestQuery->select = array('Points');

    foreach($result as $row){
    echo($row['Points']);
    }

    I get this.

    array ( 5 => array ( 'Points' => '9', ), 6 => array ( 'Points' => '9', ), 7 => array ( 'Points' => '8', ), 8 => array ( 'Points' => '4', ), )

    The top 4 results. This is the data I need. Is there a way of getting the sum from this?

  • swaiba
    Avatar
    Forum Moderator
    1784 Posts

    Re: SQL Query Trouble. Link to this post

    Hi,

    I've linked to the *post* with GetDataObjectSetFromDBQuery function in it.

    Whenever I know how to do something in SQL like real easy and using the ORM is confusing me - I use that function to get the info and it places it into a DataObjectSet so it can be used in the same way as a DataObject::Get().

    Sorry for the confusion - just trying to help.

    so in your case I'd write the sql like...

    SELECT SUM(Points)
    WHERE ChampionshipID = " . $this->ID ." AND DriverID = ".$doDriver->ID."
    ORDER BY Points DESC
    LIMIT 4

    Barry

  • Tesla
    Avatar
    Community Member
    9 Posts

    Re: SQL Query Trouble. Link to this post

    I appreciate your assistance Barry, but now I'm even more confused. That is exactly what I'm doing and it doesn't work.

    Here is the code again.

    $ResultLimit = $this->ResultsForChampionship;
    $sqlBestQuery = new SQLQuery();
    $sqlBestQuery->select = array('SUM(Points)');
    $sqlBestQuery->from = array('Result');
    $sqlBestQuery->where = array("ChampionshipID = '" . $this->ID ."' AND DriverID = ".$doDriver->ID);
    $sqlBestQuery->orderby = "Points DESC";
    $sqlBestQuery->limit = $ResultLimit;
    $sbestpoint = $sqlBestQuery->execute()->value();

    SELECT SUM(Points)
    FROM Result
    WHERE ChampionshipID = " . $this->ID ." AND DriverID = ".$doDriver->ID."
    ORDER BY Points DESC
    LIMIT 4

    This is returning all of the results and ignoring the limit.

  • Tesla
    Avatar
    Community Member
    9 Posts

    Re: SQL Query Trouble. Link to this post

    I got this sorted and I thought I would post my solution for future reference. I was correct in my earlier post that the sum function was happening before the limit. So it was only returning one record. This is the code that works.

    It was code from another post of your's Barry that got me on the right track, and thanks again for your help. I don't know if this is the best way to do this, but it works.

    $bestpoint = new DataObjectSet();
    $sqlBestQuery = new SQLQuery();
    $sqlBestQuery->select = array('Points');
    $sqlBestQuery->from = array('Result');
    $sqlBestQuery->where = array("ChampionshipID = '" . $this->ID ."' AND DriverID = ".$doDriver->ID);
    $sqlBestQuery->orderby = "Points DESC";
    $sqlBestQuery->limit = $ResultLimit;
    $pointresults = $sqlBestQuery->execute();
    if ($pointresults) {
       foreach ($pointresults as $pointresult) {
          foreach ($pointresult as $key) {
             $bestpoint->push($key);
          }
       }
    }
    $sbestpoint = $bestpoint->toArray();
    $asbestpoint = array_sum($sbestpoint);

  • swaiba
    Avatar
    Forum Moderator
    1784 Posts

    Re: SQL Query Trouble. Link to this post

    Hi, I'm glad that you have your solution. I wouldn't have carried on - if I have a SQL query that works I use it directly because it is much quicker to refine it within a sql cilent IMHO.

    1015 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.