Mysql selecting rows from multiple tables

Question

I'm working on a catalog site where users can browse categories. Categories can contain other categories and products, and products can belong to more than one category. The relevant database schema looks something like this:

CREATE TABLE products (
    product_id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
    product_title VARCHAR(100) NOT NULL,
    product_status TINYINT UNSIGNED NOT NULL
);

CREATE TABLE product_categories (
    category_id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
    parent_category_id INT UNSIGNED NOT NULL,
    category_title VARCHAR(100) NOT NULL,
    category_status TINYINT UNSIGNED NOT NULL,
    category_order INT UNSIGNED NOT NULL
);

CREATE TABLE products_categories (
    product_id INT UNSIGNED NOT NULL,
    category_id INT UNSIGNED NOT NULL,
    product_order INT UNSIGNED NOT NULL,
    PRIMARY KEY(product_id, category_id)
);

The issue i'm having is I need to paginate the results using LIMIT n, n:

$perpage = 20;
$start = (isset($_GET['page'])) ? (int)$_GET['page'] * $perpage : 1;
$limitsql = "LIMIT $start, $perpage";

But I can't figure out how to select both distinct categories and products without joining and merging the results. Ideally I would like results like this:

product_id      |       product_title   |       category_id     |       category_title
NULL            |       NULL            |       32              |       category foo
NULL            |       NULL            |       239             |       category bar
9391            |       product foo     |       NULL            |       NULL
325             |       product bar     |       NULL            |       NULL

The best I've been able to do is get something like this, which doesn't really help:

product_id      |       product_title   |       category_id     |       category_title
9391            |       product foo     |       32              |       category foo
325             |       product bar     |       239             |       category bar
239             |       product foo2    |       32              |       category foo
115             |       product bar2    |       239             |       category bar

The only other solutions that I can think of would be to query all subcategories and products within the category, stick them in a php array and extract the current page with array_slice. Considering the volume of products (several thousand) this isn't a very appealing option.

Otherwise I could query the number of categories, and offset the $start in the LIMIT clause by the number of categories. This get's messy though if there is more than a full page of categories.

Here is my current working query which gives me the results above:

SELECT
 p.product_id, p.product_title,
 c.category_id, c.category_title
FROM products AS p
JOIN product_categories AS c
 ON c.parent_category_id='20'
INNER JOIN products_categories AS pc
 ON p.product_id=pc.product_id
WHERE p.product_status='1' AND pc.category_id='20'
ORDER BY pc.product_order ASC

Edit

I think i've got it working with UNION, which I completely forgot about

SELECT
 c.category_id AS row_id, c.category_title AS row_title, 1 AS is_category
FROM product_categories AS c
WHERE c.parent_category_id='20'

UNION 

SELECT
 p.product_id AS row_id, p.product_title AS row_title, 0 AS is_category
FROM products AS p
INNER JOIN products_categories AS pc
 ON p.product_id=pc.product_id

Edit 2

I guess Union isn't going to work as I thought. Since both are treated as separate queries I can't apply LIMIT to the entire result, only each individual SELECT. Also it seems the columns selected from each statement must be of the same type of the corresponding type in the other statement.

Answer 1

Use:

SELECT *
  FROM (SELECT c.category_id AS row_id, c.category_title AS row_title, 1 AS is_category
          FROM product_categories AS c
         WHERE c.parent_category_id='20'
        UNION 
        SELECT p.product_id AS row_id, p.product_title AS row_title, 0 AS is_category
          FROM products AS p
          JOIN products_categories AS pc ON p.product_id=pc.product_id) x
LIMIT x, y

Answer 2

Another way you could approach this would be changing your schema to make categories and products the same thing essentially.

CREATE TABLE items (
    item_id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
    item_title VARCHAR(100) NOT NULL,
    item_status TINYINT UNSIGNED NOT NULL,
    category_or_item TINYINT UNSIGNED NOT NULL,
);

CREATE TABLE items_parents (
    item_id INT UNSIGNED NOT NULL,
    parent_id INT UNSIGNED NOT NULL, #points to itemid
    item_order INT UNSIGNED NOT NULL,
    PRIMARY KEY(item_id, parent_id)
);

Your query then is flat and you can sort it by category_or_item so categories appear first.