CODEWITHSUNDEEP
ruby-on-railsruby-on-rails-3postgresqlruby-on-rails-3.2rails-activerecord
Benjamin M
Benjamin M

Reputation: 24527

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

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}
]

Upvotes: 1

Views: 7425

Answers (4)

emery
emery

Reputation: 9653

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

Upvotes: 0

Prodis
Prodis

Reputation: 514

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

Upvotes: 0

Aleksei Chernenkov
Aleksei Chernenkov

Reputation: 1051

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:

  1. 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.

  2. Create model MapWithMeasurement and place it in models/map_with_measurement.rb. class MapWithMeasurement < ActiveRecord::Base; end

  3. Use MapWithMeasurement.find_all.

Upvotes: 0

Simone Carletti
Simone Carletti

Reputation: 176352

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.

Upvotes: 4

Related Questions