1

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.

IndigoFenix
  • 291
  • 2
  • 20
  • 1
    first, avoid using the same alias multiple times; "children" is used as a table alias and a column alias ; same for "items" alias on "times"; these aliases make query ambiguous – Preuk May 09 '16 at 10:49
  • please post your `CREATE TABLE` statements and some sample data – Preuk May 09 '16 at 11:03
  • 2
    This is a really bad schema, I would change the table to use a [Nested Set Model](https://en.wikipedia.org/wiki/Nested_set_model), with a direct parent column as a back up. You can then run very simple queries to get the descendants count, or any other count you want. – superphonic May 09 '16 at 11:10
  • @superphonic That is very useful. I am going to need to do a lot of queries up and down the tree, so I might go with that structure instead. – IndigoFenix May 09 '16 at 13:19

1 Answers1

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

Community
  • 1
  • 1
Preuk
  • 632
  • 7
  • 18