Welcome to Working With Rails

 

Discussion Forums

Discuss all things Ruby on Rails with perhaps the web's most vibrant group of Ruby on Rails enthusiasts.
How to use joins in rails
3 Posts
How to use joins in rails

First of all your join table should be named "cities_companies", as "city" is closer to the begining of alphabet then "company". Then, your request should look like:

Rails2: Company.find_all_by_city_and_status(@city, "active") Rails3: Company.where(:city => @city).and(:status => "active")

Please post some info on how your models are setup in rails.

I want the below sqlquery write using rails joins

sql = "select * from companies c where status = #{Company::ACTIVE} and id in (select company_id from companies_cities cc where cc.city_id=#{city.id}) order by company_name asc"

This is my db structure CREATE TABLE gg.companies ( id int(11) NOT NULL auto_increment, company_name varchar(50) NOT NULL, no_of_employees int(11) NOT NULL, contact_person varchar(100) NOT NULL, status tinyint(1) NOT NULL, created_at date NOT NULL, updated_at date NOT NULL, PRIMARY KEY (id) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1

CREATE TABLE gg.companies_cities ( id int(11) NOT NULL auto_increment, company_id int(11) NOT NULL, city_id int(11) NOT NULL, created_at datetime default NULL, updated_at datetime default NULL, status tinyint(1) NOT NULL default '1', PRIMARY KEY (id) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1

CREATE TABLE gg.cities ( id int(11) NOT NULL auto_increment, city_name varchar(40) NOT NULL, status tinyint(1) NOT NULL default '1', created_at datetime NOT NULL, updated_at datetime NOT NULL, PRIMARY KEY (id) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1"

I want to get the companies based on selected city and companies staus should be active. Can anybody help to write in rails joins EX:Company.find(:all,:joins{etc.....})

Person
Forum Rating: 50
Sign In To Rate Post
3 Posts
Login to add your message