I have a Rails application that is set up to use a MySQL database for
most of its data but I have a need to pull in data from a SQL Server
2008 instance as well. This holds our data for Microsoft's CRM and GP
and therefore cannot be pulled into the MySQL database. So I set up a
database configuration in config/database.yml for the connection to this
server and the connection seems to work just fine.
With that said, I have a couple of queries that I need to execute on
this database which joins together two tables and pulls columns from
each table. So I set up a model file for one of the tables like the
following...
Maybe a simple solution would be to create a view in SQL Server and a
model based on that view in Rails?
That would be a simple solution and one that I have thought about.
However, in the future I am considering redeveloping the entire database
and using SQL Server as the main platform. I wouldn't want to have to
create a bunch of views just to get this to work. I thought the whole
point of the Active Record/Model abstraction was so you could code it
once and have it work for any database type.
I guess the problem is that the select statement returns a collection
of Sop10200 objects, so there is nowhere to put the data from the
other table. Normally in rails one would have relationships between
the tables so that the joined data has somewhere to go.