# Active Record SQL View Support for SQL Server - Is this supp

**URL:** <https://www.ruby-forum.com/t/active-record-sql-view-support-for-sql-server-is-this-supp/89916>\
**Category:** Rails\
**Created:** [March 23, 2007, 7:13pm UTC](https://www.ruby-forum.com/t/active-record-sql-view-support-for-sql-server-is-this-supp/89916 "2007-03-23T19:13:00Z")\
**Posts on this page:** 9\
**Page:** 1

<div class="post-metadata">

**Author:** ![BrendanC](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/e9c0ed/32.png) [@BrendanC](https://www.ruby-forum.com/u/BrendanC)\
**Post date:** [March 23, 2007, 7:13pm UTC](https://www.ruby-forum.com/t/active-record-sql-view-support-for-sql-server-is-this-supp/89916/1 "2007-03-23T19:13:00Z")

</div>

I’m just exploring ROR for the first time - so excuse me if that has  
already been asked/answered. I’m intersted in developing a Web front  
end to an existing SQL Server database - migrating this to MySql/  
Postgres is NOT an option.

The front end needs to display data that is aggregated from multiple  
tables via SQL server views. The views already exist in the database  
and are sufficiently complex that I cannot consider recoding them  
using business objects etc.

Is it possible to use a SQL Server view as the source for an Active  
Record - I want to create a form that displays the aggregrated data  
(represented by views). There in no requirement to update from these  
views.

Any links/references/examples/tutorials would be helpful.

TIA,  
BrendanC

---

<div class="post-metadata">

**Author:** ![BrendanC](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/e9c0ed/32.png) [@BrendanC](https://www.ruby-forum.com/u/BrendanC)\
**Post date:** [March 23, 2007, 7:34pm UTC](https://www.ruby-forum.com/t/active-record-sql-view-support-for-sql-server-is-this-supp/89916/2 "2007-03-23T19:34:02Z")

</div>

On 23/03/07, BrendanC [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote:

> Is it possible to use a SQL Server view as the source for an Active  
> Record - I want to create a form that displays the aggregrated data  
> (represented by views). There in no requirement to update from these  
> views.

I do this all the time without any problems. Simply access the view  
as though it were a table.

Tom

---

<div class="post-metadata">

**Author:** ![BrendanC](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/e9c0ed/32.png) [@BrendanC](https://www.ruby-forum.com/u/BrendanC)\
**Post date:** [March 23, 2007, 8:30pm UTC](https://www.ruby-forum.com/t/active-record-sql-view-support-for-sql-server-is-this-supp/89916/3 "2007-03-23T20:30:10Z")

</div>

Hi,

I use this and it works fine.

2007/3/23, BrendanC [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid):

> using business objects etc.  
> BrendanC
> 
> >

–  
Weverton G. de Morais  
Tecnólogo em Redes de Comunicação  
Desenvolvedor Delphi

---

<div class="post-metadata">

**Author:** ![BrendanC](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/e9c0ed/32.png) [@BrendanC](https://www.ruby-forum.com/u/BrendanC)\
**Post date:** [March 23, 2007, 8:44pm UTC](https://www.ruby-forum.com/t/active-record-sql-view-support-for-sql-server-is-this-supp/89916/4 "2007-03-23T20:44:11Z")

</div>

> I do this all the time without any problems. Simply access  
> the view as though it were a table.

I do this for aggregate statistics views by choosing the model most  
closely tied to the view and calling find\_by\_sql on its class. Is there  
a benefit to actually making model objects for the views? How do you  
cope with views that do not have primary keys? Alternately, is there a  
benefit to getting the DBI connection and calling query manually instead  
of going through find\_by\_sql?

- donald

---

<div class="post-metadata">

**Author:** ![BrendanC](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/e9c0ed/32.png) [@BrendanC](https://www.ruby-forum.com/u/BrendanC)\
**Post date:** [March 26, 2007, 10:13am UTC](https://www.ruby-forum.com/t/active-record-sql-view-support-for-sql-server-is-this-supp/89916/5 "2007-03-26T10:13:38Z")

</div>

On 23/03/07, Ball, Donald A Jr (Library) [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid)  
wrote:

> > I do this all the time without any problems. Simply access  
> > the view as though it were a table.
> 
> I do this for aggregate statistics views by choosing the model most  
> closely tied to the view and calling find\_by\_sql on its class. Is there  
> a benefit to actually making model objects for the views? How do you  
> cope with views that do not have primary keys?

As long as you’re not doing updates, the lack of primary keys isn’t an  
issue. Using a model object gives the adapter the opportunity to type  
cast the returned values consistently. It also makes it easier to  
auto-generate aggregate queries (avoiding find\_by\_sql). As an  
example, I use a plugin that allows  
SalaryReport.group\_by(:department\_id) and similar. I can share if  
you’re interested.

Tom

---

<div class="post-metadata">

**Author:** ![BrendanC](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/e9c0ed/32.png) [@BrendanC](https://www.ruby-forum.com/u/BrendanC)\
**Post date:** [March 26, 2007, 2:28pm UTC](https://www.ruby-forum.com/t/active-record-sql-view-support-for-sql-server-is-this-supp/89916/6 "2007-03-26T14:28:48Z")

</div>

On 26/03/07, Weverton G. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote:

> What’s plugin’s name?? I want it.

I’m not able to officially release it as I don’t have the time to  
support it. I can provide the (very scrappy) code if you want to play  
around with it, at your own risk! Usage is pretty basic. First,  
define model with some aggregates:

class SalaryReport \< ActiveRecord::Base  
self.aggregates = {  
:salary =\> ‘sum(salary)’,  
:average\_salary =\> ‘avg(salary)’  
}  
end

Then, call group\_by(columns) to get something you can turn into a  
report:

SalaryReport.group\_by(:country\_id, :department\_id)

It will select all the aggregated columns you’ve defined, as well as  
the grouped attributes, so the example above is similar to

SELECT country\_id, department\_id, sum(salary) salary. avg(salary)  
average\_salary  
FROM salary\_report  
GROUP BY country\_id, department\_id

You can also include conditions if you wish, similar to those for the  
find method:

SalaryReport.group\_by(:country\_id, :conditions =\> {:region\_id =\>  
‘EUROPE’})

Here’s the actual code:

module Tomafro::ActiveRecord::GroupBy  
def self.included(base)  
base.extend(ClassMethods)  
end

module ClassMethods  
def group\_by(\*groups)  
groups = groups.dup.flatten  
options = groups.last.is\_a?(Hash) ? groups.pop : {}  
scope = scope(:find)  
aggregate\_columns = self.aggregates.collect do |key, value|  
“#{value} #{key}”  
end  
group\_columns = groups.collect(&:to\_s)  
sql = “SELECT #{(group\_columns + aggregate\_columns).join(”,\n “)}  
"  
sql \<\< “FROM #{table\_name} "  
add\_conditions!(sql, options[:conditions], scope)  
sql \<\< " GROUP BY #{group\_columns.join(”, “)}”  
sql \<\< " WITH ROLLUP” if options[:rollup]  
records = find\_by\_sql(sql)  
records.each { |record| record.readonly! }  
records  
end

```
def aggregates
  @aggregates || {}
end

def aggregates=(aggregates)
  @aggregates = aggregates
end

```

end  
end

ActiveRecord::Base.send(:include, Tomafro::ActiveRecord::GroupBy)

---

<div class="post-metadata">

**Author:** ![BrendanC](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/e9c0ed/32.png) [@BrendanC](https://www.ruby-forum.com/u/BrendanC)\
**Post date:** [March 26, 2007, 4:06pm UTC](https://www.ruby-forum.com/t/active-record-sql-view-support-for-sql-server-is-this-supp/89916/7 "2007-03-26T16:06:11Z")

</div>

On Mar 26, 2007, at 8:27 AM, Tom W. wrote:

> self.aggregates = {  
> It will select all the aggregated columns you’ve defined, as well as  
> SalaryReport.group\_by(:country\_id, :conditions =\> {:region\_id =\>  
> def group\_by(\*groups)  
> add\_conditions!(sql, options[:conditions], scope)
> 
> ```
> def aggregates=(aggregates)
> @aggregates = aggregates
> end
> 
> ```
> 
> end  
> end
> 
> ActiveRecord::Base.send(:include, Tomafro::ActiveRecord::GroupBy)

I just read about ActiveRecord::Calculations::ClassMethods this  
morning. Tom, do the grouping capabilities provided by those methods  
not meet your needs such that you wrote this plugin for yourself?

Thanks,  
Craig

---

<div class="post-metadata">

**Author:** ![BrendanC](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/e9c0ed/32.png) [@BrendanC](https://www.ruby-forum.com/u/BrendanC)\
**Post date:** [March 26, 2007, 1:14pm UTC](https://www.ruby-forum.com/t/active-record-sql-view-support-for-sql-server-is-this-supp/89916/8 "2007-03-26T13:14:23Z")

</div>

What’s plugin’s name?? I want it.

2007/3/26, Tom W. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid):

> > a benefit to actually making model objects for the views? How do you  
> > Tom
> 
> >

–  
Weverton G. de Morais  
Tecnólogo em Redes de Comunicação  
Desenvolvedor Delphi

---

<div class="post-metadata">

**Author:** ![BrendanC](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/e9c0ed/32.png) [@BrendanC](https://www.ruby-forum.com/u/BrendanC)\
**Post date:** [March 26, 2007, 4:28pm UTC](https://www.ruby-forum.com/t/active-record-sql-view-support-for-sql-server-is-this-supp/89916/9 "2007-03-26T16:28:16Z")

</div>

> I just read about ActiveRecord::Calculations::ClassMethods this  
> morning. Tom, do the grouping capabilities provided by those methods  
> not meet your needs such that you wrote this plugin for yourself?

Last I looked, the Calculations stuff could only pull out a single  
aggregate at a time. i.e to find total payroll and average salary  
would take two queries:

SalaryReport.sum(:salary, :conditions =\> {:country\_id =\> ‘DE’})  
SalaryReport.avg(:salary, :conditions =\> {:country\_id =\> ‘DE’})

Of course, it’s also possible to get these results using the standard  
finder:

SalaryReport.find(:all, :select =\> ‘sum(salary) total\_salary,  
avg(salary) avg\_salary’ …)

I’m not advocating my code over either of these techniques. For my  
particular purpose, where I’m pulling several reports down from views,  
each of which can be grouped in an astounding number of ways, my  
little piece of code has proved useful. For general grouping code,  
you may well find the existing finder methods sufficient.

Tom