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.

We've moved the forum!

Please use forum.silverstripe.org for any new questions (announcement).
The forum archive will stick around, but will be read only.

You can also use our Slack channel or StackOverflow to ask for help.
Check out our community overview for more options to contribute.

Data Model Questions /

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

SQL Query Trouble.


Go to End


7 Posts   2290 Views

Avatar
Tesla

Community Member, 9 Posts

14 July 2011 at 7:56am

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

Forum Moderator, 1899 Posts

14 July 2011 at 11:26pm

Avatar
Tesla

Community Member, 9 Posts

15 July 2011 at 4:02am

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

Forum Moderator, 1899 Posts

15 July 2011 at 4:21am

Edited: 15/07/2011 4:24am

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

Community Member, 9 Posts

15 July 2011 at 6:02am

Edited: 15/07/2011 8:07pm

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

Community Member, 9 Posts

15 July 2011 at 8:15pm

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

Forum Moderator, 1899 Posts

15 July 2011 at 8:18pm

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.