0

Is there a way to do a single query to do both a select and a count, and get back an array with the two values? For example:

posts = Post.where(category = ?, 'somecategory').limit(10)
count = Post.where(category = ?, 'somecategory').count

Edit:

data = Post.find_by_sql(["SELECT count(category) AS total_count, * FROM posts WHERE category = ? GROUP BY posts.id LIMIT 10",'somecategory'])
Jack Rothrock
  • 407
  • 1
  • 8
  • 21

2 Answers2

0

I found this to work:

data = Post.find_by_sql(["SELECT count(*) OVER (), p.* FROM posts p WHERE category = ? GROUP BY p.id LIMIT 10",'somecategory'])

the issue with this though, is that it puts the count in each post. So to get the count you would have to do: count = data.first.count. If anyone stumbles upon this, I think that this will probably be your next destination: Rails, how to sanitize SQL in find_by_sql.

Jack Rothrock
  • 407
  • 1
  • 8
  • 21
0

I think this is what you want:

Post.where('category = ?', 'somecategory').select('id, count(id) as total_count').group(:id).limit(10)

The single query returns an array of Post objects with total_count as their attribute.

Trang Tung Nguyen
  • 316
  • 4
  • 14
  • I'm only getting 1 returned when I try this. I'm also trying to do a full select, so it would be something like `Post.where('category = ?', 'somecategory').select('*, count(*) as total_count').group(:id).limit(10)`. On a side note, this is nice little hack to add a column that doesn't need to be in the DB - without having the do something like `as_json.merge!` – Jack Rothrock Mar 24 '17 at 18:52