# Named\_scope, sql-view

**URL:** <https://www.ruby-forum.com/t/named-scope-sql-view/176083>\
**Category:** Rails\
**Created:** [September 24, 2009, 3:38pm UTC](https://www.ruby-forum.com/t/named-scope-sql-view/176083 "2009-09-24T15:38:50Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![lmcdougall](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/l/74df32/32.png) [@lmcdougall](https://www.ruby-forum.com/u/lmcdougall)\
**Post date:** [September 24, 2009, 3:38pm UTC](https://www.ruby-forum.com/t/named-scope-sql-view/176083/1 "2009-09-24T15:38:50Z")

</div>

I am looking for a way to create , ( and this is the part I really do  
not know) a method a named\_scope a something…  
that allows me to create an query where I can combine several columns  
from different tables.  
In plain sql I could create a view and work with it; but I like one of  
the concepts of named\_scope that  
allows me to use it with other to form the query I want.

I wish I could do something like this:

class Master \< ActiveRecord::Base  
belongs\_to :table1  
belongs\_to :table2  
belongs\_to :table3

named\_scope :selected\_fields, :select =\> “masters.col1, table1s.col3,  
talbe2s.col5”  
end

I’ve tried few things using joins and include but it seems like it is  
always selecting the Master table only.  
My playing around had not payed off at all, I’ve tried using :include,  
:select , etc…

Can someone please give me some advice and maybe a sample code.  
The documentation I’ve found is very simplistic.

Thank you very much,

-Luis.

---

<div class="post-metadata">

**Author:** ![lmcdougall](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/l/74df32/32.png) [@lmcdougall](https://www.ruby-forum.com/u/lmcdougall)\
**Post date:** [September 24, 2009, 4:50pm UTC](https://www.ruby-forum.com/t/named-scope-sql-view/176083/2 "2009-09-24T16:50:20Z")

</div>

2009/9/24 Luis E. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid):

> always selecting the Master table only.  
> My playing around had not payed off at all, I’ve tried using :include,  
> :select , etc…

Why do you wish to do this? If it is for efficiency then are you sure  
you need it? You know that if you have a Master object @master then  
you can say @master.col1, @master.table1.col3 and @master.table2.col5.  
If in the find for @master you :include table1 and table2 it will do  
it all in one query.

Colin

---

<div class="post-metadata">

**Author:** ![lmcdougall](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/l/74df32/32.png) [@lmcdougall](https://www.ruby-forum.com/u/lmcdougall)\
**Post date:** [September 25, 2009, 3:40am UTC](https://www.ruby-forum.com/t/named-scope-sql-view/176083/3 "2009-09-25T03:40:35Z")

</div>

clanlaw wrote:

> Why do you wish to do this? If it is for efficiency then are you sure  
> you need it? You know that if you have a Master object @master then  
> you can say @master.col1, @master.table1.col3 and @master.table2.col5.  
> If in the find for @master you :include table1 and table2 it will do  
> it all in one query.
> 
> Colin

The reasoning behind is that am going to to be using the console for  
this application not html, and it will be nice if I can make the users  
life more simple.  
Later, I will be building the web interface, but for now all we need are  
statistics.

In any case I will like to know how to do it to satisfy my curiosity.

When I use include I get :  
Mysql::Error: Unknown column ‘table1s.col3’ in ‘field list’:…

Then I tried :joints, and joints does not complain but the returned  
values only include masters columns and non of the table-ns.

for instance:

named\_scope :selected\_fields, :joins=\>:table1, :select =\>“masters.id,  
masters.col1, table1s.col22”

then at the console  
Master.selected\_fields

1,col1-content  
and Iam expecting  
1,col1-content,col22-content

If this is a bug, I am such a beginner to know the difference between my  
mistake or rails bugs.

Thanks

---

<div class="post-metadata">

**Author:** ![lmcdougall](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/l/74df32/32.png) [@lmcdougall](https://www.ruby-forum.com/u/lmcdougall)\
**Post date:** [September 25, 2009, 9:52am UTC](https://www.ruby-forum.com/t/named-scope-sql-view/176083/4 "2009-09-25T09:52:32Z")

</div>

2009/9/25 Luis M. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid):

> The reasoning behind is that am going to to be using the console for  
> this application not html, and it will be nice if I can make the users  
> life more simple.  
> Later, I will be building the web interface, but for now all we need are  
> statistics.
> 
> In any case I will like to know how to do it to satisfy my curiosity.
> 
> When I use include I get :  
> Mysql::Error: Unknown column ‘table1s.col3’ in ‘field list’:…

You have mistyped the table name, it should be table1 not tabel1s.  
You can only put a table name in the include not the column name,  
which I imagine is the actual cause of the error.  
If this is just a typo in the email show us the code for the find with  
include.

> Then I tried :joints, and joints does not complain but the returned  
> values only include masters columns and non of the table-ns.

Joins will not do anything for you here as these will be provided for  
you automatically by Rails based on the associations you have setup.

I have not used :select but looking at the docs I think you want to  
use :include to specify the tables to include in the query combined  
with :select to limit it to the columns you want. Give this a go and  
if you cannot get it to work come back with the code you tried and the  
result. Also look in the log to see what the query this generated is.

Colin

---

<div class="post-metadata">

**Author:** ![lmcdougall](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/l/74df32/32.png) [@lmcdougall](https://www.ruby-forum.com/u/lmcdougall)\
**Post date:** [September 25, 2009, 10:30am UTC](https://www.ruby-forum.com/t/named-scope-sql-view/176083/5 "2009-09-25T10:30:35Z")

</div>

On Sep 25, 2:40 am, Luis M. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid)  
wrote:

> then at the console  
> Master.selected\_fields
> 
> 1,col1-content  
> and Iam expecting  
> 1,col1-content,col22-content

The default output from inspect never shows columns from joined  
tables, but they are still there. (take a peak at the attributes for  
one of the objects returned in this manner)

Fred

> If this is a bug, I am such a beginner to know the difference between my

---

<div class="post-metadata">

**Author:** ![lmcdougall](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/l/74df32/32.png) [@lmcdougall](https://www.ruby-forum.com/u/lmcdougall)\
**Post date:** [September 25, 2009, 6:22pm UTC](https://www.ruby-forum.com/t/named-scope-sql-view/176083/6 "2009-09-25T18:22:15Z")

</div>

Colin, Fred,  
Thank you.

Colin,  
it is not a typo, as I am learning ruby+rails I read that rails is  
expecting  
the plural of the name of the table. I went nuts changing the names of  
the  
tables. At the time I did not know that I could force the names, on top  
of this  
this Spanish language influenced the person who designed the DB and  
rails is English oriented 😉  
I believe that inside :select you need the name of the table, and the  
symbol of the association is not allowed. Of course, I maybe wrong but I  
have not yet seen a sample code that lead me to think otherwise.

class Estado \< ActiveRecord::Base  
has\_many :masters  
end

class Master \< ActiveRecord::Base  
belongs\_to :estado  
belongs\_to :municipio  
belongs\_to :localidade  
has\_one :migrante1  
has\_one :migrante2  
has\_one :migrante3  
has\_one :migrante4

## 

##named\_scope

## 

named\_scope :masculino, :conditions =\> {:sexo=\>‘masculino’}  
named\_scope :femenino, :conditions =\> {:sexo=\>‘femenino’}

## Test

named\_scope :edo, :joins=\>:estado, :select=\>“estados.estado”  
end

Let me mention that this database is a legacy from one of the previous  
project/team (political science@Tuffs University).

I did check the attributes, from the console:

> > ma=Master.edo  
> > =\> ma.first.attributes  
> > =\> {“estado”=\>“Michoacan”}

This is progress, but what is the syntax to access it?  
or this is it, and I have to treat it as a hash from this point on?

Although, not what I am looking for but related,  
[http://snippets.dzone.com/posts/show/2089](http://snippets.dzone.com/posts/show/2089)

> The default output from inspect never shows columns from joined  
> tables, but they are still there. (take a peak at the attributes for  
> one of the objects returned in this manner)
> 
> Fred