Jump to content

Need help with mysql query


Recommended Posts

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

962d4d62ca.png

 

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

9634b66830.png

 

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!

Link to post
Share on other sites

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
Link to post
Share on other sites

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
Link to post
Share on other sites

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!

Link to post
Share on other sites

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 by sbourdon
Link to post
Share on other sites

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.

Link to post
Share on other sites

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!

Link to post
Share on other sites

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) . ")";
Link to post
Share on other sites
This thread is more than a year old.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.