0

I have a question regarding ordering datasets with sequel.

I have tables 'jobs', 'users', 'comments'. Users can apply for any job, so I have a many-to-many association here (I also have a 'jobs_users' table). Jobs and comments represent one-to-many association. What I'm trying to implement is the following - I'd like to retrieve rows form 'jobs', ordered by:

  1. Number of users applied;

  2. Number of comments (most commented first).

I use sequel and so far I'm playing with explicit queries, so could you help me to implement such ordering in native sequel way?

Simplified db looks like this:

create_table :users do
    primary_key     :id
    String          :nickname
end

create_table :jobs do
    primary_key     :id
    Text            :description
end

create_table :jobs_users do
    primary_key     :id
    foreign_key     :job_id
    foreign_key     :user_id
end

create_table :comments do
    primary_key     :id
    Text            :comment
    foreign_key     :user_id
    foreign_key     :job_id
end
mie
  • 721
  • 5
  • 16

2 Answers2

0

You could try this -

SELECT j.id,
       COUNT(ju.id) AS appliedcount,
       COUNT(c.id) AS commentcount
FROM jobs j
     JOIN jobs_user ju ON ju.job_id = j.id
     JOIN comments c ON c.job_id = j.id
GROUP BY j.id
ORDER BY appliedcount DESC, commentcount DESC;
JHS
  • 7,761
  • 2
  • 29
  • 53
0

Assuming users can only apply once (perhaps restrict this by making the primary_key on the jobs_users table job_id and user_id you could get the number of applications for a job by simply grouping and counting around the job_id

DB[:jobs_users].group_and_count(:job_id).order(:count.desc).all

Same would be for comments. Most commented on jobs would be:

DB[:comments].group_and_count(:job_id).order(:count.desc).all

Now neither of these return the full record, but they will give you the top job_ids that you're looking for.

innonate
  • 159
  • 5