Search code examples
ruby-on-railsrubypostgresqlhstore

How do I order results by hstore attribute in Rails 4?

How can I order query results by an hstore attribute?

@items = Item.includes(:product).order('products.properties @> hstore("platform")')

Causes

PG::Error: ERROR:  column "platform" does not exist
LINE 1: ...oduct_id"  ORDER BY products.properties @> hstore("platform"...

platform is a hstore key, stored in the properties column, which is an hstore type.

Solution

  • Double quotes are used to quote identifiers (such as table and column names) in PostgreSQL (and other databases that follow the standard). So when you say:

    hstore("platform")

    PostgreSQL sees "platform" as a quoted column name and since there is no platform column, you get an error.

    Strings in standard SQL are quoted with single quotes, you want to say:

    .order("products.properties @> hstore('platform')")

    This will probably still fail though, hstore('platform') doesn't make much sense and neither does using @> here; a @> b means

    does the hstore a contain the hstore b

    If you're trying to sort on the value of the 'platform' key in the properties hstore then you'd want to use -> to lookup the 'platform' key like this:

    .order("products.properties -> 'platform'")