0

I'm coming with a question about merging two data sets.

I am using STI (single table inheritance) in the products table, so one column defines what type of product the specific row stores.

The products table stores multiple types (there are more types than I've presented below): - Vod - Series - Seasons

Let's imagine that you are viewing the action category of the Netflix vod library. You can see a mixed type of products (Vod, Series). The first thought is to just fetch all products for the category, but there are a couple of rules.

Vod can be displayed when: - there is content for a specific device. By specific device, I mean Android, iOS. The device has a profile so I skipped device in the schema - belongs to a specific category - any valid offer(you can buy the offer, not a vod directly) contains that vod OR you have entitlement for that offer (offer is no longer available, but your entitlement for the offer expires later)

Series can be displayed when: - belongs to category - has at least one episode(is a vod with season_id). The episode inherits all vod rules with one exception. The Episode does not have any category.

The data source can be sorted by product name.

I need to return the: - a total number of products that meet the requirements I've described before. - the paginated list of products and sorted by name

The steps I'm doing right now is: - to get all products that belong to category. This query gives me all ids, so I can do two operations (get the number of products and I can do pagination on them). - Based on the first query I've got all ids I need to execute the second query.

I'm not sure that my approach is right, so I would like to ask more experienced people like you to point me some hints how can I make those queries more elegant and more performant.


-- the First query is responsible for getting ids that will be used in the next query
SELECT  id, name, type
FROM (
 (
      SELECT products.id,
             products.name,
             products.type
        FROM products
        JOIN contents
          ON contents.product_id = products.id AND contents.profile_id = 1
        JOIN categories_products
          ON categories_products.product_id = products.id
        JOIN categories
          ON categories.id = categories_products.category_id
        JOIN offers_products
          ON products.id = offers_products.product_id
        JOIN offers
          ON offers_products.offer_id = offers.id
   LEFT JOIN entitlements FORCE INDEX(index_entitlements_on_offer_id_and_customer_id_and_expires_at)
          ON entitlements.offer_id = offers.id AND entitlements.customer_id = 1 AND entitlements.expires_at > UTC_TIMESTAMP()
       WHERE  products.type = 'Vod'
         AND ((Coalesce(offers.available_till > UTC_TIMESTAMP(), true) AND Coalesce(offers.available_from < UTC_TIMESTAMP(), true) AND offers.status = 'available') OR entitlements.customer_id = 1)
         AND (categories.id = 1)
    GROUP BY products.id
    ORDER BY products.name
  )
    UNION ALL
  (
    SELECT tv_series.id,
           tv_series.name,
           tv_series.type
      FROM products tv_series
      JOIN categories_products ON categories_products.product_id = tv_series.id
      JOIN categories ON categories.id = categories_products.category_id
     WHERE tv_series.type = 'Series'
       AND categories.id = 1
       AND EXISTS (
            SELECT 1
              FROM products episodes
              JOIN products seasons
                ON episodes.season_id = seasons.id
              JOIN offers_products
                ON episodes.id = offers_products.product_id
              JOIN offers
                ON offers_products.offer_id = offers.id
              JOIN contents
                ON contents.product_id = episodes.id AND contents.profile_id = 1 AND contents.activated = 1
        LEFT JOIN entitlements FORCE INDEX(index_entitlements_on_offer_id_and_customer_id_and_expires_at)
                ON entitlements.offer_id = offers.id AND entitlements.customer_id = 1 AND entitlements.expires_at > UTC_TIMESTAMP()
             WHERE seasons.series_id = tv_series.id
               AND episodes.type = 'Vod'
               AND ((Coalesce(offers.available_till > UTC_TIMESTAMP(), true) AND Coalesce(offers.available_from < UTC_TIMESTAMP(), true) AND offers.status = 'available') OR entitlements.customer_id = 1)
             LIMIT 1
           )
    GROUP BY tv_series.id
    ORDER BY tv_series.name
  )
) as mixed_products
ORDER BY mixed_products.name asc
LIMIT 0, 3;



-- The second query knows what ids should be fetched. The additional offer_name column is just an example to show that I need to group by other column.

SELECT  id, name, type, offer_name
FROM (
 (
      SELECT products.id,
             products.name,
             products.type,
             offers.name as offer_name
        FROM products
        JOIN contents
          ON contents.product_id = products.id AND contents.profile_id = 1
        JOIN categories_products
          ON categories_products.product_id = products.id
        JOIN categories
          ON categories.id = categories_products.category_id
        JOIN offers_products
          ON products.id = offers_products.product_id
        JOIN offers
          ON offers_products.offer_id = offers.id
   LEFT JOIN entitlements FORCE INDEX(index_entitlements_on_offer_id_and_customer_id_and_expires_at)
          ON entitlements.offer_id = offers.id AND entitlements.customer_id = 1 AND entitlements.expires_at > UTC_TIMESTAMP()
       WHERE  products.type = 'Vod'
         AND ((Coalesce(offers.available_till > UTC_TIMESTAMP(), true) AND Coalesce(offers.available_from < UTC_TIMESTAMP(), true) AND offers.status = 'available') OR entitlements.customer_id = 1)
         AND (categories.id = 1)
    GROUP BY products.id, offers.name
    ORDER BY products.name
  )
    UNION ALL
  (
    SELECT tv_series.id,
           tv_series.name,
           tv_series.type,
           NULL as offer_name
      FROM products tv_series
      JOIN categories_products ON categories_products.product_id = tv_series.id
      JOIN categories ON categories.id = categories_products.category_id
     WHERE tv_series.type = 'Series'
       AND categories.id = 1
       AND EXISTS (
            SELECT 1
              FROM products episodes
              JOIN products seasons
                ON episodes.season_id = seasons.id
              JOIN offers_products
                ON episodes.id = offers_products.product_id
              JOIN offers
                ON offers_products.offer_id = offers.id
              JOIN contents
                ON contents.product_id = episodes.id AND contents.profile_id = 1 AND contents.activated = 1
        LEFT JOIN entitlements FORCE INDEX(index_entitlements_on_offer_id_and_customer_id_and_expires_at)
                ON entitlements.offer_id = offers.id AND entitlements.customer_id = 1 AND entitlements.expires_at > UTC_TIMESTAMP()
             WHERE seasons.series_id = tv_series.id
               AND episodes.type = 'Vod'
               AND ((Coalesce(offers.available_till > UTC_TIMESTAMP(), true) AND Coalesce(offers.available_from < UTC_TIMESTAMP(), true) AND offers.status = 'available') OR entitlements.customer_id = 1)
             LIMIT 1
           )
    GROUP BY tv_series.id
    ORDER BY tv_series.name
  )
) as mixed_products
WHERE id IN (2,6,9)
ORDER BY mixed_products.name asc;

I've prepared also sql fiddle. http://sqlfiddle.com/#!9/43283e/4

Below you can find schema (The schema don't present constraints, but in the real application, they exists)

CREATE TABLE if not exists `products` (
  `id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `type` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
  `total_seasons` tinyint(4) DEFAULT '0',
  `total_episodes` smallint(6) DEFAULT '0',
  `season_number` int(11) DEFAULT NULL,
  `episode_number` int(11) DEFAULT NULL,
  `series_id` int(11) DEFAULT NULL,
  `season_id` int(11) DEFAULT NULL,
  KEY `index_products_on_name` (`name`) USING BTREE,
  KEY `index_products_on_type` (`type`) USING BTREE,
  KEY `index_products_on_series_id` (`series_id`) USING BTREE,
  KEY `index_products_on_season_id` (`season_id`) USING BTREE
) ENGINE=InnoDB;

CREATE TABLE if not exists `offers_products` (
  `offer_id` int(11) NOT NULL,
  `product_id` int(11) NOT NULL,
  UNIQUE KEY `index_offers_products_on_offer_id_and_product_id` (`offer_id`,`product_id`) USING BTREE,
  KEY `index_offers_products_on_product_id` (`product_id`) USING BTREE
) ENGINE=InnoDB;

CREATE TABLE if not exists `offers` (
  `id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `name` varchar(64) NOT NULL,
  `price` decimal(10,2) NOT NULL,
  `status` varchar(16) NOT NULL,
  `available_from` datetime DEFAULT NULL,
  `available_till` datetime DEFAULT NULL,
  KEY `index_offers_on_name` (`name`) USING BTREE,
  KEY `index_offers_on_status` (`status`) USING BTREE
) ENGINE=InnoDB;

CREATE TABLE if not exists `entitlements` (
  `id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `customer_id` int(11) NOT NULL,
  `offer_id` int(11) NOT NULL,
  `expires_at` datetime NOT NULL,
  KEY `index_entitlements_on_customer_id` (`customer_id`) USING BTREE,
  KEY `index_entitlements_on_expires_at` (`expires_at`) USING BTREE,
  KEY `index_entitlements_on_offer_id_and_customer_id_and_expires_at` (`offer_id`,`customer_id`,`expires_at`) USING BTREE
) ENGINE=InnoDB;

CREATE TABLE if not exists `customers` (
  `id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `email` varchar(64) DEFAULT NULL
) ENGINE=InnoDB;

CREATE TABLE if not exists `categories` (
  `id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `name` varchar(32)  NOT NULL
) ENGINE=InnoDB;

CREATE TABLE if not exists `categories_products` (
  `category_id` int(11) NOT NULL,
  `product_id` int(11) NOT NULL,
  UNIQUE KEY `index_categories_vods_on_category_id_and_vod_id` (`category_id`,`product_id`) USING BTREE,
  KEY `index_categories_products_on_product_id` (`product_id`) USING BTREE
) ENGINE=InnoDB;

CREATE TABLE if not exists `contents` (
  `id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `product_id` int(11) DEFAULT NULL,
  `profile_id` int(11) DEFAULT NULL,
  `source` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `activated` tinyint(1) DEFAULT '1'
) ENGINE=InnoDB;

CREATE TABLE if not exists `profiles` (
  `id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL
) ENGINE=InnoDB;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.