1

Following up on the question : Want to find records with no associated records in Rails

I am wondering how I can get all the NON orphan records returned as an AssociationRelation instead of an Array. When trying to subtract the total records of the table from the rails 6 .missing ones, the result is correct, but it's in the form of an array.

Here is a console example :

p = ProductResearch.first
(Product.all - p.products.where.missing(:keywords)).class
=> Array

How do I get the association ?

( With the help of @max below I found a query, without missing, that returns the expected result as an association. It's like :

irb(main):206:0> p.products.includes(:keywords).where.not(keywords: { id: nil }).class
=> Product::ActiveRecord_AssociationRelation

and it does return the non orphan ones only.

Spyros
  • 46,820
  • 25
  • 86
  • 129
  • you mean `all.count - where.missing.count` ? – Lam Phan May 11 '21 at 04:48
  • But you get back an ActiveRecord Relation instance, not an https://blog.saeloun.com/2020/01/21/rails-6-1-adds-query-method-missing-to-find-orphan-records. This is a good summary on how to do it – Joel Blum May 11 '21 at 05:26
  • how about `where.not.missing` ? – Lam Phan May 11 '21 at 05:39
  • What's the code you currently have, and is returning an array? Because from the docs it should return a relation. Trying with Rails 6.1.3.2 and it returns a relation as well. – Sebastián Palma May 11 '21 at 07:00
  • Yes @LamPhan, this one. – Spyros May 11 '21 at 13:55
  • @SebastianPalma it is like : Product.all - p.products.where.missing(:keywords). It is returning an Array. p.products is an Product::ActiveRecord_Associations_CollectionProxy – Spyros May 11 '21 at 14:00
  • @Joel_Blum you do get a relation when you are using missing, but you get an array when you try to get the NON missing ones using sth like Product.all - p.products.where.missing(:keywords). – Spyros May 11 '21 at 14:01
  • `-` is delegated to the `records` method, which is a method defined in `Array`, so, the problem isn't using missing, but the `X - Y` procedure. – Sebastián Palma May 11 '21 at 14:07
  • yeap. makes sense. – Spyros May 11 '21 at 14:07

1 Answers1

2

Given:

class Post < ApplicationRecord
  has_many :comments
end

class Comment < ApplicationRecord
  belongs_to :post
end

class CreateComments < ActiveRecord::Migration[6.0]
  def change
    create_table :comments do |t|
      # Referential integrity is for wusses! YOLO!
      t.belongs_to :post, null: true, foreign_key: false
      t.timestamps
    end
  end
end

p1 = Post.create!(title: 'Foo')
3.times { p1.comments.create! }
p2 = Post.create!(title: 'Bar')
3.times { p2.comments.create! }
p2.destroy! # orphans the comments

If you do an INNER JOIN on posts you will only get rows with at least one match in the join table:

irb(main):014:0> Comment.joins(:post)
  Comment Load (0.3ms)  SELECT "comments".* FROM "comments" INNER JOIN "posts" ON "posts"."id" = "comments"."post_id" LIMIT ?  [["LIMIT", 11]]
=> #<ActiveRecord::Relation [#<Comment id: 1, post_id: 1, created_at: "2021-05-11 08:59:04", updated_at: "2021-05-11 08:59:04">, #<Comment id: 2, post_id: 1, created_at: "2021-05-11 08:59:04", updated_at: "2021-05-11 08:59:04">, #<Comment id: 3, post_id: 1, created_at: "2021-05-11 08:59:04", updated_at: "2021-05-11 08:59:04">]>

This gives you the "non-orphaned" posts.

The opposite is of course an OUTER JOIN:

irb(main):016:0> Comment.left_joins(:post).where(posts: { id: nil })
  Comment Load (0.3ms)  SELECT "comments".* FROM "comments" LEFT OUTER JOIN "posts" ON "posts"."id" = "comments"."post_id" WHERE "posts"."id" IS NULL LIMIT ?  [["LIMIT", 11]]
=> #<ActiveRecord::Relation [#<Comment id: 4, post_id: 2, created_at: "2021-05-11 08:59:26", updated_at: "2021-05-11 08:59:26">, #<Comment id: 5, post_id: 2, created_at: "2021-05-11 08:59:26", updated_at: "2021-05-11 08:59:26">, #<Comment id: 6, post_id: 2, created_at: "2021-05-11 08:59:26", updated_at: "2021-05-11 08:59:26">]>

Rails 6.1 added the .missing query method which is a shortcut for the above query:

Comment.where.missing(:post)
max
  • 96,212
  • 14
  • 104
  • 165
  • thanks a lot @max ! I am finding the missing directive really great, which is why i was wondering if there is a way to do it using missing instead of custom joins. – Spyros May 11 '21 at 13:57
  • No, I don't think you can use `.not.missing`. what's the real problem with just using `.joins(:association_name)`? – max May 11 '21 at 14:04
  • no problem at all, I was just wondering, because it looks slicker. I tried not with missing, wasnt working yeah. I guess i will have to resort to joins. – Spyros May 11 '21 at 14:06
  • 1
    `.not.missing` would work if it generated `Comment.left_joins(:post).where.not(posts: { id: nil })` but I don't think it does. I guess you could write your own `not_missing` method - but even if its slicker it would still force devs to lookup the method - whereas most should know what `.joins` does. – max May 11 '21 at 14:11
  • yeah, i actuall just tried the left join like, it's not working indeed returns the whole set. Still having trouble getting the non orphan ones using the first join though, i think it's because it's returning joins from the whole table ? Not sure, been a long while since i messed much with joins :D But p.products.count has 58 elements in my case and p.products.joins(:keywords).count returns 240 elements :/ it should return 20. When using the missing one or the left join i do get back 38. – Spyros May 11 '21 at 14:15
  • As in p.products.left_joins(:keywords).where(keywords: { id: nil }).count. => 38 I basically want to get the other ones :D – Spyros May 11 '21 at 14:16
  • Thats impossible to debug without an actual example of the models, schema and data. – max May 11 '21 at 14:19
  • yeap understandable, maybe i will create a more detailed question if i still can't solve it. I will probably try to mess with the join a little bit, should be a slight tweak to make i work, it's just been a long while since i've worked on joins and they can be a pain :D – Spyros May 11 '21 at 14:20
  • I actually found a solution i think. Using it like : p.products.includes(:keywords).where.not(keywords: { id: nil }).count returns =>20 – Spyros May 11 '21 at 14:24
  • `.includes` just does a LEFT OUTER JOIN like `.left_joins` and also preloads the records. https://scoutapm.com/blog/activerecord-includes-vs-joins-vs-preload-vs-eager_load-when-and-where – max May 11 '21 at 14:28
  • not sure, why it worked, but it did. I basically just used a combination of your answer and the answer of the original question i linked above with the inclusion of the not directive. So far working nicely :D Was trying variants and got lucky lol – Spyros May 11 '21 at 14:30
  • Comment.missing won't work, afaik it's a method on relation. So Comment.where(..).missing(..) is the way to go – Joel Blum May 11 '21 at 15:37