CODEWITHSUNDEEP
mysqlsql
BenM
BenM

Reputation: 53246

Combining multiple queries in MySQL

This is a bit of a long-winded question, and I apologize for that. My SQL skills are severely lacking (something I'd like to redress ASAP). As a result, I really cannot fathom how I can tackle this problem that I have run into.

Basically, our project is storing user notifications in a single table notifications. The table structure looks like this:

+-----------+-----------+----------------+------------+
|  user_id  |  subject  |     action     |     date   |
+-----------+-----------+----------------+------------+
|     1     |     2     | started_follow | 1371034287 |
|     1     |     2     | stopped_follow | 1371034287 |
|     2     |     5     |   added_item   | 1371034287 |
+-----------+-----------+----------------+------------+

user_id always contains the ID of the user that performed the action, and date is obviously the date that the notification was registered. The tricky part is that subject is a reference to an ID of another table, and that table is largely dependent upon the value of the action column.

So for example, in the first two records of the sample data, subject is a reference to an ID in the users table (i.e. the user who was followed, and then unfollowed). In the third record, subject is a reference to an ID in the items table.

We also need to perform several JOIN statements, depending upon the condition of action. So, if it's added_item for example, we need to JOIN several other tables (to check for settings and other requirements).

I came across a legacy function in the code which essentially checks to see how many notifications exist in the table for a given user since a specified date. The previous developer has simply used a series of queries, and then returned the total number of several SELECT COUNT(*) statements as follows (please note, this is all inside the PHP User class):

// Get the number of notifications since the specified time (or of all time):
public function countNotifications($since = '')
{
    $sinceString = ($since == '') ? '' : "AND `date` > '$since'";

    // Notifications when someone follows $this:
    $started_following = $this->_database->query("SELECT COUNT(*) AS `count`
                                                  FROM `notifications`
                                                  WHERE `action` = 'started_following'
                                                    AND `subject` = '{$this->id}'
                                                    $sinceString
                                                    AND `notifications`.`user_id` != '{$this->id}'")->fetchObject();

    // Notifications when someone stops following $this:
    $stopped_following = $this->_database->query("SELECT COUNT(*) AS `count`
                                                  FROM `notifications`
                                                  WHERE `action` = 'stopped_following'
                                                    AND `subject` = '{$this->id}'
                                                    $sinceString
                                                    AND `notifications`.`user_id` != '{$this->id}'")->fetchObject();

    // Notifications when someone sends $this a message:
    $sent_message = $this->_database->query("SELECT COUNT(*) AS `count`
                                             FROM `notifications`
                                             WHERE `action` = 'sent_message'
                                               AND `subject` = '{$this->id}'
                                               $sinceString
                                               AND `notifications`.`user_id` != '{$this->id}'")->fetchObject();

    // Notifications when someone favorites $this' items:
    $favorited_item = $this->_database->query("SELECT COUNT(*) AS `count`
                                               FROM `notifications`
                                               INNER JOIN `items` ON `notifications`.`subject` = `items`.`id`
                                               INNER JOIN `categories` ON `items`.`category` = `categories`.`id`
                                               WHERE `notifications`.`action` = 'favorited_item'
                                                 AND `categories`.`owner` = '{$this->id}'
                                                 $sinceString
                                                 AND `notifications`.`user_id` != '{$this->id}'")->fetchObject();

    // Notifications when someone adds a comment to $this' items:
    $comments = $this->_database->query("SELECT COUNT(*) AS `count`
                                         FROM `notifications`
                                         INNER JOIN `items` ON `notifications`.`subject` = `items`.`id`
                                         INNER JOIN `categories` ON `items`.`category` = `categories`.`id`
                                         WHERE `notifications`.`action` = 'added_comment'
                                           AND `categories`.`owner` = '{$this->id}'
                                           $sinceString
                                           AND `notifications`.`user_id` != '{$this->id}'")->fetchObject();

    // Notifications when a follower of $this adds a new item:
    $new_items = $this->_database->query("SELECT COUNT(*) AS `total`
                                         FROM `notifications`
                                         INNER JOIN `categories` ON `notifications`.`subject` = `categories`.`id`
                                         INNER JOIN (SELECT `followee` FROM `user_followers` WHERE `follower` = '{$this->id}') `followers`
                                         WHERE `notifications`.`action` = 'added_item'
                                           AND `followee` = `user_id`
                                           $sinceString
                                           AND `user_id` !=  '{$this->id}'")->fetchObject();

    // Notifications when a follower of $this adds a new collection:
    $new_collections = $this->_database->query("SELECT COUNT(*) AS `total`
                                                FROM `notifications`
                                                INNER JOIN `categories` ON `notifications`.`subject` = `categories`.`id`
                                                INNER JOIN (SELECT `followee` FROM `user_followers` WHERE `follower` = '{$this->id}') `followers`
                                                WHERE `notifications`.`action` = 'added-collection'
                                                   AND `followee` = `user_id`
                                                   $sinceString
                                                   AND `user_id` !=  '{$this->id}'")->fetchObject();

    // Notifications when a follower of $this adds a new category:
    $new_categories = $this->_database->query("SELECT COUNT(*) AS `total`
                                               FROM `notifications`
                                               INNER JOIN  `categories` ON `notifications`.`subject` =  `categories`.`id`
                                               INNER JOIN (SELECT `followee` FROM `user_followers` WHERE `follower` = '{$this->id}') `followers`
                                               WHERE `notifications`.`action` =  'added-category'
                                                 AND `followee` = `user_id`
                                                 $sinceString
                                                 AND `user_id` !=  '{$this->id}'")->fetchObject();

    // Selling Notifications:
    // Notifications when someone makes an offer for an item $this is selling:
    $offers = $this->_database->query("SELECT COUNT(*) AS `count`
                                       FROM `notifications`
                                       INNER JOIN `items` ON `notifications`.`subject` = `items`.`id`
                                       INNER JOIN `categories` ON `items`.`category` = `categories`.`id`
                                       WHERE `notifications`.`action` = 'made_offer'
                                         AND `categories`.`owner` = '{$this->id}'
                                         $sinceString
                                         AND `notifications`.`user_id` != '{$this->id}'")->fetchObject();

    // Notifications when someone purchases an item $this is selling:
    $purchases = $this->_database->query("SELECT COUNT(*) AS `count`
                                          FROM `notifications`
                                          INNER JOIN `items` ON `notifications`.`subject` = `items`.`id`
                                          INNER JOIN `categories` ON `items`.`category` = `categories`.`id`
                                          INNER JOIN (SELECT COUNT(*) AS `count`, `item_id`
                                                      FROM `user_favorite_items`
                                                      WHERE `user_id` = '{$this->id}') `following` ON `items`.`id` = `following`.`item_id`
                                          WHERE `notifications`.`action` = 'bought_item'
                                            AND `following`.`count` = 1
                                            AND `categories`.`owner` = '{$this->id}'
                                            $sinceString
                                            AND `notifications`.`user_id` != '{$this->id}'")->fetchObject();

    // Notifications when an item that $this favorited is listed for sale:
    $item_sales = $this->_database->query("SELECT COUNT(*) AS `count`
                                           FROM `notifications`
                                           INNER JOIN `items` ON `notifications`.`subject` = `items`.`id`
                                           INNER JOIN (SELECT COUNT(*) AS `count`, `item_id`
                                                       FROM `user_favorite_items`
                                                       WHERE `user_id` = '{$this->id}'
                                                    ) `following` ON `items`.`id` = `following`.`item_id`
                                           WHERE `notifications`.`action` = 'selling_item'
                                             AND `following`.`count` = 1
                                             $sinceString
                                             AND `notifications`.`user_id` != '{$this->id}'")->fetchObject();

    // Return the counts:
    return ($started_following->count + 
            $stopped_following->count +
            $sent_message->count +
            $favorited_item->count +
            $comments->count +
            $new_items->count +
            $new_collections->count +
            $new_categories->count +
            $offers->count +
            $purchases->count +
            $item_sales->count);
}

While this does the job perfectly well, it makes it extremely difficult to fetch all records from a specified date, for example, or all records that relate to a specific User ID.

I suppose my question really is what is the best way to combine the numerous SQL statements provided? I have experimented with LEFT JOIN, but as you can see we need to join the table to a different column, depending upon the value of notifications.action. While I can do this using table aliases, it does tend to return a lot of redundant data.

Essentially, I'd like to combine the COUNT(*) queries given above so that we can simply return all notifications.* for a given User ID and / or time period.

I'd also like to avoid using UNION if at all possible (for obvious reasons).

Sorry for the lengthy question, but I've tried to make everything as clear as possible. Before anyone asks, I'm unable to change the structure of the data or the DB schema, as this is for an existing site.

I have put together an SQLFiddle to make things a little more clear.

Upvotes: 2

Views: 229

Answers (1)

Kickstart
Kickstart

Reputation: 21533

Those queries vary in complexity and what they are doing.

The first 3 could be combined into a single query returning (up to) 3 rows:-

SELECT action, COUNT(*) AS `count`
  FROM `notifications`
  WHERE `action` IN ( 'started_following', 'stopped_following', 'sent_message')
    AND `subject` = '{$this->id}'
    $sinceString
    AND `notifications`.`user_id` != '{$this->id}'
GROUP BY action

To force that to return 3 rows you could possibly do something like this:-

SELECT WantedActions.WantedAction, COUNT(*) AS `count`
  FROM (SELECT 'started_following' AS WantedAction UNION SELECT 'stopped_following' UNION SELECT 'sent_message') AS WantedActions
  LEFT OUTER JOIN `notifications`
  ON WantedActions.WantedAction = notifications.action
  WHERE `subject` = '{$this->id}'
    $sinceString
    AND `notifications`.`user_id` != '{$this->id}'
GROUP BY WantedActions.WantedAction

You can probably do similar things for others

EDIT

SELECT started_following, stopped_following, sent_message, favorited_item, comments, new_items, new_collections, new_categories, offers, purchases, item_sales
FROM (SELECT SUM(IF(`action` = 'started_following', 1, 0) AS started_following, 
                    SUM(IF(`action` = 'stopped_following', 1, 0) AS stopped_following, 
                    SUM(IF(`action` = 'sent_message', 1, 0) AS sent_message
            FROM `notifications`
            WHERE `subject` = '{$this->id}'
            $sinceString
            AND `notifications`.`user_id` != '{$this->id}') Sub1
CROSS JOIN (SELECT SUM(IF(`action` = 'favorited_item', 1, 0) AS favorited_item, 
                    SUM(IF(`action` = 'added_comment', 1, 0) AS comments, 
                    SUM(IF(`action` = 'made_offer', 1, 0) AS offers
            FROM `notifications`
            INNER JOIN `items` ON `notifications`.`subject` = `items`.`id`
            INNER JOIN `categories` ON `items`.`category` = `categories`.`id`
            WHERE `categories`.`owner` = '{$this->id}'
            $sinceString
            AND `notifications`.`user_id` != '{$this->id}') Sub2
CROSS JOIN (SELECT SUM(IF(`action` = 'added_item', 1, 0) AS new_items, 
                    SUM(IF(`action` = 'added-collection', 1, 0) AS new_collections, 
                    SUM(IF(`action` = 'added-category', 1, 0) AS new_categories
            FROM `notifications`
            INNER JOIN `categories` ON `notifications`.`subject` = `categories`.`id`
            INNER JOIN (SELECT `followee` FROM `user_followers` WHERE `follower` = '{$this->id}') `followers`
            WHERE `followee` = `user_id`
            $sinceString
            AND `user_id` !=  '{$this->id}') Sub3
CROSS JOIN (SELECT COUNT(*) AS purchases
          FROM `notifications`
          INNER JOIN `items` ON `notifications`.`subject` = `items`.`id`
          INNER JOIN `categories` ON `items`.`category` = `categories`.`id`
          INNER JOIN (SELECT COUNT(*) AS `count`, `item_id`
                      FROM `user_favorite_items`
                      WHERE `user_id` = '{$this->id}'
                    ) `following` ON `items`.`id` = `following`.`item_id`
          WHERE `notifications`.`action` = 'bought_item'
            AND `following`.`count` = 1
            AND `categories`.`owner` = '{$this->id}'
            $sinceString
            AND `notifications`.`user_id` != '{$this->id}') Sub4
CROSS JOIN (SELECT COUNT(*) AS item_sales
           FROM `notifications`
           INNER JOIN `items` ON `notifications`.`subject` = `items`.`id`
           INNER JOIN (SELECT COUNT(*) AS `count`, `item_id`
                       FROM `user_favorite_items`
                       WHERE `user_id` = '{$this->id}'
                    ) `following` ON `items`.`id` = `following`.`item_id`
           WHERE `notifications`.`action` = 'selling_item'
             AND `following`.`count` = 1
             $sinceString
             AND `notifications`.`user_id` != '{$this->id}') Sub5

EDIT - Using a union

SELECT action, COUNT(*)  AS action_count
            FROM `notifications`
            WHERE `subject` = '{$this->id}'
            AND action IN ('started_following', 'stopped_following', 'sent_message')
            $sinceString
            AND `notifications`.`user_id` != '{$this->id}'
            GROUP BY action
UNION
SELECT action, COUNT(*)  AS action_count
            FROM `notifications`
            INNER JOIN `items` ON `notifications`.`subject` = `items`.`id`
            INNER JOIN `categories` ON `items`.`category` = `categories`.`id`
            WHERE `categories`.`owner` = '{$this->id}'
            AND action IN ('favorited_item', 'added_comment', 'made_offer')
            $sinceString
            AND `notifications`.`user_id` != '{$this->id}'
            GROUP BY action
UNION
SELECT action, COUNT(*)  AS action_count
            FROM `notifications`
            INNER JOIN `categories` ON `notifications`.`subject` = `categories`.`id`
            INNER JOIN (SELECT `followee` FROM `user_followers` WHERE `follower` = '{$this->id}') `followers`
            WHERE `followee` = `user_id`
            AND action IN ('added_item', 'added-collection', 'added-category')
            $sinceString
            AND `user_id` !=  '{$this->id}'
            GROUP BY action
UNION
SELECT 'purchases' AS action, COUNT(*)  AS action_count
          FROM `notifications`
          INNER JOIN `items` ON `notifications`.`subject` = `items`.`id`
          INNER JOIN `categories` ON `items`.`category` = `categories`.`id`
           INNER JOIN (SELECT user_id, COUNT(*) AS `count`, `item_id`
                       FROM `user_favorite_items`
                       GROUP BY `user_id`, item_id
                    ) `following` ON `items`.`id` = `following`.`item_id` AND `notifications`.`user_id` = `following`.`user_id`
          WHERE `notifications`.`action` = 'bought_item'
            AND `following`.`count` = 1
            AND `categories`.`owner` = '{$this->id}'
            $sinceString
            AND `notifications`.`user_id` != '{$this->id}'
UNION
SELECT 'item_sales' AS action, COUNT(*) AS action_count
           FROM `notifications`
           INNER JOIN `items` ON `notifications`.`subject` = `items`.`id`
           INNER JOIN (SELECT user_id, COUNT(*) AS `count`, `item_id`
                       FROM `user_favorite_items`
                       GROUP BY `user_id`, item_id
                    ) `following` ON `items`.`id` = `following`.`item_id` AND `notifications`.`user_id` = `following`.`user_id`
           WHERE `notifications`.`action` = 'selling_item'
             AND `following`.`count` = 1
             $sinceString
             AND `notifications`.`user_id` != '{$this->id}'

Upvotes: 1

Related Questions