I already know how to use Rails to create subquery within a where condition, like so:

Order.where(item_id: Item.select(:id).where(user_id: 10))

However, my case is a little bit more tricky as you'll see. I'm trying to convert this query:

Post.find_by_sql(
  <<-SQL
    SELECT posts.*
    FROM posts
    WHERE (
      SELECT name
      FROM moderation_events
      WHERE moderable_id = posts.id
        AND moderable_type = 'Post'
      ORDER BY created_at DESC
      LIMIT 1
    ) = 'reported'
  SQL
)

into an ActiveRecord/Arel-like(ish) call but couldn't find a way so far, therefore the raw SQL code and the use of find_by_sql.

I'm wondering if anyone out there already faced the same issue and if there's a better way to write this query ?

EDIT

The raw query above is working and returns exactly the result I want. I'm using PostgreSQL.

Post model

class Post < ApplicationRecord
  has_many :moderation_events, as: :moderable, dependent: :destroy, inverse_of: :moderable
end

ModerationEvent model

class ModerationEvent < ApplicationRecord
  belongs_to :moderable, polymorphic: true
  belongs_to :post, foreign_key: :moderable_id, inverse_of: :moderation_events
end

EDIT 2

I had tried to used Rails associations to query it, using includes, joins and the like. However, the query above is very specific and work well with that form. Altering it with a JOIN query does not return the expected results.

The ORDER and LIMIT statement are very important here and cannot be moved outside of it.

A post can have multiple moderation_events. A moderation event can have multiple name (a.k.a type): reported, validated, moved and deleted.

Here is what the query is doing:

Getting all posts having their last moderation event to be a 'reported' event

I'm not trying to alter the query above because it does works well and fast in our case. I'm just trying to convert it in a more active record fashion without changing it, if possible