Mysql – COUNTING results from self-referenced table recursively

MySQLrecursive

I have a table called Clients. And at this table, I have an idFather to know who added this row to the table.

Sample:

id | user    | idFather | createdAt
---+---------+----------+-----------
01 | raphael | null     | 01-06-2016
02 | jose    | 01       | 03-06-2016
03 | ryan    | 01       | 02-06-2016
04 | alyssa  | 03       | 04-06-2016
05 | laysa   | 04       | 04-06-2016
06 | karen   | 02       | 04-06-2016
07 | roger   | 03       | 04-06-2016

Now i need know how much peoples an user invited indirectly. Sample:

The user (01 - Raphael ) invited the users ( 02 and 03 ) so:
    Raphael invited 2 directly
    And user (02 - Jose) invited no one
    But user (03 - Ryan) invited (04 and 07) total 2
    And user (04 was invited by 03) invited also the user (05) total 1

So Raphael invited 2 directly ( this i did not know )
And Raphael invited 3 indirectly ( this i need know )

I i'm trying look 7 levels under the user…
i'll try explain with this tree:

EDIT:
If the users from lv1 was hard to kick out from Couting… I can do this math on my system… i just need do recursively by 7 lvs… and count just lv1 and get the difference.

I'm looking to get a query result like this:

user    | indirectInviteds
--------+-----------------
raphael | 4

I'll search just 1 user per time… i`ll not try select ALL clients and look directly.. it will explode…!!!

Thanks for the help guys!

Best Answer

As your data is a tree structure, consider using the Nested Set data model.

A good write-up can be found at Mike Hillyer's website, here: http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/

complete with lots of SQL to manage the tree data.

Related Solutions

Grant Table Privileges to a MySQL User Connecting from Any Host

When you execute GRANT SELECT ON store.catalog TO 'wordpress'@'%';, mysqld wants to insert a row into the grant table mysql.tables_priv. Here is mysql.tables_priv:

mysql> show create table mysql.tables_priv\G
*************************** 1. row ***************************
       Table: tables_priv
Create Table: CREATE TABLE `tables_priv` (
  `Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Db` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
  `User` char(16) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Table_name` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Grantor` char(77) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `Table_priv` set('Select','Insert','Update','Delete','Create','Drop','Grant','References','Index','Alter','Create View','Show view','Trigger') CHARACTER SET utf8 NOT NULL DEFAULT '',
  `Column_priv` set('Select','Insert','Update','References') CHARACTER SET utf8 NOT NULL DEFAULT '',
  PRIMARY KEY (`Host`,`Db`,`User`,`Table_name`),
  KEY `Grantor` (`Grantor`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Table privileges'
1 row in set (0.00 sec)

mysql>

Since you want to insert a row into mysql.table_priv where user='wordpress' and host='%', there has to exist a row in mysql.user where user='wordpress' and host='%'.

You also mentioned that you are using MySQL Workbench. You must be using 'root'@'localhost'. That would usually have all rights and a password.

If you want to just allow anonymous SELECT against that table, first run this:

GRANT USAGE ON *.* TO 'wordpress'@'%';

This will place wordpress@'%' into mysql.user. Afterwards, GRANT SELECT ON store.catalog TO 'wordpress'@'%' should run just fine.

You will have to see what other wordpress entries are in mysql.user. This should show what SQL GRANT commands you need:

SELECT CONCAT('GRANT SELECT ON store.catalog TO ',userhost,';') GrantCommand
FROM
(
    SELECT CONCAT('''',user,'''@''',host,'''') userhost
    FROM mysql.user WHERE user='wordpress'
) A;

Mysql – Multiple left join where not in another table

According to comment you are not interested in users at all, you just want the person to be client or provider (boolean logic, so it can be both):

WHERE (client.id IS NOT NULL OR provider.id IS NOT NULL)

Best Answer

Related Solutions

Grant Table Privileges to a MySQL User Connecting from Any Host

Mysql – Multiple left join where not in another table

Related Question