how to join between activerecord model and sqlserver adapter result set

[Geoff Swartz]

I have a sql server view with the following columns

  JobAdId

  PositionTitle

  CompanyID

  ExpirationDate

I have a rails company model with the following properties along with a few more.

  external_id

  user_id

  name

My sql server model is defined as...

class ExpiringJob < ActiveRecord::Base

  establish_connection :jobdb

  self.table_name = "vwExpiringJobs"

  self.primary_key = 'JobAdID'

end

The CompanyID field from sql server maps to the external_id property of the company model.  I need to get all of the expiring jobs from sql server and join it on the company model where the user_id from company equals the logged in user.   So, the traditional sql query would look something like...

select * from vwExpiringJobs inner join companies on vwExpiringJobs.CompanyID = companies.external_id where companies.user_id = 5 order by vwExpiringJobs.ExpirationDate

I'm not finding a way to do a join with the sql server adapter and if that functionality is not possible, what is the next best way to accomplish this?  Thanks.

[Ken Collins]

First, do not use establish_connection like that in your app. Please see one of my recent articles WRT that here: http://technology.customink.com/blog/2015/06/22/rails-multi-database-best-practices-roundup/

Second, Rails supports joins just fine. Should have nothing to do with the adapter, ActiveRecord does the work for us. That is to say, if you solve this issue in a Rails way, then the DB should just work. Without looking too deep into your code examples, have you tried creating some associations with proper PK/FK setups?