1 year ago

Count distinct children within relationship

Posted 1 year 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) {
class event extends Model
    public function session()
        return $this->belongsTo('App\session');
        Schema::create('sessions', function (Blueprint $table) {
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

    events.key AS action, events.session_id
        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) {

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.