CODEWITHSUNDEEP
ruby-on-rails
Alexis_user
Alexis_user

Reputation: 425

Converting SQL query and creating an association between 2 tables in the model

I have just 2 models : item and order, I know use these models like that :

Item.count(:all)
Order.count(:all)

But I would like to do this query : (joining 2 tables but just for the conditions)

SELECT count(i.id)
FROM somethingitem as i, somethingorder as o
WHERE i.uid = o.uid_order
AND o.cancelled = 0
AND o.cashier = 1
AND o.seller = 0;

or with INNER JOIN :

SELECT count(i.id)
FROM somethingitem as i
INNER JOIN somethingorder as o
WHERE i.uid = o.uid_order
AND o.cancelled = 0
AND o.cashier = 1
AND o.seller = 0;

Models :

class Item < ActiveRecord::Base
self.table_name = "somethingitem"
end

class Order < ActiveRecord::Base
self.table_name = "somethingorder"
end

My tables don't have any index or key. (no primary neither foreign)

Thank you.

Edit : For the moment, I use Item.count_by_sql("My query") and it works.

Edit 2 : Ok, To use Activerecord into a existing db, I have to create an association between my 2 tables but just in the rails model, not in my db ! belongs_to , has _many or has_one... My field names hasn't rails standard.

Order can have many items.

Upvotes: 1

Views: 159

Answers (1)

PinnyM
PinnyM

Reputation: 35531

Rails makes this easy if you setup the correct associations in your models. As you noted, your primary/foreign keys aren't following the ActiveRecord convention, so you'll need to give the association a bit more information:

class Item < ActiveRecord::Base
  belongs_to :order, foreign_key: 'uid', primary_key: 'uid_order'
end

class Order < ActiveRecord::Base
  has_many :items, foreign_key: 'uid', primary_key: 'uid_order'
end

You can now do this:

Item.joins(:order).where(orders: {cancelled: 0, cashier: 1, seller: 0}).count

You can make this query cleaner if you put the order scope in the Order class (where it more naturally belongs anyhow):

class Order
  def self.cashier
    where(cashier: 1, seller: 0)
  end

  def self.non_cancelled
    where(cancelled: 0)
  end
end

And now you can do the same query like this:

Item.joins(:order).merge(Order.non_cancelled.cashier).count

Upvotes: 2

Related Questions