Why is find_by_sql not working with PostgreSQL?

Question

I have a PostgreSQL query that sums values by month, extracting the month value from a date:

SELECT vendors.name AS vendor, 
SUM(purchases.invoice_total) AS invoice_total, 
EXTRACT(MONTH FROM purchases.date) AS date 
FROM vendors, purchases 
WHERE purchases.vendor_id = vendors.id 
AND purchases.store_id = ? 
AND purchases.purchase_department_id = ? 
AND (purchases.date BETWEEN ? AND ?) 
GROUP BY vendor, EXTRACT(MONTH FROM purchases.date) 
ORDER BY vendor, date;

After executing the query directly in the DB console, I get something like this:

+------------------------------------+---------------+------+
| vendor                             | invoice_total | date |
+------------------------------------+---------------+------+
| Store 1                            |        448.56 |    1 |
| Store 1                            |        263.44 |    2 |
| Store 1                            |        939.47 |    3 |

The problem comes when using Rails' find_by_sql function. The vendor and invoice_total values get returned correctly, but the date values get returned as nil.

#<Purchase date: nil, invoice_total: #<BigDecimal:85c75c8,'0.26344E3',18(18)>>

This is my call. I've even tried with hardcoded values and nothing, but it's like the Extract function won't execute in Rails:

<% @purchasesSQL = Purchase.find_by_sql(["SELECT vendors.name AS vendor, SUM(purchases.invoice_total) AS invoice_total, 
    EXTRACT(MONTH FROM purchases.date) AS date 
    FROM vendors, purchases 
    WHERE purchases.vendor_id = vendors.id 
    AND purchases.store_id = ? 
    AND purchases.purchase_department_id = ? 
    AND (purchases.date BETWEEN ? AND ?) 
    GROUP BY vendor, EXTRACT(MONTH FROM purchases.date) 
    ORDER BY vendor, date;", @store.id, purchase_department.id, @start, @end])
%>

I've already done some debugging and the values I'm passing are not nil nor wrong.

purchases table has (id, purchase_amount, vendor_id, store_id, invoice_total, department_id, date, create and modify timestamps) and some other boolean attriubtes. — cero2k
– cero2k, Commented Jun 19, 2013 at 6:23

mu is too short · Accepted Answer · 2013-06-19 06:35:03Z

2

You say that you have a column named date in your Purchase model's table (that's a terrible name for column BTW, using type names for column names just leads to pain and suffering). Presumably your date column is a date or timestamp. In your Purchase.find_by_sql, you have this:

EXTRACT(MONTH FROM purchases.date) AS date

and Purchase.find_by_sql will be trying to create Purchase instances. So you're giving Purchase a simple number and calling it date, Purchase probably notices that it has a date column and tries to parse your simple number as a full date or timestamp. A simple number cannot be parsed as a date or timestamp in any meaningful fashion so they're probably all get converted to nil.

You have two things to do here:

Renamed that date column to something else, something that doesn't conflict with any keywords.
And to hopefully fix your immediate problem, use a different alias for your month number:
```
EXTRACT(MONTH FROM purchases.date) AS month_number
```
That should get you a month_number method on the returned Purchase objects.

answered Jun 19, 2013 at 6:35

mu is too short

436k71 gold badges863 silver badges822 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

cero2k Over a year ago

Renaming date for something else indeed fixed the problem. Thanks! This was my first question on stackoverflow, it delivered! Now it actually makes sense on why it was working on the console and not the app, the console doesn't create objects!

Collectives™ on Stack Overflow

Why is find_by_sql not working with PostgreSQL?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related