CODEWITHSUNDEEP
mysqlruby-on-railsrubyactiverecord
mhz
mhz

Reputation: 1029

How to generate ActiveRecord query for

I have a question about ActiveRecord queries: What's the best way to write AR for a query like so:

SELECT .* FROM `professionals` 
LEFT OUTER JOIN `networks` 
  ON `networks`.`userable_id` = `professionals`.`id` 
  AND `networks`.`userable_type` = 'Professional'
WHERE (`networks`.`insurer_id` IS NULL OR (`networks`.`insurer_id` != 1))

The most trouble I'm having is with this part within the query:

WHERE (`networks`.`insurer_id` IS NULL OR (`networks`.`insurer_id` != 1))

I am able to generate that above query with this:

professionals.left_joins(:networks)
  .where(networks: { insurer_id: nil })
  .or(
  professionals.left_joins(:networks)
    .where.not(networks: { insurer_id: @insurer&.id }))

Which is quite insanely smelly because it looks as if I'm repeating part of the query within the first query. Is there a way for me to write it better?

I've been having quite a lot of trouble when using WHERE NOT and chaining OR queries in Rails. So when they eventually all exist at the same time, I'm really out of luck.

EDIT>>>>>>> Not having the NULL condition seems to ignore the other items of the LEFT JOIN...

[4] pry(main)> Professional.left_joins(:networks).where.not(networks: {insurer_id: 1}).count
   (0.4ms)  SELECT COUNT(*) FROM `professionals` LEFT OUTER JOIN `networks` ON `networks`.`userable_id` = `professionals`.`id` AND `networks`.`userable_type` = 'Professional' WHERE (`networks`.`insurer_id` != 1)
=> 0

Upvotes: 0

Views: 73

Answers (3)

engineersmnky
engineersmnky

Reputation: 29308

So I am going to make some assumptions here and hopefully your set up is like so 

class Professional 
  has_many :networks, as: :userable
end 

class Network
   belongs_to :userable, polymorphic: true
end

Then the following should work appropriately (using Arel)

net = Network.arel_table

Professional.left_joins(:networks)
  .where(net[:insured_id].not_eq(@insured.id)
    .or(net[:insured_id].eq(nil)))

This will generate the SQL you posted assuming @insured.id returns 1

Upvotes: 1

mhz
mhz

Reputation: 1029

Two things I had wrong:

  1. In a LEFT JOIN you can't put a WHERE condition without essentially turning your result set into an INNER JOIN (see this question)
  2. LEFT JOINing on polymorphic associations, with your custom AND condition is a bad idea because since its a LEFT JOIN, it won't filter out rows from your additional AND condition. It will simply include them there (because of the polymorphic association ANDs) but will NULL fields.

Ultimately I replaced my previous query with:

professionals.where.not(id: 
    Network.select(:userable_id)
        .where(insurer_id: @insurer.id, userable_type: 'Professional'))

Which works just the same. It boils down to a nested SELECT query in SQL.

Upvotes: 0

grepsedawk
grepsedawk

Reputation: 3411

The best thing you can do is refactor your SQL before switching it to AR:

SELECT .* FROM `professionals` 
LEFT OUTER JOIN `networks` 
  ON `networks`.`userable_id` = `professionals`.`id` 
  AND `networks`.`userable_type` = 'Professional'
WHERE (`networks`.`insurer_id` IS NULL OR (`networks`.`insurer_id` != 1))

If an insurer ID is NOT 1, then is NULL a valid value? I would say so. you should be able to replace 

(`networks`.`insurer_id` IS NULL OR (`networks`.`insurer_id` != 1))

with

`networks`.`insurer_id` != 1

Which leaves us with

SELECT .* FROM `professionals` 
LEFT OUTER JOIN `networks` 
  ON `networks`.`userable_id` = `professionals`.`id` 
  AND `networks`.`userable_type` = 'Professional'
WHERE `networks`.`insurer_id` != 1

which should be a TON easier to query using AR.

Upvotes: 1

Related Questions