Skip to content

Instantly share code, notes, and snippets.

@Epigene Epigene/Pure SQL.rb
Last active Jul 29, 2017

Embed
What would you like to do?
The WOW
# from https://stackoverflow.com/a/123481/3319298
# Uses LEFT JOIN to join the posts table with itself in a clever manner - on id AND the relevant, :created_at row.
# The check on created_at makes it so that the last records have NULLs in t2 rows, so we use WHERE to select those and done.
scope :users_last_posts, -> {
query = <<~HEREDOC
SELECT t1.id
FROM post t1
LEFT OUTER JOIN posts t2
ON t1.author_id = t2.author_id AND
(
(t1.published_on < t2.published_on) OR
(t1.published_on = t2.published_on AND t1.id < t2.id)
)
WHERE (
t2.author_id IS NULL
)
HEREDOC
query = query.gsub(%r'\n', ' ').gsub(%r'\s{2,}', ' ').strip
joins("JOIN (#{query}) AS lasts ON lasts.id = #{table_name}.id")
}
Post.users_last_posts.order(created_at: :desc)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.