CODEWITHSUNDEEP
mysqlruby-on-railsrubyactiverecord
AbhishekSaha
AbhishekSaha

Reputation: 745

ActiveRecord- Inner Join two SQL tables + Where clauses

I have two tables in MySQL and I would like to call a SQL query using an inner join and multiple Where clauses based on a form's input. I am aware that you can execute a raw SQL Query via ActiveRecord::Base.connection, but I'd like to learn how to do so using ActiveRecord objects. The schema for the two MySQL tables are like so:

Describe Options;

'VIN','varchar(45)','NO','PRI',NULL,''
'LEATHER','varchar(45)','YES','',NULL,''
'4WD','varchar(45)','YES','',NULL,''
'FOGLIGHTS','varchar(45)','YES','',NULL,''
'DVD','varchar(45)','YES','',NULL,''
'SURROUND','varchar(45)','YES','',NULL,''

and

Describe Inventory;
'VIN','varchar(30)','NO','PRI',NULL,''
'MAKE','varchar(30)','NO','',NULL,''
'MODEL','varchar(30)','NO','',NULL,''
'TYPE','varchar(50)','NO','',NULL,''

I would like to execute a SQL script like so:

Select Inventory.* from Inventory
INNER JOIN Options
ON Inventory.VIN = Options.VIN
WHERE Inventory.Make = "Toyota"
AND Options.Leather = "Yes";

My Ruby classes in ActiveRecord are like so:

 class Option < ActiveRecord::Base
    self.table_name = "Options"
 end

 class Inventory < ActiveRecord::Base
    self.table_name = "INVENTORY"
 end

Again, I know that I can just input the query as a script, but I'd like to learn how to do it via Ruby best practices

Upvotes: 0

Views: 672

Answers (1)

akbarbin
akbarbin

Reputation: 5105

You can change into this:

Inventory.joins("INNER JOIN Options ON Inventory.VIN = Options.VIN")
         .where("Inventory.Make = ? AND Options.Leather = ?", "Toyota", "YES")

In my opinion, I'd say that you have to change you table into inventories and options for model Inventory and Option so you don't need to use set_table_name in each model. It's about rails convention style code. Then you can see the model like this.

class Inventory < ActiveRecord::Base
  has_many :options, foreign_key: "VIN"
end

class Option < ActiveRecord::Base
  belongs_to :inventory, foreign_key: "VIN"
end

I hope this help you.

Upvotes: 1

Related Questions