cyonder
cyonder

Reputation: 872

Writing complex query for active record

@conversations = Message.find_by_sql("SELECT correspondent, MAX(updated_at) as date FROM (SELECT sender_id as correspondent,updated_at FROM messages WHERE recipient_id = #{current_user.id} UNION ALL SELECT recipient_id as correspondent, updated_at FROM messages WHERE sender_id = #{current_user.id}) x GROUP BY correspondent ORDER BY date desc")

I have this query, which is little bit complex for me as a beginner. I have been trying to write this query according to active record but I couldn't.

I have this query but this isn't correct.

@conversations = Message.where('recipient_id = ?', current_user.id).select('sender_id AS correspondent') + Message.where('sender_id = ?', current_user.id).select('recipient_id AS correspondent')

How can I write it?

Upvotes: 0

Views: 193

Answers (1)

Sergei Stralenia
Sergei Stralenia

Reputation: 865

You can't do it using active record syntax.
I can only suggest you use Arel gem(https://github.com/rails/arel) to build sql-query more elegantly than pure sql-string

Upvotes: 1

Related Questions