I''m connecting to MS SQL Server to an *ugly* legacy database schema and trying to create a read-only dashboard view that joins a few tables. I''ve got two tables that I need to join and display a subset of columns from both tables. It seems that I don''t need all of ActiveRecord''s magic as (a) I''d need to override most of it anyway, (b) the "model" is about 50-50 across two tables, and (c) no write access needed or allowed. Should I go with just a bare controller and query sql directly from there, or is there an even simpiler way to go about this? --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
On Feb 15, 8:30 am, "kreiggers" <luckybo...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> I''m connecting to MS SQL Server to an *ugly* legacy database schema > and trying to create a read-only dashboard view that joins a few > tables. > > I''ve got two tables that I need to join and display a subset of > columns from both tables. It seems that I don''t need all of > ActiveRecord''s magic as (a) I''d need to override most of it anyway, > (b) the "model" is about 50-50 across two tables, and (c) no write > access needed or allowed. > > Should I go with just a bare controller and query sql directly from > there, or is there an even simpiler way to go about this?I am not an expert in these matters, but could you get create a view in the database for you to use for this application, then base your model off that? This has the added advantage of naming the view after the Rails conventions. If you are worried about access , create a db user with no rights. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
> I am not an expert in these matters, but could you get create a view > in the database for you to use for this application, then base your > model off that? This has the added advantage of naming the view after > the Rails conventions. > If you are worried about access , create a db user with no rights.A view would have worked, but I don''t have access to create the view. I did end up trying the ActiveRecord model and it worked much easier than I expected. I have an empty ActiveRecord class that gives me the connection to the SQL Server 2003 database and I just used ModelClass.find_by_sql(my_query) and it works just fine. As I don''t have any controller methods other than the one or two that I''m using to get my data I don''t think I have to worry about any of the other ActiveRecord bits. I was assuming that AR would get confused and try and give me more attributes that I needed named after the horrible legacy schema I''m using, but it returns exactly what I ask for in the query and nothing extra. Here''s what it ends up looking like: class RequestQueue< ActiveRecord::Base end class RequestQueueController < ApplicationController require ''RequestQueue'' def index sql =<<EOF select fp.mrID as id, fp.mrTITLE as title, cr.mrID as request, fp.mrPRIORITY as severity, fp.mrSTATUS as status from master14 as fp left join master3 as cr on fp.mrID = cr.Ticket where fp.Hotfix = ''on'' and fp.mrSUBMITDATE > ''2007-01-01 00:00:00.000'' order by fp.mrID desc EOF @tickets = RequestQueue.find_by_sql(sql) end end The end result is an object with attributes id, title, request, severity, and status, which is all I was needing. I guess I was just over thinking it before, thinking I needed to write my own model class or to futz around with the raw result from DBI. It''s just, unfortunaely, way easier than that ;-) --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
After using an empty ActiveRecord class as my model and just using
MyModel.find_by_sql in my views I found that I was unhappy with my
code duplication that only involved minor changes tot he sql script as
I started adding different views, plus I missed the ease of adding
conditionals etc the rails way. So after digging around in
ActiveRecord and following through find and find_by_sql etc... I
decided the best way for me to tackle this was to override
construct_finder_sql in my model.It took me a few times to get it just
right -- I found I needed to use set_table_name and set_primary_key
even though I specifically call the table name in the sql as rails
uses the table_name and the primary_key when using a find(:id) call,
also, I found that I also had to define a method for id, otherwise my
model would not have an id (probably due to it not finding mrID as
part of the attributes).
It would seem that setting the id via set_primary_key means the
original implementation looks to return the id via a call to
read_attribute looking for the ''mrID'' that I set it to via the
call to
set_primary_key. Of course my sql actually selects "fp.mrID as id", so
the call to set_primary_key confuses the issue as the query appears to
not return the primary key (it''s always nil). Re-defining the method
for id fixes this.
class MyModel < ActiveRecord::Base
set_table_name ''fp''
set_primary_key ''mrID''
def id
read_attribute(:id)
end
class << self
def construct_finder_sql(options)
# my custom select statement includes a join to get my
''view''
across two tables
# statement leaves out any here or order by clauses as they are
added by the rest of the code
sql = "SELECT some columns blah, blah FROM master AS fp LEFT
JOIN master2 as cr ON fp.mrID = ...." # etc... you get the idea
# this is the original code from ActiveRecord''s implementation
that deals with rails-like where, order_by etc...
add_conditions!(sql, options[:conditions], scope)
sql << " GROUP BY #{options[:group]} " if options[:group]
add_order!(sql, options[:order], scope)
add_limit!(sql, options, scope)
add_lock!(sql, options, scope)
sql
end
end
end
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---