Complex query help required

Databases
1

Let me first start by saying that if anyone have a better idea for me to do this, then please, your help will be greatly appreciated.

I want to implement a status system based on the amount of referals someone has made or influenced.

I have 5 levels. When someone joins, they are default on Level 1 and i have a field in the table called ‘status’ with an integer value assigned, see list below:

LEVEL NAME                      INT VALUE
Level 1: Commoner                 25
Level 2: Labourer                 30
Level 3: Apprentice               35
Level 4: Knight                   40
Level 5: Lord                     50

By default, when a person joins and does not enter a referer, a default referer id is assigned.

Currently my table looks like this:

CREATE TABLE `users` (
  `id` int(11) NOT NULL auto_increment,
  `firstname` varchar(64) NOT NULL,
  `lastname` varchar(64) NOT NULL,
  `email` varchar(64) NOT NULL,
  `status` smallint(5) NOT NULL,
  `refid1` int(11) NOT NULL,
  `refid2` int(11) NOT NULL,
  `refid3` int(11) NOT NULL,
  `refid4` int(11) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

I store the first referer in the refid1 field. If the user then invites another person, his id will be stored in the refid1 field and the original referer will be stored in the refid2 field and so on, with the final reference to the original referer disappearing from the referal list when he is replaced in refid4. If it is unneccesary to store the values as i have and only store the referer details in refid1 (nested model), it would be nice to know.

Now I need to run a query that will adjust the status of a user if it meets certain criteria. For the sake of clarity, I will try and simplify what I want to achieve:
USER A = The Website; Default Referer (id = 1)
USER B = Non-refered user signing up (id = 2)
USER B will then have refid1=1 as his referer.
For USER B to become Level 2 Labourer he needs to invite atleast 10 users.
For USER B to become Level 3 Apprentice a minimum of 10 of his invited users need to have each invited atleast 10 users.
For USER B to become Level 4 Knight a minimum of 10 of his invited users need to have invited atleast 10 users whom should have in turn invited atleast 10 users.
Since this only goes to Level 5, you can understand where this is going. The minimum requirement to move up in status is that you need to have atleast 10 users refered at each level basically.

Is there someone that can help me build a query for this that will not take ages to run or do I need to restructure my database. I have been up for a couple of nights now trying to figure out how I am going to implement this and I just cannot seem to get it right.

Thanks for any help.

2

I would get rid of the refid1-4 columns. Why would you want to throw away valuable data about who referred who after the 4th referral?

Create a second table

CREATE TABLE user_referred (user_id INT, referred_user_id INT, PRIMARY KEY (user_id, referred_user_id));

Insert a row into this table each time one user refers another to your site.

Now you can see how many users someone has referred by counting their rows in that table

SELECT COUNT(*) FROM user_referred WHERE user_id = #

And you can count how many users their referrals have referred by joining the table to itself as many times as how deep you want to follow the referral chain

3

Why do I want to create a new table when I can just use refchid1 as it is at the moment? It would have the same function as a seperate table that stores that value :slight_smile:

Someone suggested I use triggers to automate the level updating and that way keep track of all the upgrades in levels dependent on all other referer levels below. This makes sense to me in the way that if someone below a certain level has refered more people they should break from the original referer and be the same level as them.

Would anyone please evaluate this TRIGGER for me and comment if I have the correct assumption:


CREATE TRIGGER ins_trig BEFORE INSERT ON users
FOR EACH ROW
BEGIN
UPDATE users, ( SELECT SUM(refchid1) FROM users WHERE refchid1=$refererid) AS total
SET status = 
CASE
 WHEN status = "25" THEN status = "28" 
 WHEN status = "28" THEN status = "30"
 WHEN status = "30" THEN status = "35"
 WHEN status = "35" THEN status = "40"
WHERE total >= 10
END;

This would be the first time I am working with triggers. If I understand it correctly, I only setup the trigger once and it will always be active? I guess the SUM could also be a COUNT(refchid1), just wondering which would be faster :stuck_out_tongue:

4

Just wish to fix my previous post, but can’t seem to edit it anymore.

This is the update part of the trigger now:


UPDATE users, ( SELECT COUNT(refchid1) AS total FROM users WHERE refchid1=$refererid) AS t SET status = 
CASE  
 WHEN ((t.total > 10) AND (status = "25")) THEN "28"
 WHEN ((t.total > 10) AND (status = "28")) THEN "30"
 WHEN ((t.total > 10) AND (status = "30")) THEN "35"
 WHEN ((t.total > 10) AND (status = "35")) THEN "40"
 ELSE status
END
WHERE id = $refererid
5

I have now made a select query that uses joins to return the hierarchy:


SELECT t1.id AS lev1, t2.id AS lev2, t3.id AS lev3, t4.id AS lev4, t5.id AS lev5
FROM users AS t1
LEFT JOIN users AS t2 ON t2.refchid1 = t1.id
LEFT JOIN users AS t3 ON t3.refchid1 = t2.id
LEFT JOIN users AS t4 ON t4.refchid1 = t3.id
LEFT JOIN users AS t5 ON t5.refchid1 = t4.id
WHERE t1.id =3

This brings me closer to where I need to be.

Simple, because I am just trying to determine statuses :slight_smile: I don’t need the complete referal list beyond the 5 status levels I have.