Combining multiple queries in MySQL

Question

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.

Excellent question setup, but just to verify, you are looking to combine all of the queries listed above into one massive query? Basically you want to eliminate the complex return statement? Or are you needing new/more queries to handle things like fetch records w/in a specific date or records related to a specific user ID? — redhotspike
– redhotspike, Commented Jul 1, 2013 at 16:46
Well, both I suppose. My thinking was, that if the combined COUNT(*) query worked, it would work just as well for fetching information within a specific date. Basically, I want to be able to return notifications.* for a specified user ID within a given time period, or of all time. Does that make sense? — BenM
– BenM, Commented Jul 1, 2013 at 16:52
I have edited the question to give a little more background information and more info on what I'd like to achieve. — BenM
– BenM, Commented Jul 1, 2013 at 17:07

Kickstart · Accepted Answer · 2013-07-01 20:00:40Z

1

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}'

edited Jul 1, 2013 at 20:00

answered Jul 1, 2013 at 16:53

Kickstart

21.5k2 gold badges26 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

14 Comments

BenM Over a year ago

Thanks for your comments. As I said though, I'd rather not use UNION (performance reasons amongst others). In addition, I'm sure this can be achieved by using conditional joins, but I don't have a great working knowledge of them. I know the first three could easily be combined, but would like to do as much of this on the SQL server as possible, while avoiding duplicate data and so forth.

Kickstart Over a year ago

UNION shouldn't really cause a performance issue (for some things it is the best solution to allowing MySQL to use decent indexes). The counts are all fairly separate so difficult to clean up. I have knocked up a query which I am updating my answer with but don't think it will be that efficient.

BenM Over a year ago

Thanks for your help, and the detailed second query. It isn't overly efficient with the real data set. I did some benchmarking with UNION and it seems that perhaps this is indeed the way forward. My problem now (being someone SQL-illiterate) is to create a unioned query that can return all notifications for a specific user within a given time period. Would you be able to point me in the direction of something like that? Once again, I really appreciate your efforts here - SQL gives me a severe headache...!

Kickstart Over a year ago

All notifications are easy, the problem is that the queries you have there are limiting the results in different ways depending on which notifications are required. I have knocked up another query using UNIONs (not tested I am afraid) which I hope will help

Kickstart Over a year ago

Yes, although probably worth adding it as (user_id, action, subject)

|

Collectives™ on Stack Overflow

Combining multiple queries in MySQL

1 Answer 1

14 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

14 Comments

Your Answer

Sign up or log in

Post as a guest

Related