How to calculate long strip using MySQL and two where clauses

Question

This is a continuation of my original question on how to calculate the longest undefeated streak .

I modified the data table to add a “place” column to show whether the game was “home” or “away”:

  date result venue
 -----------------------------
 1980-08-16 WH
 1980-08-19 LA
 1980-08-23 WA
 1980-08-26 WH
 1980-08-30 DH
 and so on ...

I was able to calculate the longest common bands due to my previous question, but now I would like to calculate these bands at the meeting place, and I'm not sure how to do this.

For most home home victories, for example, I tried the following query - but the received “runs” are returned shorter than they actually are (based on manual data counting), and there is no obvious interruption

SQL:

SELECT result, venue, MIN(date) as StartDate, MAX(date) as EndDate, COUNT(*) as Games FROM ( SELECT result, venue, date, ( SELECT COUNT(*) FROM resultengine R WHERE R.result <> RE.result AND (R.venue = 'H') <> (RE.venue = 'H') AND R.date <= RE.date ) as RunGroup FROM resultengine RE ) A WHERE venue = 'H' AND result='W' GROUP BY result, RunGroup ORDER BY Games 

PHP:

 $result = mysql_query( /* the SQL statement from above */ ); while($row=mysql_fetch_assoc($result)) { $startrundate = date("d FY",strtotime($row['StartDate'])); $endrundate = date("d FY",strtotime($row['EndDate'])); echo "<tr>"; echo "<td>".$row['Games']."</td>"; echo "<td class='tableprofile' style='text-align:right;'>".$startrundate." - ".$endrundate."</td>"; echo "</tr>"; $rowCount += 1; } 

It turned out to be more difficult than I imagined, but in the same way I do not think that I am too far from the solution. It’s just bridging the gap between what I have and what I’m not doing right now.

UPDATE

I seem to have solved this specific problem. And it was as simple as changing the sentence (R.venue = 'H') <> (RE.venue = 'H') to R.venue = RE.venue

Answer 1

The following ignores any SQL and assumes that you have a list of "games" and you want to work out the longest strip. In this case, I assumed that the “game” is a class with the wasWon() method.

 $currentStreak = 0; $longestString = 0; foreach($games as $game) { if(!$game->wasWon()) { $currentStreak = 0; continue; } $currentStreak++; if($currentStreak > $longestStreak) { $longestStreak = $currentStreak; } } 

If you want to expand the logic only for home games, you can wrap the whole logic:

 foreach($games as $game) { if($game->wasHomeGame()) { if(!$game->wasWon()) { $currentStreak = 0; continue; } $currentStreak++; if($currentStreak > $longestStreak) { $longestStreak = $currentStreak; } } } 

This would be better placed in a class with more flexibility, for example, you could use a class of values ​​to tell where it was played:

 public function getLongestStreak(array $games, Venue $venue) { $currentStreak = 0; $longestString = 0; foreach($games as $game) { if($game instanceof Game && $game->wasPlayedAt($venue)) { if(!$game->wasWon()) { $currentStreak = 0; continue; } $currentStreak++; if($currentStreak > $longestStreak) { $longestStreak = $currentStreak; } } } return $longestStreak; } 

I could talk about how Value classes can work here, but I'm just distracted from work, and I hope I solved your problem .;)