Rails - Apr 2005 - Create a pseudo-model from SQL query?

I''m having a hard time figuring out how to build my application using
Rails.  As i go
along, simple questions occur to me like: are you supposed to have a separate
model
class (each in its own file, in the app/models directory) for each table in your
database?  You''d think that something as basic as this would be laid
out in the
introductory documentation but, alas, the tutorials seem to mostly cover getting
your
system set up and the rails documentation all reads like reference manuals
(which is
what they are).  I''m feeling a big lack of anything in the middle that
*explains* the
basic methodology to be used in building models and controllers for anything but
the
most trivial examples.  Anyway, enough bitching.  But i don''t like, and
don''t want to
use, the access mechanism provided by ActiveRecord, if i''m
understanding things
properly.  It seems like it will be far easier for me to just use
''find_by_sql'' and
write my own sql queries to pull out the data i need.

Anyway, the examples i''ve seen suggest that in my controller i will
reference my
model(s) -- each table via its own individual query via an automatic translation
from
the corresponding model -- as the means to get my data.  But this seems to imply
that
the data i want can (fairly) simply be pulled from a single table, which may not
be
the case.  I suppose i could grab a little data from one "model" and
another from
another and then in ruby manipulate it to get what i want.  But i can far more
easily
and efficiently get it using SQL queries (across all the relevant tables in my 
database).  So... is there a way of accommodating this method of doing things? 
i.e.
is it possible (in my model) to do a sql query that gets the data i want,
however
that happens, and then stick it into a variable/array/whatever to be accessed in
my
controller?  The problem i have with thinking about doing this is that what i
seem to
have available to me to use in returning data to the controller are (basically)
the
individual models (e.g. tables) themselves.  What i''d like to be able
to do is make
up a (pseudo-model) name, which i can populate -- in my pseudo-model class --
for
access in my controller.  Is this accommodated in any natural way by the rails
framework?

craig

On 4/18/05, craig duncan
<craig-duncan-ihVZJaRskl1bRRN4PJnoQQ@public.gmane.org>
wrote:
> I''m having a hard time figuring out how to build my application
using Rails.  As i go
> along, simple questions occur to me like: are you supposed to have a
separate model
> class (each in its own file, in the app/models directory) for each table in
your
> database?  You''d think that something as basic as this would be
laid out in the
> introductory documentation but, alas, the tutorials seem to mostly cover
getting your
> system set up and the rails documentation all reads like reference manuals
(which is
> what they are).  I''m feeling a big lack of anything in the middle
that *explains* the
> basic methodology to be used in building models and controllers for
anything but the
> most trivial examples.  Anyway, enough bitching.  But i don''t
like, and don''t want to
> use, the access mechanism provided by ActiveRecord, if i''m
understanding things
> properly.  It seems like it will be far easier for me to just use
''find_by_sql'' and
> write my own sql queries to pull out the data i need.
> 
> Anyway, the examples i''ve seen suggest that in my controller i
will reference my
> model(s) -- each table via its own individual query via an automatic
translation from
> the corresponding model -- as the means to get my data.  But this seems to
imply that
> the data i want can (fairly) simply be pulled from a single table, which
may not be
> the case.  I suppose i could grab a little data from one "model"
and another from
> another and then in ruby manipulate it to get what i want.  But i can far
more easily
> and efficiently get it using SQL queries (across all the relevant tables in
my
> database).  So... is there a way of accommodating this method of doing
things?  i.e.
> is it possible (in my model) to do a sql query that gets the data i want,
however
> that happens, and then stick it into a variable/array/whatever to be
accessed in my
> controller?  The problem i have with thinking about doing this is that what
i seem to
> have available to me to use in returning data to the controller are
(basically) the
> individual models (e.g. tables) themselves.  What i''d like to be
able to do is make
> up a (pseudo-model) name, which i can populate -- in my pseudo-model class
-- for
> access in my controller.  Is this accommodated in any natural way by the
rails framework?
There is nothing about rails that ties it to using ActiveRecord for
your models.  If you want to have a complicated schema which doesn''t
match your applications business rules, then go for it.

There have been several discussions on the list about this in the
past.  Look for things like "Rails without active record" "rails
without a db" "rails and dbi" etc.   You''ll lose some of
the
productivity gains as you''ll be writing a lot of code that you would
otherwise get for free.   But this sort of thing is perfectly
possible.  Just write a ruby library to do your data access & business
logic, drop it into vendor,  require it where it''s needed and off you
go.

As this comes up a lot, perhaps we need a wiki page for it.....

PS, it would have been possible to word your email in a slightly less
angry manner,  all you''re asking is for an app without using Active
Record.
> craig
> 
> _______________________________________________
> Rails mailing list
> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
> 

-- 
Cheers

Koz

craig,

Could you possibly share more about the models/system you''re trying to
put together? It might be easier to figure out what the best solution
might be, in that case.

As I understand it, you want a single model''s attributes to be drawn
up from multiple tables, correct? (So, your Album model draws it''s
attributes from tables of CoverImages, of Artists, and so on.) In this
case, yes, I''d use some has_many / has_one relationships, but I can
see how that''s not always the best.

Michael Koziarski wrote:
> On 4/18/05, craig duncan
<craig-duncan-ihVZJaRskl1bRRN4PJnoQQ@public.gmane.org> wrote:
> 
<snip>
> 
> There is nothing about rails that ties it to using ActiveRecord for
> your models.  If you want to have a complicated schema which
doesn''t
> match your applications business rules, then go for it.
> 
> There have been several discussions on the list about this in the
> past.  Look for things like "Rails without active record"
"rails
> without a db" "rails and dbi" etc.   You''ll lose
some of the
> productivity gains as you''ll be writing a lot of code that you
would
> otherwise get for free.   But this sort of thing is perfectly
> possible.  Just write a ruby library to do your data access & business
> logic, drop it into vendor,  require it where it''s needed and off
you
> go.
I''ve been reading the mailing list... including the topics you refer
to.  "Losing the
productivity gains", though, implies that, basically, i can''t use
ActiveRecord (that
it won''t easily accommodate a slightly different approach towards data
access).  This
was the essence of my question.  ActiveRecord, as i understand it, seems like it
might, in fact, be able to accommodate what i have in mind, if only it were a
little
more flexible.  (I didn''t actually know whether it could or
couldn''t, though, thus my
question).
> As this comes up a lot, perhaps we need a wiki page for it.....
> 
> PS, it would have been possible to word your email in a slightly less
> angry manner,  all you''re asking is for an app without using
Active
> Record.
I''m surprised that it came across as angry.  Certainly frustrated,
basically because
of a lack of
"overview"/"fundamentals"/"architecture" sort of
documentation.  But i
didn''t think my question was how to do without ActiveRecord... because
that seems
like doing without quite a bit.  It was "can it be used in a slightly
different
way"... specifically as i was inquiring about... writing my own sql queries
(and
_how_ to get the result back to the controller?).  Does this imply  completely 
throwing out ActiveRecord?  It seems like AR also provides a layer which
implements
the "linkage" between the model and controller.  This is what
i''d like to not have to
completely rewrite, if i can''t use AR exactly as it is.  I''m
very new to Rails *and*
web programming, though (and barely understand the RAILS architecture at all) so
please excuse my frustration.

craig

Dev Purkayastha wrote:
> craig,
> 
> Could you possibly share more about the models/system you''re
trying to
> put together? It might be easier to figure out what the best solution
> might be, in that case.
For an album there is artist info (of course), audio format information
(and subformat for types of encoding), physical source info (CD (w/ subtypes),
hard drive...), location info., linkage info to other CDs, and maybe more.
I want to be able to display album info very flexibly but almost any display
format requires info from a number of other tables that contain the actual
info mentioned above (referred to by id in an album record).
> As I understand it, you want a single model''s attributes to be
drawn
> up from multiple tables, correct? (So, your Album model draws it''s
> attributes from tables of CoverImages, of Artists, and so on.) In this
> case, yes, I''d use some has_many / has_one relationships, but I
can
> see how that''s not always the best.
Exactly.  In fact, just thinking about using those associations made me start
casting
about for a better way.  Beyond the simplest level, it seems to me that these 
association mechanisms would be far more cumbersome to work with than just
querying
directly for what i want from the database (and less efficient, too, although
that
might or might not be a consideration, depending).

craig

craig duncan wrote:
> I''ve been reading the mailing list... including the topics 
> you refer to.  "Losing the productivity gains", though, 
> implies that, basically, i can''t use ActiveRecord (that it 
> won''t easily accommodate a slightly different approach 
> towards data access).  This was the essence of my question.  
> ActiveRecord, as i understand it, seems like it might, in 
> fact, be able to accommodate what i have in mind, if only it 
> were a little more flexible.  (I didn''t actually know whether 
> it could or couldn''t, though, thus my question).
It''s perfectly possible to extend AR to fit your will.

Let me tell you about my model.

The table names are all singular, prefixed with "tbl", and the
corresponding
class names are prefixed with "C".

All classes inherit from a root class - "CEntity".  The corresponding
table,
tblEntity contains a ClassID field which relates to tblClass.

Every class has its own table, with inherited attributes stored in the
parent class'' table.

So by declaring class CActor < CPerson,
CActor.find will pick up fields from tblActor and tblPerson.

There are probably some people cringing in horror at my choice of naming
conventions, but my point is that my model is completely un-railsy yet I
haven''t had to modify a single line of code in AR to make it work (well
I
have, but for reasons that don''t relate to my model design).

What I have had to do, to make it all work, is to extend module ActiveRecord
with some redefinitions.

module ActiveRecord
  class MyBase < Base
    def self.find_all(...)
      ...Do some magic here
    end

    ...Define some more methods here
  end
end

I haven''t tried it, but since all of my redifinitions of AR methods are
in
MyBase, I could probably mix and match "traditional" AR objects with
MyBase-derived objects in the same model.

As things stand, I just declare

class CEntity < ActiveRecord::MyBase
  extend MyExtension
  belongs_to :Class , :class_name => "CClass", :foreign_key =>
"lClassID"
end

class CClass < CEntity
  belongs_to :ParentClass, :class_name => "CClass", :foreign_key
=>
"lParentClassID"    
end

The rest of my classes declare themselves.

What I am doing is quite a departure from the "standard" model.  What
you
are trying to do sounds even easier.

You could probably get away with descending your objects from
ActiveRecord::Base and just overriding a few methods (starting with
self.find_all).  I found overriding the way ActiveRecord does updates to be
a little tricky, but doable.

Adelle.

On 4/18/05, craig duncan
<craig-duncan-ihVZJaRskl1bRRN4PJnoQQ@public.gmane.org>
wrote:
> Dev Purkayastha wrote:
> > craig,
> >
> > Could you possibly share more about the models/system you''re
trying to
> > put together? It might be easier to figure out what the best solution
> > might be, in that case.
> 
> For an album there is artist info (of course), audio format information
> (and subformat for types of encoding), physical source info (CD (w/
subtypes),
> hard drive...), location info., linkage info to other CDs, and maybe more.
> I want to be able to display album info very flexibly but almost any
display
> format requires info from a number of other tables that contain the actual
> info mentioned above (referred to by id in an album record).
> 
> > As I understand it, you want a single model''s attributes to
be drawn
> > up from multiple tables, correct? (So, your Album model draws
it''s
> > attributes from tables of CoverImages, of Artists, and so on.) In this
> > case, yes, I''d use some has_many / has_one relationships, but
I can
> > see how that''s not always the best.
> 
> Exactly.  In fact, just thinking about using those associations made me
start casting
> about for a better way.  Beyond the simplest level, it seems to me that
these
> association mechanisms would be far more cumbersome to work with than just
querying
> directly for what i want from the database (and less efficient, too,
although that
> might or might not be a consideration, depending).
Something to bear in mind is that work on ''eager loading'' of
associations is currently underway in subversion.  Which may alay some
of your performance concerns. I believe what this means is that if you
have a has_one or a belongs_to association, it''ll get pulled in by an
outer join.

I found some mentions of the eager loading in this changeset:

http://dev.rubyonrails.org/changeset/1083

One thing to remember is that the reason that active record is so much
more fun to work with than a full ORM solution like hibernate or
toplink, is that it doesn''t try to solve all the hairy corner cases.  
So the limitations aren''t a bug,  they''re a feature ;)

Another thing to remember is premature optimisation is the root of all
evil (http://c2.com/cgi/wiki?PrematureOptimization).

Make it work *right*.
Then make it work *fast*.

Wait until you see what speed issues you actually have with your
application before assuming that lazy associations will be the cause
of your grief.  It could be that some algorithm in your app is where
the real problem is.
> craig
> _______________________________________________
> Rails mailing list
> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
> 

-- 
Cheers

Koz

craig duncan wrote:
> I''ve been reading the mailing list... including the topics you
refer
> to.  "Losing the productivity gains", though, implies that,
basically,
> i can''t use ActiveRecord (that it won''t easily
accommodate a slightly
> different approach towards data access).  This was the essence of my 
> question.
> ActiveRecord, as i understand it, seems like it might, in fact, be 
> able to accommodate what i have in mind, if only it were a little more 
> flexible.  (I didn''t actually know whether it could or
couldn''t,
> though, thus my question).
It''s perfectly possible to extend AR to fit your will.

Let me tell you about my model.

The table names are all singular, prefixed with "tbl", and the
corresponding
class names are prefixed with "C".

All classes inherit from a root class - "CEntity".  The corresponding
table,
tblEntity contains a ClassID field which relates to tblClass.

Every class has its own table, with inherited attributes stored in the
parent class'' table.

So by declaring class CActor < CPerson,
CActor.find will pick up fields from tblActor and tblPerson.

There are probably some people cringing in horror at my choice of naming
conventions, but my point is that my model is completely un-railsy yet I
haven''t had to modify a single line of code in AR to make it work (well
I
have, but for reasons that don''t relate to my model design).

What I have had to do, to make it all work, is to extend module ActiveRecord
with some redefinitions.

module ActiveRecord
  class MyBase < Base
    def self.find_all(...)
      ...Do some magic here
    end

    ...Define some more methods here
  end
end

I haven''t tried it, but since all of my redifinitions of AR methods are
in
MyBase, I could probably mix and match "traditional" AR objects with
MyBase-derived objects in the same model.

As things stand, I just declare

class CEntity < ActiveRecord::MyBase
  extend MyExtension
  belongs_to :Class , :class_name => "CClass", :foreign_key =>
"lClassID"
end

class CClass < CEntity
  belongs_to :ParentClass, :class_name => "CClass", :foreign_key
=>
"lParentClassID"    
end

The rest of my classes declare themselves.

What I am doing is quite a departure from the "standard" model.  What
you
are trying to do sounds even easier.

You could probably get away with descending your objects from
ActiveRecord::Base and just overriding a few methods (starting with
self.find_all).  I found overriding the way ActiveRecord does updates to be
a little tricky, but doable.

Adelle.

> For an album there is artist info (of course), audio format information
> (and subformat for types of encoding), physical source info (CD (w/
subtypes),
> hard drive...), location info., linkage info to other CDs, and maybe more.
> I want to be able to display album info very flexibly but almost any
display
> format requires info from a number of other tables that contain the actual
> info mentioned above (referred to by id in an album record).
Is there a good reason that this information isn''t all on a single row
of a single database? Why stretch this out over several tables if it''s
not necessary? Very self-contained information may well deserve it''s
own table (and a has_one) relationship, but I''ve heard that database
normalization, where possible, is an important component of
optimization.

On Apr 17, 2005, at 7:11 PM, craig duncan wrote:
> I''m having a hard time figuring out how to build my application
using
> Rails.  As i go
> along, simple questions occur to me like: are you supposed to have a 
> separate model class (each in its own file, in the app/models 
> directory) for each table in your database?  You''d think that 
> something as basic as this would be laid out in the introductory 
> documentation but, alas, the tutorials seem to mostly cover getting 
> your system set up and the rails documentation all reads like 
> reference manuals (which is what they are).  I''m feeling a big
lack of
> anything in the middle that *explains* the basic methodology to be 
> used in building models and controllers for anything but the most 
> trivial examples.
I just wanted to chime in with a "me too" on this aspect of your post.
I''m starting to get it, but it''s taken a while, and
I''m still not
there.

I know that the request -- "Can someone please just write a Pickaxe for 
Rails?" -- is a lot to ask, and not a trivial undertaking. I''m
very
grateful to those who have worked on Rails so far, both on code and 
documentation. I just think it''s important to note that I had the exact
same reaction when starting with rails. The API documentation is 
EXCELLENT, and in snippets gives quite good class overviews. But it''s 
still API documentation, and neither it nor the diagram on the rails 
homepage explains how everything fits together.

If/when I grok rails I would love to contribute with a ToC for what I''m
looking for, and pretty diagrams which I think would help. I just don''t
grok it yet, and so am in the position of asking others to fix the 
problem for me, and all other newbies.

(Given my experience on the #rubyonrails IRC channel, it''s not just 
Craig and I who have this problem. It''s a current problem for the 
majority of rails newbies. Hopefully it''s one that can be solved.)

Dev Purkayastha wrote:
>>For an album there is artist info (of course), audio format information
>>(and subformat for types of encoding), physical source info (CD (w/
subtypes),
>>hard drive...), location info., linkage info to other CDs, and maybe
more.
>>I want to be able to display album info very flexibly but almost any
display
>>format requires info from a number of other tables that contain the
actual
>>info mentioned above (referred to by id in an album record).
> 
> 
> Is there a good reason that this information isn''t all on a single
row
> of a single database?
I think so.
> Why stretch this out over several tables if it''s
> not necessary?
To avoid duplication, and data entry mistakes.  I have textual descriptions for
every
record in every subsidiary table that i referred to.  I can reference the record
by
id but what you''re suggesting is to put all the information into the
main table.
Very duplicative and error-prone.  Although it does point up what i
hadn''t quite
noticed (yet) when responding to other suggestions: these relationships are all
1:1.
> Very self-contained information may well deserve it''s
> own table (and a has_one) relationship, but I''ve heard that
database
> normalization, where possible, is an important component of
> optimization.
Don''t think my structure has anything to do with db normalization as i
understand it.

craig

Be sure to note that there is a book all about Rails due out in July:

http://www.pragmaticprogrammer.com/titles/rails/index.html

I''m sure these concepts in the "middle" (which I agree are
sorely
lacking from the existing docs--it''s the hardest part to get done,
especially with things changing as fast as they are) will be addressed
in the book, which I look forward to buying, even though I *think*
I''ve gotten over those conceptual hurdles myself.

-dave

On 4/18/05, Gavin Kistner <gavin-XtLdkLkwz3ZWk0Htik3J/w@public.gmane.org>
wrote:
> On Apr 17, 2005, at 7:11 PM, craig duncan wrote:
> > I''m having a hard time figuring out how to build my
application using
> > Rails.  As i go
> > along, simple questions occur to me like: are you supposed to have a
> > separate model class (each in its own file, in the app/models
> > directory) for each table in your database?  You''d think that
> > something as basic as this would be laid out in the introductory
> > documentation but, alas, the tutorials seem to mostly cover getting
> > your system set up and the rails documentation all reads like
> > reference manuals (which is what they are).  I''m feeling a
big lack of
> > anything in the middle that *explains* the basic methodology to be
> > used in building models and controllers for anything but the most
> > trivial examples.
> 
> I just wanted to chime in with a "me too" on this aspect of your
post.
> I''m starting to get it, but it''s taken a while, and
I''m still not
> there.
> 
> I know that the request -- "Can someone please just write a Pickaxe
for
> Rails?" -- is a lot to ask, and not a trivial undertaking.
I''m very
> grateful to those who have worked on Rails so far, both on code and
> documentation. I just think it''s important to note that I had the
exact
> same reaction when starting with rails. The API documentation is
> EXCELLENT, and in snippets gives quite good class overviews. But
it''s
> still API documentation, and neither it nor the diagram on the rails
> homepage explains how everything fits together.
> 
> If/when I grok rails I would love to contribute with a ToC for what
I''m
> looking for, and pretty diagrams which I think would help. I just
don''t
> grok it yet, and so am in the position of asking others to fix the
> problem for me, and all other newbies.
> 
> (Given my experience on the #rubyonrails IRC channel, it''s not
just
> Craig and I who have this problem. It''s a current problem for the
> majority of rails newbies. Hopefully it''s one that can be solved.)
> 
> _______________________________________________
> Rails mailing list
> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>