Need to convert a Boolean from Postgres (== String) to a Ruby Boolean

Question

I'm using Postgres with Rails. There's a query with a subselect which returns a boolean, but Postgres always returns a String like 't' or 'f'. But in the generated JSON I need a real boolean.

This is my query:

SELECT
  *,
  EXISTS (
    SELECT TRUE
    FROM measurement
    JOIN experiment ON measurement.experiment_id = experiment.id
    JOIN location ON experiment.location_id = location.id
    WHERE location.map_id = map.id
    LIMIT 1
  ) AS measurements_exist
FROM "map"

It doesn't matter whether I use THEN true or THEN 1 or THEN 'true', I will always get a string. So my JSON response will always look like that:

[
  {"id":8, ..., "measurements_exist":"f"},
  {"id":9, ..., "measurements_exist":"t"}
]

But it should(!) look like that:

[
  {"id":8, ..., "measurements_exist":false},
  {"id":9, ..., "measurements_exist":true}
]

Is there any way to get this working right?

Thank you!

THE SOLUTION:

Just give the corresponding model (here: Map) an attribute accessor, which uses value_as_boolean to convert the value. So every time the controller tries to access the value, it uses the attribute accessor method automatically.

The controller code:

class MapsController < ApplicationController
  def index
    select = ["*"]
    select.push(measurements_exist) # This will just insert the string returned by the 'measurements_exist' method
    maps = Map.select(select) # Results in 'SELECT *, EXISTS (...) AS measurements_exist FROM "map"'
    render json: maps
  end

  private

  def measurements_exist
    "EXISTS (
      SELECT TRUE
      FROM measurement
      JOIN experiment ON measurement.experiment_id = experiment.id
      JOIN location ON experiment.location_id = location.id
      WHERE location.map_id = map.id
      LIMIT 1
    ) AS measurements_exist"
  end
end

The model code:

class Map < ActiveRecord::Base
  def measurements_exist
    ActiveRecord::ConnectionAdapters::Column.value_to_boolean(self[:measurements_exist])
  end
end

Resulting JSON:

[
  {"id":7, ..., "measurements_exist":false},
  {"id":6, ..., "measurements_exist":true}
]

score 4 · Accepted Answer · answered Feb 17 '13 at 12:43

4

ActiveRecord has a method called ActiveRecord::ConnectionAdapters::Column.value_to_boolean it uses internally to convert any true-like value to a Ruby true value.

You can use it in your code.

answered Feb 17 '13 at 12:43

Simone Carletti

173,507
49
363
364

1

I googled around for `value_to_boolean` and combined it with an attribute accessor in my model and it works perfectly!! I'll put the solution at the bottom of my question. – Benjamin M Feb 17 '13 at 14:31
[deprecated from Rails 4.2] – Evgenia Karunus Mar 18 '16 at 21:46
Now that `value_to_boolean` has been deprecated you can use `ActiveRecord::Type::Boolean.new.cast(value)` – Alberto T. Mar 15 '20 at 17:59

Aleksei Chernenkov · Answer 2 · 2013-02-17T13:10:23.583

0

When you query model from Rails its boolean fields converted to true/false automatically because DB adapter can determine type of field from schema. If you select custom boolean field from db - adapter doesn't know anything about it, so it returned string 't' or 'f' and you need to convert it manually.

One of the ways to get expected boolean value:

Create the view with provided SQL-query on the DBMS side (e.g. see CREATE VIEW ... statement for PostgreSQL). Views fields have types so boolean fields will be converted in your app automatically. Suppose its called map_with_measurements.
Create model MapWithMeasurement and place it in models/map_with_measurement.rb. class MapWithMeasurement < ActiveRecord::Base; end
Use MapWithMeasurement.find_all.

edited Feb 17 '13 at 13:10

answered Feb 17 '13 at 12:23

Aleksei Chernenkov

991
1
8
23

That's bad. I even tried do use CAST inside the SQL Query, but it still gives me the string representation. So what would be the way to convert it manually? Iterate over the whole result set and replace the strings with booleans? Or is there a way that's more nice? Or can I somehow add some kind of virtual attribute to my model that tells Rails to convert it to boolean? – Benjamin M Feb 17 '13 at 12:26
Hmm... May be you can try to define [abstract model](http://blog.chrisblunt.com/rails-abstract-models-activerecord-without-tables/) with all needed fields, then query such model with [find_by_sql](http://api.rubyonrails.org/classes/ActiveRecord/Querying.html#method-i-find_by_sql). But I dont tried to do so and don't sure it'll work as I expect. – Aleksei Chernenkov Feb 17 '13 at 12:45
I found not so bad solution with database views. See edited answer. – Aleksei Chernenkov Feb 17 '13 at 13:04

score 0 · Answer 3 · answered Oct 30 '14 at 01:21

You can use wannabe_bool gem. https://github.com/prodis/wannabe_bool

This gem implements a #to_b method for String, Integer, Symbol and NilClass classes.

class Map < ActiveRecord::Base
  def measurements_exist
    self[:measurements_exist].to_b
  end
end

emery · Answer 4 · 2016-03-25T22:07:35.770

Here is another solution:

boolean = (value_from_postgres =~ /^t$/i) == 0

converts a value_from_postgres of 't' to boolean true or 'f' to boolean false

$irb
2.2.1 :001 > value_from_postgres = 't'
 => "t" 
2.2.1 :002 > (value_from_postgres =~ /^t$/i) == 0
 => true 
2.2.1 :003 > value_from_postgres = 'f'
 => "f" 
2.2.1 :004 > (value_from_postgres =~ /^t$/i) == 0
 => false

The regular expresion in this line could be modified to match /^true$/i if you are expecting a string "true" or "false". This is more flexible than using a ternary or a gem, because you can write it to convert a match to any regex to a boolean true.

Using a ternary it looks like:

boolean = value_from_postgres.eql?('t') ? true : false

Need to convert a Boolean from Postgres (== String) to a Ruby Boolean

THE SOLUTION:

4 Answers4

Linked