Order by count this month

Published 6 months ago by ralphmorris

Hi there,

I have a referral system where users can refer other users.

Relationships

    public function referredBy()
    {
        return $this->belongsTo('App\User', 'referred_by');
    }

    public function referrals()
    {
        return $this->hasMany('App\User', 'referred_by');
    }

There is also a 'referral_activated' dateTime field which gets set once the user is confirmed which is the point when the referrer gets their reward.

In our admin interface I am trying to get the top 3 referrers this month and order them by their referrals count that month.

My code so far.

    // Controller
    public function topReferrersThisMonth()
    {
        return User::whereHas('referrals', function ($query) {
                        $query->thisMonth('referral_activated');
                    })
                    ->withCount('referrals')
                    ->orderBy('referrals_count', 'desc')
                    ->take(3)
                    ->get();
    }

    // Model
    public function scopeThisMonth($query, $field = 'created_at')
    {
        return $query
            ->whereDate($field, '>=', Carbon::now()->startOfMonth())
            ->whereDate($field, '<=', Carbon::now()->endOfMonth());
    }

This almost does what I want apart from it is ordering by the total count, not the count this month. Can anyone point me in the right direction?

Thanks :)

Ralph

bashy
bashy
6 months ago (1,002,020 XP)

You can't order by a relation. You will have to do some joins etc or look into this online more.

EDIT: Oh I think I got your question wrong. The withCount() will not do it via the whereHas. Check the query it performs with ->toSql() instead of ->get()

ralphmorris

Hi @bashy

Thanks for your reply.

Typically, an idea came to me just after posting this question and think I've got it working now!

I created a new relationship with the added thisMonth query scope.

    public function referralsThisMonth()
    {
        return $this->referrals()->thisMonth('referral_activated');
    }

And then modified the controller method as follows:

    public function getTopReferrersThisMonth()
    {
        return User::has('referralsThisMonth')
                    ->withCount('referralsThisMonth')
                    ->orderBy('referrals_this_month_count', 'desc')
                    ->take(3)
                    ->get();
    }
bashy
bashy
6 months ago (1,002,020 XP)

Yup, making a new relationship with the params within that will force it to query that. Most posts will probably say do that! :P

Please sign in or create an account to participate in this conversation.