rails using sql variables in find_by_sql

Question

I have this query:

SET @current_group = NULL; 
SET @current_count = 0; 
SELECT user_id, MIN( created_at ) as created_at, CASE WHEN @current_group = user_id THEN @current_count WHEN @current_group := user_id THEN @current_count := @current_count + 1 END AS c 
FROM notifies 
G    ROUP BY user_id, c 
ORDER BY id desc LIMIT 0 , 10

If i launch it it works

but if i put it in a find_by_sql method like:

Notify.find_by_sql("SET @current_group = NULL; SET @current_count = 0; SELECT user_id, MIN( created_at ) as created_at, CASE WHEN @current_group = user_id THEN @current_count WHEN @current_group := user_id THEN @current_count := @current_count + 1 END AS c FROM notifies GROUP BY user_id, c ORDER BY id desc LIMIT 0 , 10")

It returns this error:

Mysql2::Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SET @current_count = 0; SELECT user_id, MIN( created_at ) as created_at, CASE WH' at line 1:

How can i do?

thanks

I've never seen such a thing. Is this a proper MySQL query or some other dialect? — Wukerplank, Jun 20 '11 at 13:49

Groxx · Accepted Answer · 2012-03-02T00:25:43.093

It's because find_by_sql only works with a single statement.

Setting variables happen in separate statements. set specifically is in the Database Administration section, number 12.4.4, and select is in Data Manipulation section, number 12.2.7. Consoles (usually) allow multiple statements, and keep the variables around, but ActiveRecord queries do not.

To allow multiple statements, I think you have to maintain a persistent connection with the database, which Rails doesn't do (edit: normally). But I'm not certain about that - if anyone else knows, I'd love a more definite reason.

Edit: actually, I have a solution for you. Try this:

items = YourModel.transaction do
  YourModel.connection.execute("SET @current_group = NULL;")
  YourModel.connection.execute("SET @current_count = 0;")
  # this is returned, because it's the last line in the block
  YourModel.find_by_sql(%Q|
    SELECT user_id, MIN( created_at ) as created_at, CASE WHEN @current_group = user_id THEN @current_count WHEN @current_group := user_id THEN @current_count := @current_count + 1 END AS c 
    FROM notifies 
    GROUP BY user_id, c 
    ORDER BY id desc LIMIT 0 , 10
  |)
end

All those run in a single transaction, and any variables / settings inside the transaction block will persist between queries. You're still bound to a single statement per query though. There might be a way to do it without an actual transaction wrapping the whole set, but I haven't looked for it - most likely you want one, or you now have a very specific thing to look for if you know you don't.

Enjoy!

thenengah · Answer 2 · 2011-06-20T14:10:52.970

-2

Accordingly to the API it is this syntax:

Post.find_by_sql ["SELECT title FROM posts WHERE author = ? AND created > ?", author_id,  start_date]

So you might want to try putting it into an array instead of using parenthesis.

Notify.find_by_sql ["SET @current_group = NULL; SET @current_count = 0; SELECT user_id, MIN( created_at ) as created_at, CASE WHEN @current_group = user_id THEN @current_count WHEN @current_group := user_id THEN @current_count := @current_count + 1 END AS c FROM notifies GROUP BY user_id, c ORDER BY id desc LIMIT 0 , 10"]

edited Jun 20 '11 at 14:10

answered Jun 20 '11 at 13:53

thenengah

42,557
33
113
157

Sam @current_group and @current_count aren't ruby variables but MYSQL variables. if i launch this in phpmyadmin it works. But with rails find_by_sql method no. So the question is: how can i do that? – Luca Romagnoli Jun 20 '11 at 13:57
oh I see. Just glossed over it. – thenengah Jun 20 '11 at 14:06

rails using sql variables in find_by_sql

2 Answers2