CODEWITHSUNDEEP
mysqllaravelmany-to-many
Nick Surmanidze
Nick Surmanidze

Reputation: 1689

Laravel 5.3 query - distinct results with sum of votes

I have faced a tricky problem which I would like to solve eloquent way but I think I would need to get into raw sql, which I do not really want. Here is what I have so far:

  1. User model - user can have many posts id | name

  2. Posts - can have several categories id | user_id | title ... etc

  3. Skills - This is pivot table for many-to-many relationship (skill_types related to posts) id | skill_type_id | post_id

  4. Skill types - this is list of unique skills. id | slug | title | etc..

  5. Votes - votes are related to Skills table

id | skill_id

What I need to do is to get top 10 skills of users with number of votes. For doing it I need to merge skills by name and sum up number of votes, then sort by number of votes and output the top 10.

Probably my brain is too tired today already and I will solve the puzzle tomorrow morning but until I came up with a solution, maybe someone else will help with it.

My Solution:

public function getTopSkills($limit = 10, $offset = 0)
{

    $results = DB::select('select skill_types.name as name, count(likes.id) as  votes 
                        FROM 
                          skills
                        left join likes 
                        on skills.id=likes.likeable_id
                        join posts on skills.item_id = posts.id
                        join users on posts.user_id = users.id
                        join skill_types on skill_types.id = skills.skill_type_id
                        where users.id = :userid
                        group by skill_types.name 
                        order by votes desc
                        limit :limit
                        offset :offset', ['userid' => $this->id, 'limit' => $limit, 'offset' => $offset]);
    return $results;
}

Upvotes: 2

Views: 1185

Answers (1)

faraday703
faraday703

Reputation: 141

Fortunately, the SQL is easier than you think

SELECT skill_id, count(id) 
FROM VOTES 
group by skill_id

Then you just have to tie skill_id back to the skill type table to get the name instead of a numerical ID

Upvotes: 2

Related Questions