Why is ActiveRecord incorrectly translating SQL command?

Question

I believe thatActiveRecord::Querying is screwing up the translation of postgresql commands. The results are returning a different column then what I would expect. Can anybody help me figure out why?

THIS WORKS (POSTGRES)

SELECT 
quick_tests.id, quick_tests.status, name, 
started_at, array_agg(quick_test_agents.agent_id) as quick_test_agent_ids
FROM quick_tests
JOIN quick_test_agents ON quick_tests.id = quick_test_agents.quick_test_id
GROUP BY quick_tests.id
ORDER BY quick_tests.id desc;

I am trying to paginate all QuickTests by doing this: (quick_test to quick_test_agent is one to many)

tests = QuickTest.paginate :select => 'quick_tests.id,
                                       quick_tests.name,
                                       quick_tests.status,
                                       quick_tests.started_at,
                  array_agg(quick_test_agents.agent_id) as quick_test_agent_ids',
                           :page => current_page,
                           :per_page => page_size,
                           :joins => :quick_test_agent,
                           :group => 'quick_tests.id',

Now the problem is that QuickTest.paginate is not return quick_test_agents.agent_id, it is returning quick_test_agents.id.

Looking at the log print out, it seems that it is being translated incorrectly:

QuickTest Load (57.6ms)  SELECT quick_tests.id, quick_tests.name, quick_tests.status,
 quick_tests.started_at, array_agg(quick_test_agents.agent_id) as quick_test_agent_ids FROM "quick_tests" INNER JOIN "quick_test_agents" ON "quick_test_agents"."quick_test_id" = "quick_tests"."id" 
DEBUG    SQL (70.2ms)  SELECT "quick_test_agents".id FROM "quick_test_agents" WHERE "quick_test_agents"."quick_test_id" = 889
DEBUG    SQL (66.4ms)  SELECT "quick_test_agents".id FROM "quick_test_agents" WHERE "quick_test_agents"."quick_test_id" = 885
DEBUG    SQL (58.3ms)  SELECT "quick_test_agents".id FROM "quick_test_agents" WHERE "quick_test_agents"."quick_test_id" = 883
DEBUG    SQL (56.5ms)  SELECT "quick_test_agents".id FROM "quick_test_agents" WHERE "quick_test_agents"."quick_test_id" = 882
DEBUG    SQL (52.4ms)  SELECT "quick_test_agents".id FROM "quick_test_agents" WHERE "quick_test_agents"."quick_test_id" = 881
DEBUG    SQL (54.6ms)  SELECT "quick_test_agents".id FROM "quick_test_agents" WHERE  "quick_test_agents"."quick_test_id" = 880
DEBUG    SQL (52.6ms)  SELECT "quick_test_agents".id FROM "quick_test_agents" WHERE "quick_test_agents"."quick_test_id" = 879

It should be selecting quick_test_agents.agent_id

SELECT "quick_test_agents".agent_id FROM "quick_test_agents"...

Why is this happening?

Answer 1

The problem is with aliasing!

This statement aliases the array aggregation column as quick_test_agent_ids:

array_agg(quick_test_agents.agent_id) as quick_test_agent_ids

Now, this works in postgres but in ActiveRecord this is getting screwed up. Somehow the alias `quick_test_agent_ids' is causing ActiveRecord to believe it's actually looking for quick_test_agents.id.

If I change the alias statement it works:

    array_agg(quick_test_agents.agent_id) as quick_testSSS_agent_ids
    array_agg(quick_test_agents.agent_id) as agent_ids

TL;DR

Be careful with alias names, they might screw you up