Need Counter Cache from Bullet Gem

Counting data is something we normally do. We count cart items in e-commerce websites, answers to questions in question and answer websites. Bullet is a gem that helps you increase your application’s performance by reducing the number of queries it makes.

Here is an example of a request that has a N+1 issue

Question Load (123.0ms)  SELECT "questions".* FROM "questions"
[active_model_serializers]    (0.9ms)  SELECT COUNT(*) FROM "answers" WHERE "answers"."question_id" = $1  [["question_id", 9]]
[active_model_serializers]       app/serializers/question_serializer.rb:7
[active_model_serializers]    (3.6ms)  SELECT COUNT(*) FROM "answers" WHERE "answers"."question_id" = $1  [["question_id", 2]]
[active_model_serializers]       app/serializers/question_serializer.rb:7

Bullet will show this in the log as:

[active_model_serializers]       app/serializers/question_serializer.rb:7
user: theuser
GET /api/v1/questions
Need Counter Cache
  Question => [:answers]

Need Counter Cache

Here is how to do caching counters with ActiveRecord’s counter caches.

To create a counter cache, we should add a field to the parent table. In this case, it is the question table.

class AddAnswersCountToQuestions < ActiveRecord::Migration[5.2]
  def change
    add_column :questions, :answers_count, :integer
  end
end

When we request from our application, we can see that only one query is applied

Started GET "/api/v1/questions" for ::1 at 2019-08-31 09:13:55 +0800
Processing by Api::V1::QuestionsController#index as JSON
  Question Load (8.5ms)  SELECT "questions".* FROM "questions"
    app/controllers/api/v1/questions_controller.rb:8
[active_model_serializers] Rendered ActiveModel::Serializer::CollectionSerializer with ActiveModelSerializers::Adapter::Attributes (6.34ms)
Completed 200 OK in 84ms (Views: 61.6ms | ActiveRecord: 18.8ms)

 Counting Rows in MongoDB

How do you count rows in MongoDB? It is easy to count records.

Use the count method

> db.books.find().count()
4
> db.books.find({"title": "Book"}).count()
0
> db.books.find({"title": "Book Title"}).count()
1
> db.books.find({"category": "Fiction"}).count()
2

You can also you multiple key terms on find

> db.books.find({"category": "Fiction", "title": "Book Title"}).count()
1

 Sorting Rows in MongoDB

Sorting is a fundamental action done with databases. MongoDB has a similar function that works great.

Sort by ascending order

Use 1 as the value to sort in ascending order

> db.books.find().sort({title: 1})
{ "_id" : ObjectId("5d5e1c357545de885fa391fb"), "title" : "Book Title", "category" : "Fiction", "tags" : [ "children", "fantasy" ], "user" : { "name" : "Sophia", "status" : "author" }, "date" : "Thu Aug 22 2019 12:38:12 GMT+0800 (PST)" }
{ "_id" : ObjectId("5d5e21467545de885fa391fc"), "title" : "Book Title 2", "category" : "Fiction", "tags" : [ "teen", "fantasy" ], "user" : { "name" : "Ryan", "status" : "author" }, "date" : "Thu Aug 22 2019 12:59:50 GMT+0800 (PST)" }
{ "_id" : ObjectId("5d5e21a47545de885fa391fd"), "title" : "Book Title 3", "category" : "Non-Fiction", "tags" : [ "history", "documentary" ], "user" : { "name" : "Arthur", "status" : "author" }, "date" : "Thu Aug 22 2019 13:01:24 GMT+0800 (PST)" }
{ "_id" : ObjectId("5d5e21a47545de885fa391fe"), "title" : "Book Title 4", "category" : "Non-Fiction", "tags" : [ "legal" ], "user" : { "name" : "Manuel", "status" : "author" }, "date" : "Thu Aug 22 2019 13:01:24 GMT+0800 (PST)" }

Sort by descending order

Use -1 as the value to sort by descending order

> db.books.find().sort({title: -1})
{ "_id" : ObjectId("5d5e21a47545de885fa391fe"), "title" : "Book Title 4", "category" : "Non-Fiction", "tags" : [ "legal" ], "user" : { "name" : "Manuel", "status" : "author" }, "date" : "Thu Aug 22 2019 13:01:24 GMT+0800 (PST)" }
{ "_id" : ObjectId("5d5e21a47545de885fa391fd"), "title" : "Book Title 3", "category" : "Non-Fiction", "tags" : [ "history", "documentary" ], "user" : { "name" : "Arthur", "status" : "author" }, "date" : "Thu Aug 22 2019 13:01:24 GMT+0800 (PST)" }
{ "_id" : ObjectId("5d5e21467545de885fa391fc"), "title" : "Book Title 2", "category" : "Fiction", "tags" : [ "teen", "fantasy" ], "user" : { "name" : "Ryan", "status" : "author" }, "date" : "Thu Aug 22 2019 12:59:50 GMT+0800 (PST)" }
{ "_id" : ObjectId("5d5e1c357545de885fa391fb"), "title" : "Book Title", "category" : "Fiction", "tags" : [ "children", "fantasy" ], "user" : { "name" : "Sophia", "status" : "author" }, "date" : "Thu Aug 22 2019 12:38:12 GMT+0800 (PST)" }

 Finding a Row in MongoDB

MongoDB has a find method that works absolutely like SQL’s WHERE.

This is how you show rows in MongoDB

> db.books.find()
{ "_id" : ObjectId("5d5e1c357545de885fa391fb"), "title" : "Book Title", "category" : "Fiction", "tags" : [ "children", "fantasy" ], "user" : { "name" : "Sophia", "status" : "author" }, "date" : "Thu Aug 22 2019 12:38:12 GMT+0800 (PST)" }
{ "_id" : ObjectId("5d5e21467545de885fa391fc"), "title" : "Book Title 2", "category" : "Fiction", "tags" : [ "teen", "fantasy" ], "user" : { "name" : "Ryan", "status" : "author" }, "date" : "Thu Aug 22 2019 12:59:50 GMT+0800 (PST)" }
{ "_id" : ObjectId("5d5e21a47545de885fa391fd"), "title" : "Book Title 3", "category" : "Non-Fiction", "tags" : [ "history", "documentary" ], "user" : { "name" : "Arthur", "status" : "author" }, "date" : "Thu Aug 22 2019 13:01:24 GMT+0800 (PST)" }
{ "_id" : ObjectId("5d5e21a47545de885fa391fe"), "title" : "Book Title 4", "category" : "Non-Fiction", "tags" : [ "legal" ], "user" : { "name" : "Manuel", "status" : "author" }, "date" : "Thu Aug 22 2019 13:01:24 GMT+0800 (PST)" }

If you only want to find books with the title, Book Title 4, you should add a parameter to the find method

> db.books.find({title: "Book Title 4"})
{ "_id" : ObjectId("5d5e21a47545de885fa391fe"), "title" : "Book Title 4", "category" : "Non-Fiction", "tags" : [ "legal" ], "user" : { "name" : "Manuel", "status" : "author" }, "date" : "Thu Aug 22 2019 13:01:24 GMT+0800 (PST)" }
> db.books.find({category: "Non-Fiction"})
{ "_id" : ObjectId("5d5e21a47545de885fa391fd"), "title" : "Book Title 3", "category" : "Non-Fiction", "tags" : [ "history", "documentary" ], "user" : { "name" : "Arthur", "status" : "author" }, "date" : "Thu Aug 22 2019 13:01:24 GMT+0800 (PST)" }
{ "_id" : ObjectId("5d5e21a47545de885fa391fe"), "title" : "Book Title 4", "category" : "Non-Fiction", "tags" : [ "legal" ], "user" : { "name" : "Manuel", "status" : "author" }, "date" : "Thu Aug 22 2019 13:01:24 GMT+0800 (PST)" }

You can use multiple keys to search for

> db.books.find({category: "Non-Fiction", title: "Book Title 3"})
{ "_id" : ObjectId("5d5e21a47545de885fa391fd"), "title" : "Book Title 3", "category" : "Non-Fiction", "tags" : [ "history", "documentary" ], "user" : { "name" : "Arthur", "status" : "author" }, "date" : "Thu Aug 22 2019 13:01:24 GMT+0800 (PST)" }