StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PONested Set Model, count items in categories
text
Body
copied!<p>I've got a Nested Set Model working for my site with items in subcategories and so on. It's working great except for one problem I can't come around.</p> <pre><code>+---------+-----------------------------+ | item_id | item_name | +---------+-----------------------------+ | 1 | Laptop | | 2 | iPod Classic 80GB | | 3 | iPod Classic 160GB | +---------+-----------------------------+ +---------+-------------+ | item_id | category_id | +---------+-------------+ | 1 | 4 | | 2 | 2 | | 3 | 2 | +---------+-------------+ +-------------+--------------------+-----+-----+ | category_id | name | lft | rgt | +-------------+--------------------+-----+-----+ | 1 | iPod | 1 | 6 | | 2 | Classic | 2 | 3 | | 3 | Nano | 4 | 5 | | 4 | Computers | 7 | 8 | +-------------+--------------------+-----+-----+ </code></pre> <p>Using the following query:</p> <pre><code>SELECT parent.name, COUNT(product.item_id) FROM Category AS node, Category AS parent, Item_Category AS product WHERE node.lft BETWEEN parent.lft AND parent.rgt AND node.category_id = product.category_id GROUP BY parent.name ORDER BY node.lft; </code></pre> <p>Gives the following output:</p> <pre><code>+-----------------+------------------------+ | name | COUNT(product.item_id) | +-----------------+------------------------+ | iPod | 2 | | Classic | 2 | | Computers | 1 | +-----------------+------------------------+ </code></pre> <p>In other words, all fields that don't have products in them won't be shown. Now to the problem, I want to show them with the COUNT() result = 0. How would my query look to make that happen? :)</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload