Mysql - Recursive Query to get only parent categories

Question

Mysql - Recursive Query to get only parent categories

Navigation

#1 by (1 votes)
#2 by (0 votes)

1

I have the following table category :

id | desc_cat       | parent_id
19 | Pessoais       | (null)
20 | Cartao Credito | 19
21 | Academia       | 19
22 | Residencia     | (null)
23 | Agua           | 22
24 | Luz            | 22
25 | Aluguel        | 22

And I have the table containing postings with name cashbook :

id  | value    | category_id
177 | 480.55   | 20
178 | 100.00   | 24
179 | 580.00   | 25
180 | 80.00    | 21
181 | 28.00    | 23

I need to make a query in cashbook and return the summed and grouped values by the parent category (where it is as null ), for example:

Categoria-Pai | Total
Pessoais      | 560,55
Residencia    | 708

Can you help me mount this query ?? I got some examples of recursive queries but I'm very lost. My system is being done in PHP (Yii framework).

mysql

asked by anonymous 01.05.2016 / 21:56

2 answers

How to check if the connection dropped before sending a data via ajax? Request header field Content-Type is not allowed by Access-Control-Allow-Headers in preflight response [duplicate]

score 1 · Answer 1

Thanks guys, SQL below solved my problem:

SELECT y.desc_category as segment, sum( x.value) as total FROM (
SELECT category.id_category, category.desc_category, category.parent_id , c.value AS value
FROM category
INNER JOIN cashbook AS c ON category.id_category = c.category_id )AS x 
INNER JOIN category AS y ON x.parent_id = y.id_category
INNER JOIN user AS u ON y.user_id = u.id
WHERE u.id = 3
GROUP BY y.desc_category

score 0 · Answer 2

"http://sqlfiddle.com/#!9/add59/1"> link

Node that your categories were a tree with only two levels, so all the looks are directly connected to the root, in this case a simple LEFT JOIN with COALESCE should work.

SELECT
  id,
  desc_cat,
  SUM(value)
FROM (
  SELECT 
    COALESCE(CatParent.id, Category.id) id, 
    COALESCE(CatParent.desc_cat, Category.desc_cat) desc_cat,
    CashBook.value
  FROM CashBook
  JOIN Category ON CashBook.category_id = Category.id
  LEFT JOIN Category AS CatParent ON Category.parent_id = CatParent.id
) AS CatParent
GROUP BY id, desc_cat

But this approach will only work in this situation, if your tree goes deeper, you will have problems ... for example ... if your Category table has the following data:

| id |            desc_cat | parent_id |
|----|---------------------|-----------|
| 26 |         Categoria 1 |    (null) |
| 27 |   Sub Categoria 1.1 |        26 |
| 28 | Sub Categoria 1.1.1 |        27 |
| 29 | Sub Categoria 1.1.2 |        27 |
| 30 |   Sub Categoria 1.2 |        26 |
| 31 | Sub Categoria 1.2.1 |        30 |
| 32 | Sub Categoria 1.2.2 |        30 |

I even tried to emulate a recursive query using CTE using View or a Function with MySQL ... but I could not do it in SQLFiddle , I believe MySQL does not support this type of structure.

You can still use WHILE to try to do something, but your MySQL will not be able to infer an execution plan, your query will be complex. I would only advise you to do this for a scheduled JOB.

But as this data is going to be consumed in PHP, the ideal thing is to do this type of direct control in PHP ... unfortunately I do not know the PHP enough to give you an example, however I can update this answer with a snippet of Code in C # or JavaScript, then you could make a port from this logic to PHP.