1

I had a trouble that I dealt with using multiple queries which is not very efficient. I was wondering if you maybe know an easier way.

Problem description: Each user has an id of the person that 'invited' that user. This user can then invite someone else.

So

User 1 || ID 1 || Invited_by 0
User 2 || ID 2 || Invited_by 1
User 3 || ID 3 || Invited_by 1
User 4 || ID 5 || Invited_by 3
User 5 || ID 6 || Invited_by 4

How I can get the full list of users invited by user 1 and users that he invited (for example) with Mysql, php and laravel.

Any ideas will be appreciated!

1
  • Just to clarify, you are after a list of all the users invited by user 1 and then all the users that they in turn invited? Commented Jun 20, 2016 at 0:28

1 Answer 1

1

Okay let's suppose the Database structure is something similar to this

Table: users

int: user_id
int: invited_by

Where invited_by is a foreign key that references user_id in the same table (users).

I have created the following migration to create the above database structure (p.s you are free to create it using raw MySql query)

<?php

use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;

class CreateUsersTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('users', function (Blueprint $table) {
            $table->increments('user_id');
            $table->integer('invited_by')->unsigned()->nullable();

            $table->foreign('invited_by')
                ->references('user_id')
                ->on('users');
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::drop('users');
    }
}

Then I Inserted into users table some test data as following:

user_id: 1, invited_by: null
user_id: 2, invited_by: 1
user_id: 3, invited_by: 1
user_id: 4, invited_by: 1

Now in User eloquent model I have added those two functions:

/**
 * Function to get the users that were invited by the current user.
 *
 * @return \Illuminate\Database\Eloquent\Relations\HasMany
 */
public function invitees()
{
    return $this->hasMany(User::class, 'invited_by', 'user_id');
}

/**
 * Function to get the user who invited the current user.
 *
 * @return \Illuminate\Database\Eloquent\Relations\BelongsTo
 */
public function invitedBy()
{
    return $this->belongsTo(User::class, 'invited_by', 'user_id');
}

Now let's say if I want to know who invited user of ID 2:

$user = User::where('user_id', 2)->first();
$invitedByUser = $user->invitedBy()->getResults();

If we want to know the users that user of ID 1 invited:

$user = User::where('user_id', 1)->first();
$invitees = $user->invitees()->getResults();

I have tried it and it works as expected. Hope this helps.

Sign up to request clarification or add additional context in comments.

2 Comments

Thanks mate, I really appreciate your answer, however my question was a bit wrong (now it's edited) but I think you might set me on right path.
@Lixi I have edited my answer to give a working example. Hope it helps and feel free to comment if you have any questions. Cheers

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.