How to retrieve BOOLEANS with a custom sql query in Rails 3 + Postgres?

Question

In my model I've made this custom sql query :

SELECT
    training_courses.id,
    training_courses.training_id,
    training_courses.course_id,
    training_courses.is_pre_test, 
    training_courses.order_by,
    training_course_histories.id AS training_course_history_id, 
    training_course_histories.finished_at,
    training_course_histories.score,
    CAST(coalesce(user_training_courses.id, 0) as BOOLEAN) as purchased
FROM
    training_courses
    LEFT OUTER JOIN training_course_histories
        ON training_course_histories.training_course_id = training_courses.id AND training_course_histories.id = (
            SELECT th1.id
            FROM training_course_histories th1      
            WHERE th1.training_course_id = training_courses.id
              AND th1.finished_at IS NOT NULL
              AND th1.user_id = 1
            ORDER BY th1.finished_at DESC LIMIT 1
        )
    LEFT OUTER JOIN user_training_courses
        ON user_training_courses.training_course_id = training_courses.id AND user_training_courses.id = (
            SELECT th2.id
            FROM user_training_courses th2          
            WHERE th2.training_course_id = training_courses.id 
              AND th2.user_id = 1
        )
WHERE (training_courses.training_id = 1)
GROUP BY
    training_courses.id,
    training_courses.id,
    training_courses.training_id,
    training_courses.course_id,
    training_courses.is_pre_test, 
    training_courses.order_by,
    training_course_histories.id,
    training_course_histories.finished_at,
    training_course_histories.score, 
    user_training_courses.id
ORDER BY 
    order_by ASC, 
    id ASC, 
    training_courses.order_by ASC, 
    training_courses.id ASC

Before, I was using a Mysql database so, no problem at all, the query was like : ISNULL(user_training_courses.id) as purchased

But now, I'm using Postgres and I must have CAST(coalesce(user_training_courses.id, 0) as BOOLEAN) as purchased

The problem: for Rails, purchased = 't' or 'f' (strings)

What I want : purchased = true or false (booleans)

Is it possible ?

Thx.

The CAST as boolean is simply not working... :-(

fro_oo
– fro_oo

2011-09-09 14:37:07 +00:00
Commented Sep 9, 2011 at 14:37 — fro_oo
– fro_oo, Commented Sep 9, 2011 at 14:37

fro_oo · Accepted Answer · 2011-09-12 09:30:52Z

1

Finally, I rather have an accessor in my model to handle this case.

def purchased
  ![false, 'false', 'f', 'FALSE', 'F', 0, '0'].include?(self[:purchased])
end

answered Sep 12, 2011 at 9:30

fro_oo

1,6306 gold badges24 silver badges47 bronze badges

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

1 Comment

mu is too short Over a year ago

You'd be better off with ActiveRecord::ConnectionAdapters::Column.value_to_boolean.

mu is too short · Accepted Answer · 2011-09-10 00:00:45Z

0

The Rails PostgreSQL driver understands that PostgreSQL booleans come out as 't' and 'f' and usually translates them properly. Your CAST:

CAST(coalesce(user_training_courses.id, 0) as BOOLEAN)

should also be fine:

psql=> select cast(11 as boolean), cast(1 as boolean), cast(0 as boolean);
 bool | bool | bool 
------+------+------
 t    | t    | f

So I'd guess that Rails just doesn't know the column type of your CAST. For example:

rails > ActiveRecord::Base.connection.select_rows('select cast(1 as boolean), 11 = 11')
 => [["t", "t"]]

You can convert the 't' and 'f' to booleans yourself using the Set constants in ActiveRecord::ConnectionAdapters::Column:

TRUE_VALUES = [true, 1, '1', 't', 'T', 'true', 'TRUE'].to_set
FALSE_VALUES = [false, 0, '0', 'f', 'F', 'false', 'FALSE'].to_set

or you can use ActiveRecord::ConnectionAdapters::Column.value_to_boolean:

rails > ActiveRecord::Base.connection.select_rows('select cast(1 as boolean), 11 = 11').map { |r| r.map { |b| ActiveRecord::ConnectionAdapters::Column.value_to_boolean(b) } }
 => [[true, true]]

But you'll still have to know what types all your columns are and sort things out by hand if you're going to use the low level SQL interface. Similar issues apply to integers, dates, and any other non-string types that you get back.

edited Sep 10, 2011 at 0:00

answered Sep 9, 2011 at 17:15

mu is too short

436k71 gold badges863 silver badges822 bronze badges

1 Comment

fro_oo Over a year ago

Thank you, this is a nice solution. I understand that I'll have similar issues with other types... so I choose another way to convert 'f' and 't' to booleans.

Collectives™ on Stack Overflow

How to retrieve BOOLEANS with a custom sql query in Rails 3 + Postgres?

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related