Reputation: 1201
I am trying to replica the following query in ActiveRecords
model, currently in Ticket
:
SELECT COUNT(*)
FROM tickets
WHERE tickets.status = 'open' AND (SELECT COUNT(*) FROM messages INNER JOIN users ON messages.user_id = users.id WHERE messages.commentable_id = tickets.id AND users.admin = true) = 0
I have a scope that filters by opened
status:
scope :opened, -> { where(status: 'open') }
And now I created another scope, unanswered
to use it:
scope :unanswered, -> {
opened.all.where(select.joins('INNER JOIN users ON messages.user_id = users.id').where('messages.commentable_id = tickets.id AND users.admin = true').count.zero?)
}
And then it gives me this error:
Completed 500 Internal Server Error in 31ms (ActiveRecord: 5.5ms)
ArgumentError (Call this with at least one field):
What did I miss?
Upvotes: 0
Views: 923
Reputation: 1271231
If you phrase the query like this:
SELECT COUNT(DISTINCT t.id)
FROM tickets t LEFT JOIN
messages m
ON m.commentable_id = t.id LEFT JOIN
users u
ON m.user_id = u.id AND u.admin = true
WHERE t.status = 'open' AND u.id IS NULL;
Then perhaps it will be easier to transform.
Upvotes: 1