CODEWITHSUNDEEP
ruby-on-railsrubycount
PEF
PEF

Reputation: 973

Complex count in ruby

I would like to identify and count the posts that have a specific number of messages and a post.location that is not nil.

I have approximately 100k rows and it is for a statistical use so I need fast queries (I might have to index post.location by the way).

How can I do it the easiest and quickest way?

Here is my schema : 

create_table "posts", :force => true do |t|
  t.string   "ref"
  t.string   "title"
  t.string   "author"
  t.datetime "created_at"
  t.datetime "updated_at"
  t.string   "location"
  t.float    "lat"
  t.float    "long"
end

create_table "messages", :force => true do |t|
  t.integer  "post_id"
  t.integer  "status_id"
  t.integer  "user_id"
  t.string   "content"
  t.datetime "created_at"
  t.datetime "updated_at"
  t.float    "lat"
  t.float    "long"
end

add_index "messages", ["post_id"], :name => "index_messages_on_post_id"
add_index "messages", ["created_at"], :name => "index_messages_on_created_at"

Upvotes: 2

Views: 99

Answers (3)

Tigraine
Tigraine

Reputation: 23648

The SQL Query you are looking for should look a lot like this:

SELECT COUNT(posts.id)
FROM posts
JOIN messages ON (posts.id = messages.post_id)
GROUP BY (posts.id) HAVING (count(messages.id) > ?)
WHERE posts.location IS NOT NULL

You can then convert that to a ActiveRecord Query or just use find_by_sql. (I wrote this from memory so you might have to tweak it in some places .. but the general idea should work).

Upvotes: 3

PEF
PEF

Reputation: 973

OK I managed to get through it thanks to both Tigraine's and az7ar's answers. Here's what worked out:

Post.joins(:messages).select('post_id as id').group("post_id").having("count(*) = 1").where('location IS NOT NULL')

Thank you Tigraine for the JOIN advice and az7ar for the .where("location IS NOT NULL")syntax.

Upvotes: 0

az7ar
az7ar

Reputation: 5237

You can use this scope in you Post model

scope :give_a_valid_name, lambda {|n| joins(:messages).having("COUNT(messages.id) = #{n}").where("location IS NOT NULL")}

Then use Post.give_a_valid_name(5).size to get the number of posts that have 5 messages.

Hope this helps.

Upvotes: 1

Related Questions