CODEWITHSUNDEEP
ruby-on-railsdatabaseruby-on-rails-3arel
Saifis
Saifis

Reputation: 2237

Trouble translating SQL query to Arel

I have a search page that narrows down the list of a specific class, and I want an OR condition that can grab two different conditions and add the together, for example, I have classes

model/party.rb

class Party < ActiveRecord::Base

  has_many :invitations
end

mode/invitation.rb

class Invitation < ActiveRecord::Base

   belongs_to :party
end

invitation has a status attribute, which will be "decline", "accept", or "unanswered"

What I want to do is grab all the parties that do not have any invitations, or any that have all of the invitations "unanswered".

I currently do

  scope :not_confirmed, lambda { find_by_sql( "SELECT * FROM `parties` INNER JOIN `invitations` ON `invitations`.`party_id` = `parties`.`id` WHERE (invitations.status = 'unanswered') OR (parties.id NOT IN (SELECT DISTINCT(party_id) FROM invitations))" ) }

which works, but since it does not lazy load I can't add them in a facet like query.

I did something like

no_invitations.or(no_one_has_answered)

but it did not work.

I especially do not get the concept of using OR on AREL, could someone please help out?

edited:

For a very ugly yet functional work around until I get this down, here is what I have done

party.rb

  scope :not_confirmed, lambda { joins(:invitations).where( "invitations.status NOT IN (?)", ["accepted", "declined" ] ) }
  scope :with_no_invitations, lambda { includes(:invitaions).where( :invitations => { :party_id => nil } ) }

search_controller.rb

@parties = Party.all_the_shared_queries
@parties = ( @parties.not_confirmed + @parties.with_no_invitations).uniq

Upvotes: 1

Views: 101

Answers (2)

Малъ Скрылевъ
Малъ Скрылевъ

Reputation: 16507

The query:

  scope :not_confirmed, lambda { find_by_sql( "SELECT * FROM `parties` INNER JOIN `invitations` ON `invitations`.`party_id` = `parties`.`id` WHERE (invitations.status = 'unanswered') OR (parties.id NOT IN (SELECT DISTINCT(party_id) FROM invitations))" ) }

can be converted to with some transformation using boolean algebra too. But since it is only theoretical conversion, you have to verify it manually. So:

class Invitation < ActiveRecord::Base
   belongs_to :party

   scope :non_answered, -> { where(arel_table[:status].not_eq('unanswered')) }
end

class Party < ActiveRecord::Base
   has_many :invitations

   scope :not_confirmed, -> { not.join(:invitaions).merge(Invitation.non_answered)) }
end

Please test it and comment here.

Upvotes: 2

Aaditi Jain
Aaditi Jain

Reputation: 7027

Firstly, from the question tags, I have assumed that you are using Rails3 (had it been Rails4, there were more easy ways of doing things :))

For your requirement above (ie grab all the parties that do not have any invitations, or any that have all of the invitations "unanswered"), here is a way of doing it (using scope :unattended):

Party Model:

class Party < ActiveRecord::Base
  has_many :invitations
  scope :invitations_answered, -> { joins(:invitations).merge(Invitation.answered) }
  scope :unattended, -> { where(arel_table[:id].not_in invitations_answered.pluck(:id)) }
end

Invitation Model:

class Invitation < ActiveRecord::Base
  belongs_to :party
  scope :answered, -> { where(status: ["decline", "accept"])}
end

In Rails 4, you can use where.not and simplify it further like this:

Party Model:

class Party < ActiveRecord::Base
  has_many :invitations
  scope :invitations_answered, -> { joins(:invitations).merge(Invitation.answered) }
  scope :unattended, -> { where.not(id: invitations_answered.pluck(:id)) }
end

Invitation Model:

class Invitation < ActiveRecord::Base
  belongs_to :party
  scope :answered, -> { where.not(status: 'unanswered') }
end

Upvotes: 1

Related Questions