Same query on Rails and SQL return different results

Question

I need help in converting this SQL to ActiveRecord query.

SELECT 
  MAX(total_sales.start_date) AS maximum_start_date,
  customers.external_id, product_categories.external_id AS product_category_id
FROM
  total_sales
INNER JOIN 
  customers
ON
  customers.id = total_sales.customer_id
LEFT JOIN
  product_categories
ON
  product_categories.id = total_sales.product_category_id
WHERE 
  (customers.organization_id = 1)
GROUP BY
  customers.external_id,
  product_categories.external_id

I tried doing

TotalSales.joins(:customer).includes(:product_category).
  where("customers.organization_id = ?", 1).
  group("customers.external_id, product_categories.external_id").
  maximum(:start_date)

and it generates almost the query I want. This is what it generates:

SELECT
  MAX("total_sales"."start_date") AS maximum_start_date, 
  customers.external_id, product_categories.external_id AS customers_external_id_product_categories_external_id
FROM
  "total_sales"
INNER JOIN "customers" ON
  "customers"."id" = "total_sales"."customer_id"
LEFT OUTER JOIN "product_categories" ON
  "product_categories"."id" = "total_sales"."product_category_id" 
WHERE
  (customers.organization_id = 1)
GROUP BY
  customers.external_id, product_categories.external_id

But this returns on Rails:

=> {"DIA"=>Wed, 01 Jan 2014, "MES"=>Wed, 01 Jan 2014, nil=>Wed, 01 Jan 2014}

If I run that query on DB console, it returns

"2014-01-01";"CLIENTE.1";"DIA"
"2014-01-01";"CLIENTE.1";"MES"
"2014-01-01";"CLIENTE.1";""
"2014-01-01";"CLIENTE.10";"DIA"
"2014-01-01";"CLIENTE.10";"MES"
"2014-01-01";"CLIENTE.10";""
"2014-01-01";"CLIENTE.100";"DIA"
"2014-01-01";"CLIENTE.100";"MES"
...

and this is what I want. On DB console it works, but on Rails it doesn't. :(

Here is an example of the working query: http://sqlfiddle.com/#!9/7a6d1/1/0. The same doesn't work on Rails.. — João Daniel, May 21 '15 at 18:54
guess it's the OUTER LEFT JOIN vs LEFT JOIN that's generating the behavior. Look at: http://stackoverflow.com/questions/8025429/how-can-i-do-a-left-outer-join-using-rails-activerecord Also look at: http://www.codeproject.com/Articles/33052/Visual-Representation-of-SQL-Joins One way might be to drop the where and to filter after you get the records, depending on the record set size — Mircea, May 21 '15 at 18:56
Both queries with or withour the OUTER produces the desired output on DB console. It seems the problem is how AR is transforming it on a hash. — João Daniel, May 21 '15 at 19:14
I believe it's related to the fact that both group by columns have the same name.. — João Daniel, May 21 '15 at 19:17

score 0 · Answer 1 · answered Oct 08 '15 at 23:11

It seems to be a bug in Rails. If you change your line with the group from:

group("customers.external_id, product_categories.external_id").

to:

group("customers.external_id", "product_categories.external_id").

it works. NB! that you should use two strings separated with comma.

Must be when Rails are rendering the result from SQL it thinks that the group is only one field if it's in one string, although it builds a correct SQL query.

You will have this result instead:

=> {["CLIENTE.1", "DIA"]=>Wed, 01 Jan 2014, ["CLIENTE.1", "MES"]=>Wed, 01 Jan 2014,
    ["CLIENTE.1", nil]=>Wed, 01 Jan 2014, ["CLIENTE.10", "DIA"]=>Wed, 01 Jan 2014,
    ["CLIENTE.10", "MES"]=>Wed, 01 Jan 2014, ["CLIENTE.10", nil]=>Wed, 01 Jan 2014, ...}

I assume that you know how to use it.

A little tip, use Arel to avoid "hard code" table names, you can use this:

cust = Customer.arel_table
prod = ProductCategory.arel_table
TotalSales.joins(:customer).includes(:product_category).
  where(cust[:organization_id].eq(1)).
  group(cust[:external_id], prod[:external_id]).
  maximum(:start_date)

score 0 · Answer 2 · edited May 23 '17 at 12:29

includes != LEFT JOIN

You should not use includes(othertable) when you want your query somehow depend on othertable, as includes it has a different semantic.

With includes you just say ActiveRecord that you intent to get the objects from othertable soon, so it should optimize for it. But it is object-oriented. You get TotalSales objects in your example.

A join is a relational database thing, where the column of tables can be mixed. We normally do not do such things in object world. That why this often gets difficult.

If you need a left join, you need to tell rails so. According to the rails guide, you do need to use a SQL fragment.

TotalSales.joins('LEFT OUTER JOIN product_categories ON product_categories.id = total_sales.product_category_id')
...

See also my answer to this question: Rails 4 Relation is not being sorted: 'has_many' association w/ default scope, 'includes' and 'order' chained together

Same query on Rails and SQL return different results

2 Answers2