Count distinct children within relationship

Hi, I'm trying to build a basic tracking system. I've setup a system using related tables and I'd like to avoid using raw DB queries if possible.

Here's how my models relate: Each Event belongs to a Session and a Session has multiple events.

        Schema::create('events', function (Blueprint $table) {
            $table->increments('id');
            $table->string('key');
            $table->float('type');
            $table->string('data');
            $table->string('timeDate');
            $table->string('tags');
        $table->unsignedInteger('session_id');
            $table->foreign('session_id')->references('id')->on('sessions')->onDelete('cascade');
            $table->timestamps();
        });

class event extends Model
{
    //
    public function session()
    {
        return $this->belongsTo('App\session');
    }
}

        Schema::create('sessions', function (Blueprint $table) {
            $table->increments('id');
            $table->string('device_id');
            $table->float('version');
            $table->string('platform');
            $table->string('start_time');
            $table->string('end_time');
            $table->float('length');
            $table->timestamps();
        });

class session extends Model
{
    public function events()
    {
        return $this->hasMany('App\event','session_id', 'id');
    }
 }

So the session table has the date range and other filters, like version and platform and is filtered by the use of scopes.

Each Event has a Key, which is the name of the event that occurred and essentially I just want to count how many distinct keys are found but filtered by the session.

If I was to write this using a raw query it would be along the lines of

SELECT 
    events.key AS action, events.session_id
FROM
    events
        INNER JOIN
    sessions ON sessions.id = events.session_id
where sessions.end_time > '2018-05-19 04:42:00' and sessions.end_time < '2019-05-19 05:42:00'

I've read the docs on Laravel relationships and I just can't see how I can generate the same kind of query using eloquent. I've tried using 'with', but it seems to either ignore the group by or cause errors.

    public function get_events_by_filter(Request $request) {
        $query = session::query();
        $query->when(request('platform'), function ($q) {
            return $q->Platform(request('platform'));
        });
       
        $events = $query->with(['events' => function ($query) {
            $query->groupBy('id','key')->count();
        }])->get();
}

Final output should be along the lines of:

{ 
    "ACTION_1": 19,
    "ACTION_2": 11,
    ....
    "ACTION_99" : 2
}

Thank you for your time

tisuchi

7 years ago

Level 70

First of all, in the event table, session_id filed is missing. You have forgotten to add that.

Now, you have a few options to figure it out.

Using relationship. In that case, you can pass an anonymous method in eager loading then do filtration.
In your relationship, you can define more logic. For example-

public function events()
{
        return $this->hasMany('App\event','session_id', 'id')->where('put your logic here')->where('Add more logic');
}

Maybe you can apply Laravel query scope. Read more: https://laravel.com/docs/5.8/eloquent#query-scopes

4 likes

kevinbui

7 years ago

Level 41

According to your thread title, there are two approaches to do this.

The first approach is to eager load distinctive events for each session.

$sessions = Session::withCount([
    'events' => function ($query) {
        $query->distinct();
    }
])
->whereBetween('end_time', ['2018-05-19 04:42:00', '2019-05-19 04:42:00')
->get()

The second approach is to join two tables together:

$sessions = Session::selectRaw('sessions.*, COUNT(DISTINCT events.id) AS events_count')
->join('events', 'sessions.id', '=', 'events.session_id')
->whereBetween('sessions.end_time', ['2018-05-19 04:42:00', '2019-05-19 04:42:00')
->get()

I have also noticed that the time range you put is nothing (from '2018-05-19 04:42:00' to '2018-05-19 04:42:00'). So please update this time range as well.

1 like

WelshyRob

7 years ago

Level 1

@kevinbui @tisuchi - Thanks for your direction.

I ended up writing this, which does what I need.

Can you see a way to refactor it at all, it seems a bit clunky!

        $sessions = Session::selectRaw('events.key AS action, COUNT(events.key) AS count')
        ->join('events', 'sessions.id', '=', 'events.session_id')
        ->when(request('start_date') && request('end_date'), function ($q) {
            $q->whereBetween(request('start_date'), request('end_date'));
        })->when(request('platform'), function($q){
            $q->Platform(request('platform'));
        })->when(request('device_id'), function($q) {
            $q->Device(request('device_id'));
        })->when(request('version'), function($q){
            $q->Version(request('device_id'));
        })->groupBy('events.key')->get();

Ideally I'd like to combine all of the scopes into a single query string / object and append that to:

Session::selectRaw()->myCombinedScopes()->get()

CorCronje

7 years ago

Level 6

Hello,

Your objective is "count how many distinct keys are in a session", thus this should theoretically work, I haven't tested it out.

$count = Session::->whereBetween('end_time', ['2018-05-19 04:42:00', '2019-05-19 04:42:00'])->events->pluck('key')->unique()->count();

Best.

Please or to participate in this conversation.