Need help with mysql query

[sbourdon]

Hello,

 

I am doing my best to learn mysql but I still have a lot to learn when queries involve loops or complex syntax.

Here's what I would like to achieve:

 

Table 1 includes games played since the beginning of the season (where homeTeamScore or awayTeamScore > 0):

 

Table 2 includes predictions made by users as to which team will win each game (gameId in T2 = id in T1):

 

The following query lets me know how many games have been played since the beginning of the season:

        $query = "SELECT COUNT(`id`)
                    FROM  " . SPORTS_BOL_GameDao::getInstance()->getTableName() . "
                    WHERE SeasonID = " . $seasonId . "
                    AND (homeTeamScore > 0 OR awayTeamScore > 0)
                 ORDER BY gametime DESC";

        $totalGames = $this->dbo->queryForColumn($query);

And this one how many predictions each user has registered:

        $query = "SELECT COUNT(`g`.`id`)
                    FROM  " . SPORTS_BOL_GameDao::getInstance()->getTableName() . " g 
                    INNER JOIN " . SPORTS_BOL_PredictionDao::getInstance()->getTableName() . " p
                    ON g.id = p.gameId
                    WHERE g.SeasonID = " . $seasonId . "
                    AND (g.homeTeamScore > 0 OR g.awayTeamScore > 0)
                    AND p.userId = " . $point['userId'] . "
                 ORDER BY g.gametime DESC";

        $totalPreds = $this->dbo->queryForColumn($query);

First question: how could I combine these two queries into one and get the total number of missing predictions for each user?

As an example, if 89 games have been played and user has registered 70 predictions, I would like to get "19" out of the query.

 

Second question, more complex (at least to me!): what would the query look like if I wanted to know how many consecutive games has each user missed since their last prediction?

As an example, if my last prediction was registered on gameId "460" and 4 more games have been played since then (id 468, 469, 470 and 471), I would like to get "4" out of the query.

 

Hope this makes sense and is easy to understand!

Thank you very much for your support!

[Barand]

You would use a subquery to combine EG

SELECT p.userid
  , COUNT(*) as predictions
  , totgames - COUNT(*) as missing
  , totgames
FROM prediction p 
CROSS JOIN (
	SELECT COUNT(*) as totgames FROM games
        ) tot
GROUP BY p.userid

[Barand]

Similarly, for the last part

SELECT 
    userid
  , lastgame - lastprediction as missed
FROM (
	SELECT MAX(id) as lastgame FROM games
	) games
CROSS JOIN (
	SELECT userid
      , MAX(gameid) as lastprediction
	FROM prediction
    GROUP BY userid
    ) users

[sbourdon]

Hello and thank you for your support!

 

Regarding my first question (which is to combine my two queries into one), here's what the new query looks like:

        $query = "SELECT p.userid
                    , COUNT(*) as predictions
                    , totgames - COUNT(*) as missing
                    , totgames
                    FROM " . SPORTS_BOL_PredictionDao::getInstance()->getTableName() . " p
                    CROSS JOIN (
	               SELECT COUNT(*) as totgames
                       FROM " . SPORTS_BOL_GameDao::getInstance()->getTableName() . " g
                       ) tot
                    GROUP BY p.userid
                 ORDER BY g.gametime DESC";

        $missingPreds = " " . $this->dbo->queryForColumn($query) . ")";

Unfortunately, this returns an error 500!

Is there something I haven't done correctly here?

 

Thank you!

[sbourdon]

By the way, this query is being executed inside a

foreach ($points as $point) {
(...)
}

where

p.userId = " . $point['userId'] . "

Therefore, since we already have "p.userId", what should the query look like to get this user's missing predictions?

 

I can get the number of missing predictions by doing the following:

$missingPreds = $totalGames - $totalPreds; 

but I guess I could achieve the same thing in one query, right?

 

 

Again, thank you for your time!

Edited June 9, 2016 by sbourdon

[Psycho]

By the way, this query is being executed inside a

foreach ($points as $point) {
(...)
}

 

And why is that?  You should never run queries in loops as they are expensive from a performance aspect and can bring your site to a crawl based on the amount of data and usage.

 

In this case, you should instead run ONE query for all the applicable user IDs in your list. You are already grouping by the user ID anyway. Plus, the currently query doesn't even use the user ID, so each iteration of the loop is apparently running the exact same query.

[sbourdon]

And why is that?  You should never run queries in loops as they are expensive from a performance aspect and can bring your site to a crawl based on the amount of data and usage.

 

In this case, you should instead run ONE query for all the applicable user IDs in your list. You are already grouping by the user ID anyway. Plus, the currently query doesn't even use the user ID, so each iteration of the loop is apparently running the exact same query.

 

Thanks for the info; I didn't know about that!

So I guess we're back at post #4!

Anything wrong with that query?

 

Thank you!

[Barand]

Since the query is aggregating totals, individual gametimes are irrelevant, so why the useless ORDER BY?

 

You should also note that the data is currently not limited to a specific season.

 

I don't know what your method queryForColumn() returns but this looks a little odd to me

$missingPreds = " " . $this->dbo->queryForColumn($query) . ")";

[sbourdon]

Thank you, I'll look into this!