Retrieve All Posts Where the Given User Has Commented, Ruby on Rails

Retrieve all posts where the given user has commented, Ruby on Rails

You could define an association which retrieves all the posts with comments in a single query. Keeping it in the model reduces the complexity of your controllers, enables you to reuse the association and makes it easier to unit test.

class User < ActiveRecord::Base
  has_many :posts_with_comments, :through => :comments, :source => :post
  # ...
end

:through is an option for has_many to specify a join table through which to perform the query. We need to specify the :source as Rails wouldn't be able to infer the source from :post_with_comments.

Lastly, update your controller to use the association.

def show
  @user  = User.find(params[:user_id])
  @posts = @user.posts_with_comments
end

To understand more about :through and :source take a look at the documentation.

rails find all comments where the post's user_id = user

You have to join the posts table for that:

@comments = Comment.joins(:post).where(:posts => { :user_id => @user.id })

Get user comments and all comments grouped within the same query

You can get the comments in one query like below:

@comments = Comment.left_outer_joins(:post)
                   .select("comments.id,comments.description,posts.id as post_id")
                   .where("posts.id = ? OR posts.id IS NULL",  post_id })

Then split using group_by:

remaining_comments = @comments.group_by(&:post_id)

Accessing all comments, on all posts, that a current_user has commented on

As you'd probably want to organize the comments according the the workouts that the user had commented on (as opposed to one long undifferentiated string of comments without any context), first I'd suggest using a :has_many => through to aggregate the workouts that the user had commented on, something roughly like (untested, obviously):

has_many :commented_workouts, :through => :comments, :class_name => 'Workout', :source =>  :workout, :uniq => true, :order => "created_at desc"

Then you could display the comments in your erb something like:

<% current_user.commented_workouts.each do |workout| %>
  <%= workout.name %>:
  <% workout.comments.each do |comment| %>
    <%= comment.text %>
  <% end %>
<% end %>

EDIT: you could also do:

<% current_user.commented_workouts.each do |workout| %>
  <% workout.comments.sort{|x,y| x.created_at <=> y.created_at }.each do |comment| %>
    <%= comment.user.name %> just commented on <%= workout.title %>:
    <div><%= comment.text %></div>
  <% end %>
<% end %>

EDIT: Or like so (note the limit added to the array):

class User
  def watched_comments
    commented_workouts.map(&:comments).flatten.sort{|x,y| x.created_at <=> y.created_at }
  end
end

# and in the erb: 

<% current_user.watched_comments[0,10].each do |comment| %>
  <%= comment.user.name %> just commented on <%= comment.workout.title %>:
  <div><%= comment.text %></div>
<% end %>

There's a bit of nasty n+1 query fu going on here that might not be real performant tho. Alternately you could try to get everything done in straight sql which would perform better. Something like (sql ninjas no doubt could do better):

EDIT: You can also add a 'limit' option directly in the SQL

has_many :watched_comments, :class_name => 'Comment', :finder_sql => 'select * from comments, workouts where comments.workout_id = workout.id and workouts.id in (select workouts.id from workouts, comments where workouts.id = comments.id and comments.user_id = #{id}) order by comments.created_at desc limit 10'

Rails Query to show all posts that have no comments by current_user

Here is how you can achieve your three goals.

Requests with no quotes:

Request.includes(:quotes).where( quotes: { id: nil } ).order(created_at: :desc)

Requests with quotes from other service centers:

Requests.joins(:quotes).where.not(quotes: { service_center_id: current_service_center.id })

Here is the answer to your question:

Requests with no quotes from your service center:

Request.where.not(
  id: Request.joins(:quotes)
        .where(quotes: { service_center_id: current_service_center.id })
        .select(:id) 
)

This is somewhat janky, but it should work. The subquery gets all of the requests that have a quote from the current service center. The outer query returns all of the requests that are not in the first list. It should still run fast with one query in the database.

Another solution which will yield the same result is to use NOT EXISTS:

Request.joins(:quotes).where(
  Quote
    .where('requests.id = quotes.request_id')
    .where('quotes.service_center_id = ? ', current_service_center.id)
    .exists.not
)

This will result in a NOT EXISTS subquery in SQL. It should result in roughly the same performance as the other query. I suppose it is just a matter of preference.

Get all articles that a certain user hasn't commented on

#app/models/article.rb
class Article < ActiveRecord::Base
   scope :no_user_comments, ->(id) { joins(:comments).where('comments.user_id NOT IN (?)', id) }
end

You can then pass:

@articles = Article.no_user_comments @user.id

or

@articles = Article.no_user_comments [@user.id, "15", "25"]

Get last comment of all posts in one category

Rails doesn't do this particularly well, especially with Postgres which forbids the obvious solution (as given by @Jon and @Deefour).

Here's the solution I've used, translated to your example domain:

class Comment < ActiveRecord::Base
  scope :most_recent, -> { joins(
    "INNER JOIN (
      SELECT DISTINCT ON (post_id) post_id,id FROM comments ORDER BY post_id,updated_at DESC,id
    ) most_recent ON (most_recent.id=comments.id)"
  )}
  ...

(DISTINCT ON is a Postgres extension to the SQL standard so it won't work on other databases.)

Brief explanation: the DISTINCT ON gets rid of all the rows except the first one for each post_id. It decides which row the first one is by using the ORDER BY, which has to start with post_id and then orders by updated at DESC to get the most recent, and then id as a tie-breaker (usually not necessary).

Then you would use it like this:

Comment.most_recent.joins(:post).where("posts.category_id" => category.id)

The query it generates is something like:

SELECT *
FROM comments
  INNER JOIN posts ON (posts.id=comments.post_id)
  INNER JOIN (
    SELECT DISTINCT ON (post_id) post_id,id FROM comments ORDER BY post_id,updated_at DESC,id
  ) most_recent ON (most_recent.id=comments.id)
WHERE
  posts.category_id=#{category.id}

Single query, pretty efficient. I'd be ecstatic if someone could give me a less complex solution though!

Ruby on Rails: How to iterate whether the user already commented on the post

In your comment.rb

validates :user_id, uniqueness: { scope: [:post_id]}

This validation will make sure the combination user_id and post_id will be unique in your comments table.

---

Related Topics