Return array of grouped results

Question

Note: I'm using Laravel 5.3.

I have a table, comments, that looks like this:

+====+=========+
| id | message |
+====+=========+
|  1 |      Hi |
|  2 |   World |
+====+=========+

I have a second table, comment_stats, which keeps track of the total number of votes on each comment, that looks like this:

+====+============+=======+
| id | comment_id | votes |
+====+============+=======+
|  1 |          1 |    10 |
|  2 |          2 |     0 |
+====+============+=======+

And lastly, I have a third table, comment_votes, which keeps track of each individual user's vote for each comment, that looks like this:

+====+============+=========+=========+
| id | comment_id | user_id |    type |
+====+============+=========+=========+
|  1 |          1 |      10 |       0 |
|  2 |          1 |       9 |       0 |
|  3 |          1 |       8 |       1 |
|  4 |          1 |       7 |       2 |
|  5 |          1 |       6 |       1 |
|  6 |          1 |       5 |       5 |
|  7 |          1 |       4 |       3 |
|  8 |          1 |       3 |       3 |
|  9 |          1 |       2 |       1 |
| 10 |          1 |       1 |       0 |
+====+============+=========+=========+

As you can see, each comment can be voted on by other users (comment_votes) and the total votes are kept track of in comment_stats. Each vote has a type. There are a total of 6 possible types (0-5).

My current Comment.php class looks like:

class Comment extends Model
{
    protected $with = [
        'votes', 'stats'
    ];

    public function votes()
    {
        return $this->hasMany('App\Vote');
    }

    public function stats()
    {
        return $this->hasOne('App\Stat');
    }
}

My Stat.php class looks like:

class Stat extends Model
{
    protected $with = [
        'topTypes'
    ];

    public function comment()
    {
        return $this->belongsTo('App\Comment');
    }

    public function topTypes()
    {
        // Need to return an array of the top 3 types here
    }
}

And my Vote.php class looks like:

class Vote extends Model
{
    public function comment()
    {
        return $this->belongsTo('App\Comment');
    }
}

I'd like to retrieve the top 3 vote types for each comment. So for comment_id = 1, the output would be [0, 1, 3] (as an array), in that order. 0 appears 3 times, 1 appears 3 times, and 3 appears twice. If there is a tie, it should get the lower integer type.

I'm trying to get the JSON to end up looking something like this, so that the top_types is part of stats:

{
    "id": 1,
    "message": "Hi",
    "stats": {
        "id": 1,
        "comment_id": 1,
        "votes": 10,
        "top_types": [0, 1, 3]
    }
}

How could I achieve this? All of these relationships are driving me insane.

Answer 1

Getting the results is simple enough.

$result = app(Comment::class)->with('stats.topTypes')->find(1);

Next, since you only want the top 3 types and not all of them, filter our eager-load query.

$result = app(Comment::class)->with('stats.topTypes', function (Eloquent\Builder $query) {
    $query->limit(3);
})->find(1);

See: Constraining Eager-loads

If this is going to be a common use for that model, you might consider moving that behavior to a Query Scope. Just be careful not to load too much business-logic in ony Model classes. It can be tempting, but a bit of an anti-pattern as that's not really what the Model layer of MVC is for.

Another option is to apply your limit to the actual relationship definition, in the Stat::topTypes() method. Again, only do this if your certain that all use-cases for that relationship will only ever want 3 results. You could also put an orderByDesc('createdAt') there, that might be more likely to be useful in call cases.

Lastly, since you don't want the complete Type model result, but just an array of IDs, remember that the nested results will be in the form of an Eloquent\Collection object, a child of Suppot\Collection with access to all the same bells and whistles.:

$result->stats->topTypes->transform(function (Type $type) {
    return $type->getKey();
});

See: Collections (transform)