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.
via
Chebli Mohamed
