CODEWITHSUNDEEP
sqlruby-on-railspostgresqlactiverecord
djoll
djoll

Reputation: 1229

Return only unique records in this ActiveRecord query

I have a mildly-complex ActiveRecord query in Rails 3.2 / Postgres that returns documents that are related and most relevant to all documents a user has favorited in the past.

The problem is that despite specifying uniq my query does not return distinct document records:

 Document.joins("INNER JOIN related_documents ON 
                 documents.docid = related_documents.docid_id")
         .select("documents.*, related_documents.relevance_score")
         .where("related_documents.document_id IN (?)",    
                  some_user.favorited_documents)
         .order("related_documents.relevance_score DESC")
         .uniq
         .limit(10)

I use a RelatedDocument join table, ranking each relation by a related_document.relevance_score which I use to order the query result before sampling the top 10. (See this question for schema description.)

The problem is that because I select("documents.*, related_documents.relevance_score"), the same document record returned multiple times with different relevance_scores are considered unique results. (i.e. if the document is a related_document for multiple favorited-documents.)

How do I return unique Documents regardless of the related_document.relevance_score?

I have tried splitting the select into two seperate selects, and changing the position of uniq in the query with no success.

Unfortunately I must select("related_documents.relevance_score") so as to order the results by this field.

Thanks!

UPDATE - SOLUTION

Thanks to Jethroo below, GROUP BY is the needed addition, giving me the follow working query:

 Document.joins("INNER JOIN related_documents ON 
                 documents.docid = related_documents.docid_id")
         .select("documents.*, max(related_documents.relevance_score)")
         .where("related_documents.document_id IN (?)",    
                  some_user.favorited_documents)
         .order("related_documents.relevance_score DESC")
         .group("documents.id")
         .uniq
         .limit(10)

Upvotes: 1

Views: 498

Answers (1)

jethroo
jethroo

Reputation: 2124

Have you tried to group it by documents.docid see http://guides.rubyonrails.org/active_record_querying.html#group?

Upvotes: 1

Related Questions