CODEWITHSUNDEEP
ruby-on-rails
Alexis_user
Alexis_user

Reputation: 425

SELECT "sum, count, fields" with 2 tables joined

I would like to convert this query using symbols and table_name cause my DB isn't in rails standard.

SELECT v.taux, sum(v.basevat) as tax, sum(v.valuevat) as amountvat, count(t.id)
FROM vatsomething as v
INNER JOIN somethingreceipt as t
ON v.uid_receipt = t.uid
WHERE t.cancelled = 0
GROUP BY v.taux
ORDER BY v.taux ASC;

class Vat < ActiveRecord::Base
  self.table_name = "vatsomething"
  alias_attribute :base, :basevat
  alias_attribute :value, :valuevat
  alias_attribute :rate, :taux
  belongs_to :receipt, foreign_key: 'uid_receipt', primary_key: 'uid'
end

class Receipt < ActiveRecord::Base
  self.table_name = "somethingreceipt"
  has_many :item, foreign_key: 'uid_receipt', primary_key: 'uid'
end

I don't have the choice, if I divide this query to 4 queries, it's too slow.

I tried some queries :

Vat.joints(:receipt).
select(:rate, sum(:base), sum(:value), count(Receipt.table_name.:id) ).
where(Receipt.table_name => hash_of_conds.rejectblank)

I've tried quickly with pluck but I don't know if I can use symbols.

I understand that my query is really hard and the non standard db doesn't help.

Maybe, I'll have to use const_get...

Can you help me ?

Thank you.

Upvotes: 0

Views: 130

Answers (1)

engineersmnky
engineersmnky

Reputation: 29478

Unfortunately you cannot use the more railsy methods very easily with legacy databases in rails. Your queries will have to end up being more SQL for this to function correctly. (I have run into this a lot with people that want to keep their DB but run it on rails)

Have you tried this 

Vat.joins(:receipt).where("somethingreceipt.cancelled = 0").
group("vatsomething.taux").
select("vatsomething.taux as rate,
         sum(vatsomething.basevat) as tax, 
         sum(vatsomething.valuevat) as amountvat,
         count(somethingreceipt.id) as receipt_count").
order("vatsomething.taux")

This should return your desired result.

You have to remember that aliasing methods does not alter the names in the table when running queries you will still have to use the legacy table names and column names.

You can then access the attributes of each object in the ActiveRecord::Relation though their as names e.g. (#rate,#tax,#amountvat,#receipt_count)

Scoping option great for legacy DB's to keep your specific queries consolidated inside the model making it easier to make changes without having to find these queries all over the application.

class Vat

   scope :receipt_summary, ->(conditions){
        joins(:receipt).where(conditions).
        group("vatsomething.taux").
        select("vatsomething.taux as rate,
               sum(vatsomething.basevat) as tax, 
               sum(vatsomething.valuevat) as amountvat,
               count(somethingreceipt.id) as receipt_count").
        order("vatsomething.taux")
        }
end

Then you can call Vat.receipt_summary("somethingreceipt.cancelled = 0") or even should be able to call Vat.receipt_summary({receipt:{cancelled:0}})

Upvotes: 2

Related Questions