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

2

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 as expected. But when running in activerecord it returns #<ActiveRecord::Relation [#<Deducao id: nil>, #<Deducao id: nil>]> . Why does it return id: nil instead of the fields I specified in select .select('pessoas.razao_social, pessoas.qtd_min_mensal, count(deducoes.id)') ?

    
asked by anonymous 01.07.2015 / 20:52

1 answer

0

It just does not show the value of the columns it needs because there is no model in this format.

Try this: 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'll see that the value you need returned.

You can simplify it a little too, as follows:

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