CODEWITHSUNDEEP
ruby-on-railspostgresqlactiverecord
IvanSelivanov
IvanSelivanov

Reputation: 750

Activerecord: Getting a type error when using alias for a table

I have a table with items, and a table with item characteristics. Corresponding models:

    class Characteristic < ActiveRecord::Base
      belongs_to :item
    end

    class Item < ActiveRecord::Base
      has_many :characteristics
    end

Each characteristic has it's name (i.e. 'price'), value, an a reference to an item. I need to select items by multiple characteristics, say, price = 100 and weight = 50. For this, i need to join tables twice, like this:

    Item.joins('INNER JOIN characteristics c1 ON c1.item_id = items.id').
    joins('INNER JOIN characteristics c2 ON c2.item_id = items.id').
    where('c1' => {name: 'price', value: '100'}, 
    'c2' => {name: 'weight', value: '50'})

and this is where the problem is. Characteristic's value is stored in database as string, and when i try to compare it to an integer, or range, I get a type conversion error. But when i don't use an alias for a table, there is no error. So, the code below works:

    Item.joins('INNER JOIN "characteristics" ON "characteristics"."item_id" = "items"."id"').
    where(characteristics: {characteristic_type_id: 223, value: 380})

But this one does not:

    Item.joins('INNER JOIN "characteristics" c1 ON c1."item_id" = "items"."id"').
    where(c1: {characteristic_type_id: 223, value: 380})

So how can I select items with, say, price in 50..100 and color 'brown'?

UPD: neither of above code works, actually. First one does not produce an SQL error, but it does the wrong thing. It just quotes the value so it becomes string. I.e. 

    where(c1: {value: 10..15})

becomes

    WHERE ("c1"."value" BETWEEN '10' AND '15')

which is, obviously, not what I really want So I decided do add one more field to characteristics, value_f:decimal{8,2} to hold the numeric value of characteristic. I also added

    after_validation do
      self.value_f = value.to_f
    end

to characteristic's model. So, when I want to compare a value to a number, I just use value_f instead.

Upvotes: 2

Views: 152

Answers (2)

IvanSelivanov
IvanSelivanov

Reputation: 750

Found the solution: Postgresql. CREATE CAST 'character varying' to 'integer'

    CREATE CAST (varchar AS integer) WITH INOUT AS IMPLICIT;

Still have to figure out how to execute this on Heroku, but that's a different question

Upvotes: 0

Sharvy Ahmed
Sharvy Ahmed

Reputation: 7405

Try this: 

Item.joins(:characteristics).where(characteristics: [{name: 'price', value: '100'},{name: 'weight', value: '50'}] )

Upvotes: 1

Related Questions