Count distinct children within relationship

Posted 1 week ago by WelshyRob

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

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

Reply to

Use Markdown with GitHub-flavored code blocks.