How to get parent_id count?

Question

Users table

public function up()
{
    Schema::create('users', function (Blueprint $table) {
        $table->bigIncrements('id');
        $table->string('first_name');
        $table->string('last_name');
        $table->string('referral_code')->nullable();
        $table->integer('parent_id')->unsigned()->nullable();
        $table->string('mobile')->unique();
        $table->string('email')->unique();
        $table->string('password');
        $table->rememberToken();
        $table->timestamps();
    });
}

Order table

public function up()
{
    Schema::create('oreders', function (Blueprint $table) {
        $table->bigIncrements('id');
        $table->bigInteger('user_id')->unsigned();
        $table->foreign('user_id')->references('id')->on('users')->onDelete('cascade');
        $table->bigInteger('product_id')->unsigned();
        $table->foreign('product_id')->references('id')->on('products')->onDelete('cascade');
        $table->timestamps();
    });
}

I tried first and whereHas('user') replaced it

$orderCount = Order::whereHas('user')->withCount('parent_id')->get();
return $orderCount;

I get this error.

Call to undefined method App\Order::parent_id() (View: C:\xampp\htdocs\site\bazar\resources\views\Admin\master.blade.php)

Answer 1

You first need to define the relationship in your models App\User and App\Order

App/User.php

class User extends Model
{

    public function orders()
    {
        return $this->hasMany(Order::class);
    }

    public function parent()
    {
        return $this->belongsTo(User::class, 'parent_id');
    }

    public function children()
    {
        return $this->hasMany(User::class, 'parent_id');
    }

}

App/Order.php

class Order extends Model
{

    public function user()
    {
        return $this->belongsTo(User::class);
    }
}

I do believe you want to count the number of order for a user.
But first of all, we will fix some issues / I'm suspecting you have.

• Table name orders is called oreders
• You don't need to verify if an order has a user Order::whereHas('user') since $table->bigInteger('user_id')->unsigned(); is not nullable. It means an order cannot exists without a user.
• Not an issue but a suggestion $table->bigInteger('user_id')->unsigned(); can be simplified with $table->unsignedBigInteger('user_id');

Now the interesting part

$orderCount = Order::whereHas('user')->withCount('parent_id')->get();
return $orderCount;

In my understand you're trying to get the number of orders of of a parent of the user. I will show you some use case that may help your understanding.

// Get the total number of orders
$orderCount = Order::count();

// Get the total number of orders of a user
$userOrderCount = $user->orders()->count();

// Include the number of orders in the user attributes
$user = User::withCount('orders')->find($userId); // notice 'order' is, in fact `orders()` from the App\User methods

// Include the number of orders in the parent attributes
$parent = User::withCount('orders')->find($user->parent_id);

// Number of orders of the parent
$parentOrderCount = Order::where('user_id', $user->parent_id)->count();

// Edit: As you commented, you also want to know "The marketers can see how many people bought their code"
// I'm assuming this is the number of children (I have added the relation in the model above)
$childrenCount = $user->children()->count()

Note : when you do Order::where('user_id', $user->parent_id)->count(); you don't need to verify that the user has a parent first. parent_id will return null and user_id cannot be null. So it will just return 0