Query using "select" and "joins" with activerecord in Rails 4

Question

I need to make a report and I have the following query:

Deducao.joins(:prestador)
           .select('pessoas.razao_social, pessoas.qtd_min_mensal, count(deducoes.id)')
           .group('deducoes.prestador_id')
           .having('count(deducoes.id) < pessoas.qtd_min_mensal')

It generates the following SQL:

SELECT 
      pessoas.razao_social, 
      pessoas.qtd_min_mensal, 
      count(deducoes.id) 
FROM 
      deducoes
INNER JOIN 
      pessoas ON pessoas.id = deducoes.prestador_id
GROUP BY 
      deducoes.prestador_id 
HAVING 
      count(deducoes.id) < pessoas.qtd_min_mensal

The generated Sql is correct, and if executed in mysql returns the expected. But when executing in activerecord he returns #<ActiveRecord::Relation [#<Deducao id: nil>, #<Deducao id: nil>]>. Why does he return id: nil instead of the fields I specified in select .select('pessoas.razao_social, pessoas.qtd_min_mensal, count(deducoes.id)')?

Answer 1

It just doesn’t show the column value you need because there isn’t a model in this format.

Try this way: Add an alias to count(deducoes.id) as qtd

Then run on your console:

Deducao.joins(:prestador)
       .select('pessoas.razao_social, pessoas.qtd_min_mensal, count(deducoes.id) as qtd')
       .group('deducoes.prestador_id')
       .having('count(deducoes.id) < pessoas.qtd_min_mensal').first.qtd

You will see that the value you need has returned.

You can also simplify a little in the following way:

Pessoa.joins(:deducoes).select([:razao_social, :qtd_min_mensal, 'count(deducoes.id) as qtd']).group(:id).having('count(deducoes.id) < qtd_min_mensal')