Can someone show me a proper way to do this query function?

[imgrooot]

Basically, there are two level referrals. I would like to get the count number for both.

1. Sponsor's referrals

2. Referrals' referral

 

This is my code. Level 1 works of course but I'm unable to get the correct data on level 2. I was wondering if there is a more efficient way to do this query?

// LEVEL 1 REFERRALS
$root_user_id = 50;

$find_referrals = $db->prepare("SELECT user_id FROM user_referrals WHERE sponsor = :sponsor");
$find_referrals->bindParam(':sponsor', $root_user_id);
$find_referrals->execute();
$result_referrals = $find_referrals->fetchAll(PDO::FETCH_ASSOC);
if(count($result_referrals) > 0) {
  $count_1  = 0;
  foreach($result_referrals as $key=>$row) {
    $user_id  = $row['user_id'];
    $count_1 += 1;

    // LEVEL 2 REFERRALS
    $find_referrals_2 = $db->prepare("SELECT user_id FROM user_referrals WHERE sponsor = :sponsor");
    $find_referrals_2->bindParam(':sponsor', $user_id);
    $find_referrals_2->execute();
    $result_referrals_2 = $find_referrals_2->fetchAll(PDO::FETCH_ASSOC);
    if(count($result_referrals_2) > 0) {
      $count_2  = 0;
      foreach($result_referrals_2 as $key=>$row) {
        $count_2 += 1;
      }
    } else {
      $count_2 = 0;
    }

  }
} else {
  $count_1 = 0;
  $count_2 = 0;
}

$level_1_refs = $count_1;
$level_2_refs = $count_2;

 

[Barand]

Can you post some sample data and the results you expect from that data?

[imgrooot]

11 minutes ago, Barand said:

Can you post some sample data and the results you expect from that data?

It basically returns in digits. So for level 1, if the sponsor has 10 referrals, it'll return "10". 

Each row in "user_referrals" table is counted as a referral. The query is simply counting each rows and combining them.

It only becomes an issue when I tried to combine these rows on the 2nd level of the query. It'll return "0" when it should be returning more than that.

[Barand]

That's OK. I know what the query should look like - I just needed something to test it with.

If you can't be bothered to help me to help you then I'll wish you luck.

[imgrooot]

14 minutes ago, Barand said:

That's OK. I know what the query should look like - I just needed something to test it with.

If you can't be bothered to help me to help you then I'll wish you luck.

I'm sorry. Did I do something wrong? 

I gave you the info as requested.

[Barand]

3 minutes ago, imgrooot said:

I gave you the info as requested.

Except for the sample test data and expected results!

[imgrooot]

46 minutes ago, Barand said:

Except for the sample test data and expected results!

What would the sample data test look like? The queries are simply based on a user_id. And the output is just the number of rows/referrals.

 

 

[kicken]

3 hours ago, imgrooot said:

What would the sample data test look like?

A few rows of the raw data from the tables you're trying to query, or a similar representative set of data.  An example of the output you want to generate using the given data would help as well.

[imgrooot]

4 hours ago, kicken said:

A few rows of the raw data from the tables you're trying to query, or a similar representative set of data.  An example of the output you want to generate using the given data would help as well.

Gotcha. That makes more sense.

This is what the mysql table looks like.

user_referrals table

referral_id | sponsor | user_id | posted_date
---------------------------------------------
1                1         2      2022-02-01 
2                1         3      2022-02-01 
3                1         4      2022-02-01 
4                2         5      2022-02-02 
5                2         6      2022-02-02 
6                3         7      2022-02-03 

Using the table above, I would be retrieving the following data results.

- Sponsor #1 has 3 direct referrals(level 1)

- Referral #2 has 2 direct referrals(level 2)

- Referral #3 has 1 direct referral(level 2)

In total, It would generate 3 referrals for level 1 and 3 referrals for level 2.

I hope this clears it up for you guys.

[Phi11W]

12 hours ago, imgrooot said:

I was wondering if there is a more efficient way to do this query?

For starters, do you actually care about the individual referrers' names or just the [total] number of them? 

Your code currently retrieves each and every referrer and "manually" counts them.  It's far, far more efficient to get your database to do that for you: 

$find_referrals = $db->prepare("SELECT count( user_id ) tally FROM user_referrals WHERE sponsor = :sponsor");

This will return you the number of referrers directly. 

Now, at first glance, you might think that your second level referrers query needs those user_ids in order to find their referrers. 
Doing this in code is a really Bad Idea - it's called the "1+N Query" Model and it's a nightmare for Application performance.  As the number of "secondary" queries ("N") grows, your Application slows to Run like a Slug(TM).  It's unscalable and untunable; there's nothing that can be done at the database end of things to improve matters. 

Instead, you can get your database to retrieve the second level referrers based on the original sponsor and their referrers, something like this:  

SELECT count( lvl2.user_id ) tally 
FROM      user_referrals lvl1 
LEFT JOIN user_referrals lvl2
     ON   lvl2.sponsor = lvl1.user_id 
WHERE lvl1.sponsor = :sponsor ; 

Regards, 
   Phill  W.

 

[Barand]

try

SELECT 
       r.sponsor
     , a.tot_sponsor
     , r.user_id
     , coalesce(b.tot_user, 0) as tot_user
FROM user_referrals r
     JOIN
     (
	   SELECT sponsor
            , count(*) as tot_sponsor
	   FROM user_referrals
       GROUP BY sponsor
     ) a ON r.sponsor = a.sponsor
     LEFT JOIN (
            SELECT sponsor
                 , count(*) as tot_user
            FROM user_referrals     
			GROUP BY sponsor
     ) b ON r.user_id = b.sponsor
WHERE r.sponsor = :root_sponsor

+---------+-------------+---------+----------+
| sponsor | tot_sponsor | user_id | tot_user |
+---------+-------------+---------+----------+
|       1 |           3 |       2 |        2 |
|       1 |           3 |       3 |        1 |
|       1 |           3 |       4 |        0 |
+---------+-------------+---------+----------+

user_referrals:
+----+---------+---------+
| id | sponsor | user_id |
+----+---------+---------+
|  1 |       1 |       2 |
|  2 |       1 |       3 |
|  3 |       1 |       4 |
|  4 |       2 |       5 |
|  5 |       2 |       6 |
|  6 |       3 |       7 |
+----+---------+---------+

PS Alternative solution

SELECT sponsor as user_id
     , count(*) as total
     , 'Direct' as type
FROM user_referrals
WHERE sponsor = 1
GROUP BY sponsor
UNION ALL
SELECT r.user_id
     , count(r2.sponsor) as total
     , 'Indirect'
FROM user_referrals r 
     LEFT JOIN
     user_referrals r2 ON r.user_id = r2.sponsor
WHERE r.sponsor = 1
GROUP BY r2.sponsor
ORDER BY type, user_id;

+---------+-------+----------+
| user_id | total | type     |
+---------+-------+----------+
|       1 |     3 | Direct   |
|       2 |     2 | Indirect |
|       3 |     1 | Indirect |
|       4 |     0 | Indirect |
+---------+-------+----------+