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.

Data Model Questions

SQL Query Trouble.


Reply

7 Posts   1056 Views

Avatar
Tesla

14 July 2011 at 7:56am Community Member, 9 Posts

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.

Avatar
swaiba

14 July 2011 at 11:26pm Forum Moderator, 1796 Posts

Hi, maybe this might help?

http://www.silverstripe.org/data-model-questions/show/17245#post304677

Avatar
Tesla

15 July 2011 at 4:02am Community Member, 9 Posts

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?

Avatar
swaiba

15 July 2011 at 4:21am (Last edited: 15 July 2011 4:24am), Forum Moderator, 1796 Posts

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

Avatar
Tesla

15 July 2011 at 6:02am (Last edited: 15 July 2011 8:07pm), Community Member, 9 Posts

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.

Avatar
Tesla

15 July 2011 at 8:15pm Community Member, 9 Posts

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

Avatar
swaiba

15 July 2011 at 8:18pm Forum Moderator, 1796 Posts

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.