CODEWITHSUNDEEP
sqlruby-on-railspostgresqlruby-on-rails-4postgresql-9.4
Tom Prats
Tom Prats

Reputation: 7931

Select Distinct in Postgresql erroring on unrelated column

I'm struggling to write an SQL statement that does the following: Grab the most recent completed assessments of a group of users

Closest attempt:

SELECT DISTINCT(user_id), assessments.*
FROM assessments
WHERE
  user_id IN (1,2,3,4,5,10)
  AND type_name IS NOT NULL
ORDER BY
  created_at DESC,
  user_id DESC

Current error:

could not identify an equality operator for type json

On:

assessments.*

I've run through a lot of different versions of this getting various errors, but this one I think should work but it seems to be trying to be distinct based off of all the columns instead of just user_id.

I'd really like it to work in Rails, but since I couldn't get that, I thought getting the plain SQL to work first.

UPDATE

Part of the problem could be that I have JSON columns on assessment. But since I'm using distinct on just user_id, it should ignore those JSON columns

Upvotes: 2

Views: 368

Answers (1)

Filip Bartuzi
Filip Bartuzi

Reputation: 5931

Try this:

Assesment.uniq(:user_id)
          .where(iuser_id: [1, 2, 3])
          .where
          .not(type_name: nil)

Update:

Since the problem is really in other columns than I would recommend to cast all your json columns to jsonb (pg 9.4 magic)

  class AlterJsonbToJsonAndBack < ActiveRecord::Migration                      
    def up                                                                     
      change_column :assessments, :column_name, 'jsonb USING CAST(column_name AS jsonb)'  
    end                                                                        

    def down                                                                   
      change_column :assessments, :column_name, 'json USING CAST(column_name AS json)'    
    end                                                                        
  end

how to use jsonb in rails

Upvotes: 1

Related Questions