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. -- Posted via http://www.ruby-forum.com/.
2009/9/24 Luis Enrique <rails-mailing-list-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org>:> > 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 >s > 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…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
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. > > ColinThe 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 -- Posted via http://www.ruby-forum.com/.
2009/9/25 Luis Mcdoguall <rails-mailing-list-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org>:> > 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'':...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
On Sep 25, 2:40 am, Luis Mcdoguall <rails-mailing-l...-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org> 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> mistake or rails bugs. > > Thanks > -- > Posted viahttp://www.ruby-forum.com/.
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
> 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
--
Posted via http://www.ruby-forum.com/.