MYSQL query to count descendants of items by finding its id in a comma separated string

Question

My MYSQL database uses a tree-like system where each item can have an arbitrary number of descendants. Each item has a regular INT 'parent' column containing its parent as well as a VARCHAR 'parents' column which consists of a comma-separated string containing all of its ancestor's ids.

id    parent    parents
-------------------------------
1     0         0
2     1         0,1
3     1         0,1
4     3         0,1,3

I need to get a list of all items, each of them with their total number of descendants counted up. Here is the query I have so far:

SELECT items.id AS item_id, 
COUNT(children.id) AS children 
FROM items items 
LEFT JOIN items children ON (items.id IN (children.parents))

This just sends back one row, with a child count of 0. How do I do this properly?

EDIT:

After fixing the query so it appears like this:

SELECT 
  i.id AS item_id, 
  COUNT(*) AS children 
FROM 
  items i 
LEFT JOIN 
  items c 
  ON (i.id IN (c.parents))
GROUP BY i.id;

the results show the rows, but each has only one child. This does not reflect the data, presumably something is wrong with the IN statement (FIND_IN_SET does the same thing).

EDIT2:

After changing the IN statement to the following

ON LOCATE(i.id, c.parents) > 0

item 1 has the correct number of children (3) but the remaining items all show up as having 1 child. Items 2 and 4 should have 0, and 3 should have 1.

Answer 1

You need to GROUP BY items.id for COUNT()to work as intended.

With aliases changed to something less ambiguous:

SELECT 
  i.id AS item_id, 
  COUNT(*) AS children 
FROM 
  items i 
LEFT JOIN 
  items c 
  ON FIND_IN_SET(i.id, c.parents) > 0 
WHERE c.id <> i.id
GROUP BY i.id;

For more complex COUNT()/GROUP BY examples, see this question or MySQL documentation. For FIND_IN_SET(), nice example here.

See sqlfiddle here