Need to convert a Boolean from Postgres (== String

问题:

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

回答1:

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.

回答2:

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.

回答3:

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

回答4:

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