CODEWITHSUNDEEP
mysqlsqlruby-on-railsactiverecord
Foo
Foo

Reputation: 810

How can I create this complicated SQL query?

I’m using Active Record now. But it’s too slow if this code runs.

@communities = current_user.get_up_voted(Community)
@codes = Code.includes(:user).where(:community_id => @communities.collect(&:id)).order("users.active_at DESC").page(params[:page])

So, I’d like better performance with direct SQL query. But I have no idea how to write such complicated condition. How can I write SQL query to make this sophisticated and fast?

How do you write SQL query for this condition?

There are 4 tables such as below

  1. Users
    • id
    • active_at(date time)
  2. Votes
    • votable_id (this links to community.id)
    • voter_id (the id of user who bookmarked)
    • vote_flag (this has to be 1)
  3. Community
    • id
  4. Codes
    • community_id (this links to community.id)
    • user_id (this links to a user who created a code record)

User can bookmark Community by updating vote_flag of Votes table.

I’d like to fetch all the codes that belongs to communities that current_user has already bookmarked, and they have to be sorted by ‘user.active_at’ column.

Upvotes: 2

Views: 41

Answers (1)

Bla...
Bla...

Reputation: 7288

You just need to JOIN all those 4 tables and use ORDER BY to sort the records..

SELECT * FROM Users, Votes, Community, Codes 
WHERE Codes.community_id = Community_id 
AND Codes.user_id = Users.id
AND Votes.votable_id = Community.id
AND Votes.voter_id = Users.id
AND Votes.votable_id = Codes.community_id
AND Votes.voter_id = Codes.user_id
AND Votes.vote_flag = 1
ORDER BY USERS.active_at ASC

OR, for more readability:

SELECT * FROM Users
JOIN Codes ON Codes.user_id = Users.id
JOIN Community ON Codes.community_id = Community.id
JOIN Votes ON Votes.votable_id = Codes.community_id AND Votes.voter_id = Codes.user_id
WHERE vote_flag = 1
ORDER BY USERS.active_at ASC

Hope it helps..

Upvotes: 1

Related Questions