Rabl doing duplicated database calls?

[NielsKSchjoedt]

I want to do something similar to this https://github.com/nesquena/rabl/wiki/Tips-and-tricks but my problem is, that it seems that rabl makes duplicated calls to the database. Since some of the request can be quite heavy (due to some geo-stuff), it's a deal breaker for me.

I have simplified the code for this example:

I'm constructing an API that should return a set of cars. I want the format to look like the following:

{

"hits_count": 2,
"cars": [
    {
        "car": {
            "id": 295655,
            "brand": "Citroën",
            "model_name": "Saxo",
            "fuel": "Diesel",
            "km": 245000,
            "year": 2002
        }
    },
    {
        "car": {
            "id": 295656,
            "brand": "Citroën",
            "model_name": "Saxo",
            "fuel": "Diesel",
            "km": 234000,
            "year": 2001
        }
    }
 ]

}

Right now I'm doing it in my /index by saying:
object false
node(:hits_count) { @cars_in_search }
child @cars do
extends "api/v1/cars/car"
end

in the /car it says:
attributes :id, :brand, :model_name, :fuel, :km, :year

the @cars is a query selecting the cars, and the @cars_in_search is just a simple count

This gives me the correct format as I want, but it makes duplicate calls to the database as you can see:
(238.7ms) SELECT COUNT() FROM "cars" WHERE "cars"."sales_state" = 'onsale' AND "cars"."estimate_state" = 'valid'
(1.2ms) SELECT COUNT(count_column) FROM (SELECT 1 AS count_column FROM "cars" WHERE "cars"."sales_state" = 'onsale' AND "cars"."estimate_state" = 'valid' LIMIT 20 OFFSET 0) subquery_for_count
Car Load (281.4ms) SELECT "cars". FROM "cars" WHERE "cars"."sales_state" = 'onsale' AND "cars"."estimate_state" = 'valid' ORDER BY cars.au_rating DESC, cars.price_difference DESC LIMIT 1 OFFSET 0
Car Load (291.7ms) SELECT "cars".* FROM "cars" WHERE "cars"."sales_state" = 'onsale' AND "cars"."estimate_state" = 'valid' ORDER BY cars.au_rating DESC, cars.price_difference DESC LIMIT 20 OFFSET 0

If instead I do the following in my /index view:
collection @cars
extends "api/v1/cars/car"

Then it's not doing duplicate requests ?????? strange:
(228.6ms) SELECT COUNT() FROM "cars" WHERE "cars"."sales_state" = 'onsale' AND "cars"."estimate_state" = 'valid'
Car Load (286.9ms) SELECT "cars". FROM "cars" WHERE "cars"."sales_state" = 'onsale' AND "cars"."estimate_state" = 'valid' ORDER BY cars.au_rating DESC, cars.price_difference DESC LIMIT 20 OFFSET 0

But then the problem of cause is, that I'm not getting the format that I want (as described before)

Hope you can give me a clue as to what to do.

I'm running rails 3.1 and latest rabl. It's the same in both development and production.

[nesquena]

Try this:

object false
node(:hits_count) { @cars_in_search }
child @cars.to_a do
extends "api/v1/cars/car"
end

alternately:

cars = @cars.to_a
object false
node(:hits_count) { @cars_in_search }
child cars do
extends "api/v1/cars/car"
end

The issue is how relation scopes work. Does this work?

[NielsKSchjoedt]

You are the man!!! This did the trick - thanks for a fantastic gem :-D

[nesquena]

Glad this worked, great to hear it.

[yujingz]

@nesquena can any one explains this a little bit more about this issue. I fixed same problem but don't quite understand. What's the differences between :property and @object.property.to_a. Why would the :property produces another limit = 1 query. Thanks!

[kubenstein]

Solution with to_a doesnt work for me. I put .all at the end of query building chain.

[mrbrdo]

Huh this is pretty bad, I don't understand how it's being treated so lightly... Basically RABL is making an extra SQL for every collection view? That's very sucky, and the solution proposed (to_a/all) even more. Isn't there something that can be done? I'm getting 2 queries, first LIMIT 1 then the normal one.

[nesquena]

Which ORM are you guys using? ActiveRecord it sounds like?

[nesquena]

Also can you guys post specific examples? I use RABL in dozens of projects and I don't run into this problem, if I did I would have probably fixed it by now. Specific examples of the issue and patches welcome.

[mrbrdo]

Yes ActiveRecord. Example:

# controller
def index
  @stuff = Stuff.where("1 = 1") # to avoid any confusion with Rails 4 #all
end

# index.json.rabl
collection @stuff
extends 'whatever'

Does a LIMIT 1 query first. Fix:

# controller
def index
  @stuff = Stuff.where("1 = 1").to_a
end

I'm using Rails 4, btw.

[nesquena]

I see. Try this:

collection @stuff => :items
extends 'whatever'

without the "to_a" and see if it goes away. The issue I think is you need to provide the name for the key otherwise it tries to get the name by inspecting the type of the item. The way scopes work is that when the name is determined it kicks another query.

[mrbrdo]

nope, still does the limit query even with that (also I am not using json roots anyway)

[mrbrdo]

@nesquena interesting fact: even if I remove everything from the rabl file, it will still do both the queries (LIMIT 1, and then for all), but the result I get is then [ {}, {}, ... ] the count matches the number of results from the query
my (rack-mini-)profiler shows the query is coming from the rabl file (line 1)

[nesquena]

Ok thanks for the follow up, I'll do some digging into this as soon as I can. I'm sure it's going to be an easy fix, the key is tracking down the culprit to that extra call (since it doesn't appear to be naming the key for this one)

[nesquena]

I am curious if you go to the rails console and do:

> @stuff = Stuff.where("1 = 1")
> @stuff.respond_to?(:first)

I assume no query is kicked right?