CODEWITHSUNDEEP
mysqlruby-on-rails-3postgresqlbooleancoalesce
fro_oo
fro_oo

Reputation: 1610

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

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.

Upvotes: 0

Views: 1354

Answers (2)

fro_oo
fro_oo

Reputation: 1610

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

Upvotes: 1

mu is too short
mu is too short

Reputation: 434585

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.

Upvotes: 0

Related Questions