MMN Rank Query

Question

MMN Rank Query

Navigation

#1 by (0 votes)

0

Hello, I have the following problem: I made a micro-social network for managers, where initially the registration is done only by invitation, thus "measuring" the influence of each one. My problem is in the ranking that currently counts the invitations accepted by each person and the one with the most accepted invitations, obviously it is at the top of the ranking:

      SELECT 
          u.usr_id, b.ref_id, u.nome, b.c_hab, COUNT(*) AS qnt
      FROM
          tbl_user AS u
      INNER JOIN
          tbl_user b ON u.usr_id = b.ref_id
      WHERE
          b.c_hab = 1
      GROUP BY u.nome
      ORDER BY qnt DESC;

My problem arose when they asked me to count the direct invitations, that is, the manager sent to so-and-so (we will call it level 1), the invitations that so-and-so sent (level 2) and so on. It's confusing to explain, but basically a Multilevel Marketing system and count the invitations invitations, but I have no idea how to accomplish this ...

Follow the users table for further details:

      CREATE TABLE 'tbl_user' (
        'usr_id' smallint(6) NOT NULL,
        #Aqui é onde são armazenados as IDs de quem convidou você
        'ref_id' smallint(6) DEFAULT NULL,
        'nome' varchar(100) COLLATE utf8_unicode_ci NOT NULL,
        'email' varchar(50) COLLATE utf8_unicode_ci NOT NULL,
        'dt_nascimento' date DEFAULT NULL,
        'cel' varchar(15) COLLATE utf8_unicode_ci NOT NULL,
        'endereco' varchar(255) COLLATE utf8_unicode_ci NOT NULL,
        'numero' varchar(10) COLLATE utf8_unicode_ci DEFAULT NULL,
        'senha' varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
        'foto' varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
        'token' char(10) COLLATE utf8_unicode_ci DEFAULT NULL,
        'c_hab' char(1) COLLATE utf8_unicode_ci DEFAULT '0',
        'nivel' char(1) COLLATE utf8_unicode_ci DEFAULT '1',
        'candidato' varchar(25) COLLATE utf8_unicode_ci DEFAULT NULL,
        'dt_cad' timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
      ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

If you can give a possible solution, thank you in advance!

mysql mariadb

asked by anonymous 10.07.2018 / 19:40

1 answer

How to prevent '\ n' being written to the console Caused by: javax.ejb.CreateException: Error checking if entity exists: java.sql.SQLSyntaxErrorException: ORA-01722: Invalid number

score 0 · Accepted Answer

I do not want this answer to solve 100% of your problem, but I believe that what you should do for record purposes is to keep a table unique to the invitations.

Your invitation table would refer to the user who made the invitation and the user who received the invitation. In addition to another reference to the invitation table itself, this last reference would help you to know what the indication level is.

Follow the diagram

User table

CREATE TABLE IF NOT EXISTS 'tbl_user' (
  'usr_id' SMALLINT(6) NOT NULL,
  'nome' VARCHAR(100) NULL,
  'email' VARCHAR(45) NULL,
  'dt_nascimento' DATE NULL,
  PRIMARY KEY ('usr_id'))
ENGINE = InnoDB

Table Invitations

CREATE TABLE IF NOT EXISTS 'convites' (
  'id' INT NOT NULL,
  'dt_indicacao' DATETIME NULL,
  'convites_id' INT NULL,
  'tbl_user_enviou' SMALLINT(6) NOT NULL,
  'tbl_user_recebeu' SMALLINT(6) NOT NULL,
  PRIMARY KEY ('id', 'convites_id', 'tbl_user_enviou', 'tbl_user_recebeu'),
  INDEX 'fk_convites_tbl_user2_idx' ('tbl_user_enviou' ASC),
  INDEX 'fk_convites_tbl_user1_idx' ('tbl_user_recebeu' ASC),
  INDEX 'fk_convites_convites1_idx' ('convites_id' ASC),
  CONSTRAINT 'fk_convites_tbl_user2'
    FOREIGN KEY ('tbl_user_enviou')
    REFERENCES 'tbl_user' ('usr_id')
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT 'fk_convites_tbl_user1'
    FOREIGN KEY ('tbl_user_recebeu')
    REFERENCES 'tbl_user' ('usr_id')
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT 'fk_convites_convites1'
    FOREIGN KEY ('convites_id')
    REFERENCES 'convites' ('id')
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
) ENGINE = InnoDB

Accessories were generated by the workbench