lundi 26 mars 2018

SELECT list is not in GROUP BY clause and contains nonaggregated column - error with Ruby on Rails. 5

I have models Article and Language (tables articles and languages). Article has_many languages and Language belongs_to article. I want to select articles with their languages. I am using this in Article model:

def self.articles_per_page(limit, offset)
  joins("LEFT JOIN languages ON languages.article_id = articles.id")
  .select('articles.id, languages.language, COUNT(languages.id) as 
   all_languages')
  .limit(limit).offset(offset)
end

In the controller I have:

def get_per_page
  limit = 40
  render json: Article.articles_per_page(limit, params[:offset])
end

, but I am getting this MySQL error:

Mysql2::Error: Expression #10 of SELECT list is not in GROUP BY clause and contains nonaggregated column '.languages.language' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by: SELECT ....

If I add GROUP BY to the query:

def self.articles_per_page(limit, offset)
  joins("LEFT JOIN languages ON languages.article_id = articles.id")
  .select('articles.id, languages.language, COUNT(languages.id) as all_languages')
  .limit(limit).offset(offset)
  .group('articles.id, languages.language') 
end

The error is fixed, but then if I have 1 article in articles table and four languages (for the example) related to this article, this query returns four same articles.

Aucun commentaire:

Enregistrer un commentaire