CODEWITHSUNDEEP
sqlruby-on-railsarel
stonep
stonep

Reputation: 309

Rails Arel equivalent of this complex sql query

Here is the original logic

(scrape_datas = ScrapeData.find(
  :all, :conditions => 
  "artist_status = 'NOT_FOUND' 
   AND blacklisted = 1 
   AND extracted = 0 
   and not EXISTS(
     SELECT * FROM artist_name_suggestions where original = artist_name
   )

I've been able to split up the first part better

scrape_datas = ScrapeData.where(
  :artist_status => 'NOT_FOUND',
  :blacklisted   => 1,
  :extracted     => 0
)

Although having issues getting the "and not EXISTS" query into the mix

and not EXISTS(
  SELECT * FROM artist_name_suggestions where original = artist_name
)

Thanks!

Upvotes: 1

Views: 539

Answers (1)

blackfoks
blackfoks

Reputation: 121

Firstly you can extract simple scopes:

scope :not_found, where(:artist_status => 'NOT_FOUND')
scope :blacklisted, where(:blacklisted => 1)
scope :extracted, where(:extracted => 0)

Then add a query method (assume artist_name is a column of scrape_datas):

def self.no_suggestions
  scrape_datas = ScrapeData.arel_table
  suggestions = ArtistNameSuggestion.arel_table
  where(ArtistNameSuggestion.where(
    suggestions[:original].eq(scrape_datas[:artist_name])
  ).exists.not)
end

Now you can do something like this:

ScrapeData.not_found.blacklisted.extracted.no_suggestions

Upvotes: 1

Related Questions