[RESOLVED] Query where hierarchical data is involved?

h4x0rmx

For simplicity, I have the following tables:

+transactions
 - id
 - user_id
 - name
 - date

+users
 - id

+tags_transactions
 - id
 - transaction_id
 - tag_id

+tags
 - name
 - id
 - name
 - parent_id

In the tags table, parent_id refers to another record in tags table, so tags can function as groups and subgroups. A subgroup tag will have a value for parent_id. Main tags or groups will have null for parent_id because they work as the root of a category.
Let's say I have the following tags:
+Auto
- Insurance
- Gas
- Maintenance
+Housing
- Rent
- Utilities
- etc

Now, I have a transaction and I tag it with "Gas". Transactions can have only one tag (so they can be part of only a group or a subgroup, but by being part of a subgroup, they are automatically part of a group. i.e. The transaction I just tagged with "Gas" is also part of "Auto".

I need to write a query that will help me know how much a given user spends per month per group (not subgroup, so I want the total spending per month for Auto and for Housing).

I've come up with the following query

SELECT tags.name, YEAR(t.date), MONTH(t.date), SUM(t.deposit), SUM(t.withdrawal) FROM transactions AS t
INNER JOIN users AS u
ON u.id = t.user_id
INNER JOIN tags_transactions AS tt
ON tt.transaction_id = t.id
INNER JOIN tags AS tags
ON tags.id = tt.tag_id
WHERE t.user_id = 92
GROUP BY tags.id, YEAR(t.date), MONTH(t.date);

but this gets only the spending per month for subgroups (insurance, gas, maintenance, rent, etc).

There are hundreds, if not thousands, of tags as the users create them...

Any idea on how to get the spending per month per category/group?
Any help will be appreciated!

johanafm

Remove tags.name from selected fields. Add tags.parent_id. Group by should be on parent_id, year, month.

h4x0rmx

johanafm;10982827 wrote:
Remove tags.name from selected fields. Add tags.parent_id. Group by should be on parent_id, year, month.

That works great, but now I need to get the name of the tags... I thought of doing a self join, which doesn't seem to be the most effective way of getting the tag name, what do you think?

SELECT tags_parent.id, tags_parent.name, sq.tpid, sq._year, sq._month, sq.deposits, sq.withdrawals
  FROM tags AS tags_parent
  INNER JOIN (

SELECT tags.parent_id AS tpid, YEAR(t.date) AS _year, MONTH(t.date) AS _month, SUM(t.deposit) AS deposits, SUM(t.withdrawal) AS withdrawals
  FROM transactions AS t
  INNER JOIN users AS u
    ON u.id = t.user_id
  INNER JOIN tags_transactions AS tt
    ON tt.transaction_id = t.id
  INNER JOIN tags AS tags
    ON tags.id = tt.tag_id
  WHERE t.user_id = 92
    AND (
      YEAR(t.date) <> YEAR(CURDATE())
      AND MONTH(t.date) >= MONTH(CURDATE())
      )
  GROUP BY tags.parent_id, YEAR(t.date), MONTH(t.date)

  ) AS sq
  ON sq.tpid = tags_parent.id;

Also, I wanted to exclude the transactions of the current month, so I added two extra conditions on the inner query, I thought that by wrapping these two extra conditions in parenthesis mySQL would take them as one condition, but it doesn't... the query is excluding all transactions from the current year, what's the correct way of doing this?

I'll appreciate the help!

h4x0rmx

I figured out how to exclude the records from the current month

WHERE t.user_id = 92
        AND t.date < ( CURDATE() - DAYOFMONTH( CURDATE() ) )

I just want to make sure that there's no better way of getting the tag names of the parent_id tags.
Any ideas?

johanafm

When you say "name of the tags", do you actually mean 'name of the "parent tags"'? That is, do you want to show all tag names belonging to the parent, or the actual group label itself? If it is the parent tag name, then simply go with your first query, add tagname to the selected fields and also add it to the group by clause next to id.

SELECT tag.id, tag.name ...
...
GROUP BY tag.id, tag.name, year, month

If you want to be able to show names of all tags belonging to that group, I'd say that question is unrelated to the above one, and that you should do it separately. I.e.

SELECT tag.name, tag.parent_id
...
ORDER BY parent_id

And for easier processing, you could then in php build an array with parent_id as element keys

while ($row = dbfetch())
{
    $tagnames[$row['parent_id']] = $row['tagname'];
}