I have three models User, Post and Favorite.

class User < ApplicationRecord
  has_many :posts
end

class Post < ApplicationRecord
  belongs_to :user
end

class Favorite < ApplicationRecord
  belongs_to :post
  belongs_to :user
end

> user = User.create(name: 'user1')
> user.posts.create(title: 'Title 1')
> user.posts.create(title: 'Title 2')
> Favorite.create(user_id: 1, post_id: 2)

I want to retrieve all posts belongs to user1 with fav status by the user.

> User.first.posts_with_fav_status
=>  [#<Post id: 1, title: "Title 1", user_id: 1, faved: false> ],
    [#<Post id: 2, title: "Title 2", user_id: 1, faved: true> ]

How can I write the query method like this?

Edit

I could get fav status with the following query. But this query calls subquery every time. It will be too slow when DB become bigger. How can I rewrite this query?

  def posts_with_fav_status
    posts.select(<<-SQL)
    *, 
    EXISTS(SELECT * FROM favorites 
           WHERE favorites.user_id = posts.user_id
           AND favorites.post_id = posts.id) as faved
    SQL
  end

CodePudding user response：

You miss relationships in User and Post model:

User:

class User < ApplicationRecord
  has_many :posts

  has_many :favorites
  has_many :favorite_posts, through: :favorites, source: :post
end

Post:

class Post < ApplicationRecord
  belongs_to :user

  has_many :favorites
  has_many :favorited_by, through: :favorites, source: :user
end

With that you can write

u = User.first
u.favorite_posts.where(user_id: u.id)

CodePudding user response：

I came to this solution (tested on Rails 6.1.4.4)

class User < ApplicationRecord
  has_many :posts

  # User.first.posts_with_faved
  def posts_with_faved
    posts.select("posts.*, favorites.user_id = #{id} as faved")
    .left_joins(:favorites)
  end
end

# due to how the inspect works, you won't see the faved attribute in the output, but it is there

irb(main):053:0> first, second = User.first.posts_with_faved
  User Load (0.2ms)  SELECT "users".* FROM "users" ORDER BY "users"."id" ASC LIMIT ?  [["LIMIT", 1]]
  Post Load (0.2ms)  SELECT posts.*, favorites.user_id = 1 as faved FROM "posts" LEFT OUTER JOIN "favorites" ON "favorites"."post_id" = "posts"."id" WHERE "posts"."user_id" = ?  [["user_id", 1]]
=> #<ActiveRecord::AssociationRelation [#<Post id: 1, title: "Title 1", user_id: 1, created_at: "2022-01-28 09:34:19.048598000  0000", updated_at: "2022-01-28 09:34:19.048598000  0000">, #<Post id: 2, title: "Title 2", user_id: 1, created_at: "2022-01-28 09:34:22.172245000  0000", ...

irb(main):054:0> first.faved
=> nil
irb(main):055:0> second.faved
=> 1

Things to take into account:

• be careful and not override with select if you chain the query. Otherwise everything will break
• The query does not return a boolean, but nearly: nil instead of false and 1 instead of true. This will still work if you use it with an if

CodePudding user response：

The way you have your tables and models I don't see a way to avoid that subquery to get the result you want.

Not sure what your use case is, but I'm assuming these are posts users are saving, and some will be favorites, because if the user is the author of the post and they are favoriting their own posts, then why not just add a column to the posts table designating it as a favorite?

If my assumption is correct, it might work better to ditch the Favorite model and use a UserPosts loookup model instead that includes the user_id, post_id, favorite.

Class User < ApplicationRecord
  has_many :user_posts
  has_many :posts, through: :user_posts

end

Class UserPost < ApplicationRecord
  belongs_to :user
  belongs_to :post

end

Class Post < ApplicationRecord
  has_many :user_posts
  has_many :users, through: :user_posts

end

Then you can use

user = User.first
user_posts = user.user_posts.eager_load(:posts)

The fave is on the user_posts table/model so it could be accessed by user_posts.first.fave? and the actual post content user_posts.first.post.

You can add accepts_nested_attributes_for :post to the UserPost model and to create a new post association for a user:

User.first.user_posts.create(fave: true, post: {title: 'title text', ...})

Other than something like that, you're going to have to use a subquery or second query and remap into a hash/array of your own design afterwards.