CODEWITHSUNDEEP
ruby-on-railspostgresqlactiverecord
R.Bar
R.Bar

Reputation: 362

How to get first row of has_and_belongs_to_many relationship postgres sql

The relationship between the model 'battle' to the model 'category' is has_and_belongs_to_many. I'm trying to find all the battles that don't relate to a specific category. What I tried to do so far is: 

Battle.all.includes(:categories).where("(categories[0].name NOT IN ('Other', 'Fashion'))")

This error occurred: ActiveRecord::StatementInvalid: PG::DatatypeMismatch: ERROR: cannot subscript type categories because it is not an array

Thanks, Rotem

Upvotes: 1

Views: 205

Answers (1)

AmitA
AmitA

Reputation: 3245

The use of categories[0].name is not a valid SQL reference to the name column of categories.

Try this:

Battle.includes(:categories).reject do |battle| 
  ['Other', 'Fashion'].include? battle.categories.map(&:name)
end

Note that this code performs two queries - one for Battle and one for Category - and eager loads the appropriate Category Active Record objects into the instantiated Battle records' :categories relationship. This is helpful to prevent N+1 queries as described in details in the guides.

Also note that the code above first loads to memory ALL Battle records before eliminating the ones that have the undesired categories. Depending on your data, this could be prohibitive. If you prefer to restrict the SQL query so that only the relevant ActiveRecord objects get instantiated, you could get away with something like the following:

battle_ids_sql = Battle.select(:id).joins(:categories).where(categories: {name: ['Other' ,'Fashion']}).to_sql
Battle.where("id NOT IN (#{battle_ids_sql})")

battle_ids_sql is an SQL statement that returns battle IDs that HAVE one of your undesired categories. The SQL which is actually executed fetches all Battle records that are not in that inner SQL. It's effective, although use sparingly - queries like this tend to become hard to maintain rather quickly.

You can learn about joins, includes and two other related methods (eager_load and preload) here.

Upvotes: 2

Related Questions