Jump to content

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


imgrooot

Recommended Posts

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;

 

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

 

Link to comment
Share on other sites

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 |
+---------+-------+----------+

 

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.