Laravel eloquent top 10 based on a unique count of rows that exist in pivot table

Essentially, what i am trying to achieve is to return X results ordered by a unique count of rows that exist in pivot table (polymorphic relation).

A little background, when a user reaches a profile page, a $profile->hit() function is called, this function logs a visit against the entity in a visitors table.

All this functionality is handle in a trait that I can assign to any model (currently Profile and Track).

In order to get these 'popular' posts, I will need to add a scopePopular to my trait. So in the controller I can call $profile->popular(10)->get();.

The count of visitors used to calculate these popular posts must be unique rows based on the ip_address column in my visitors table.

Here is the visitors table migration:

Schema::create('visitors', function(Blueprint $table) {
    $table->increments('id');

    $table->uuid('visitorable_id');
    $table->string('visitorable_type');

    $table->string('isp')->nullable();
    $table->string('country_code')->nullable();
    $table->string('country')->nullable();
    $table->string('city')->nullable();
    $table->string('region')->nullable();
    $table->string('region_name')->nullable();
    $table->string('ip_address')->nullable();
    $table->string('timezone')->nullable();
    $table->string('zip_code')->nullable();
    $table->string('latitude')->nullable();
    $table->string('longitude')->nullable();

    $table->timestamps();
});

and the relationship in my VistorableTrait that gets added to models :

public function visitors()
{
    return $this->morphMany('App\Visitor', 'visitorable');
}

How would I go about doing this using the query builder in my popular scope?

from Newest questions tagged laravel-5 - Stack Overflow http://ift.tt/2xgWc3i
via IFTTT