Hi Everyone,
I''m trying to figure out how to do SQL queries in the Controller. I
know
this is not recommended, but I can''t use ActiveRecord because I am
dealing with thousands of a different tables, one for each user, and
I don''t think AR can be made to handle that situation.
You can assign the table name; but that''s a class variable so any
particular
derived class can only represent one table at a time (ie, you can''t
have
two ''UserTable'' objects at the same time, one representing
UserTable1000
and the other representing UserTable 1001).
Anyway, does anyone know how I can do queries inside the Controller,
or how I can hack an ActiveRecord object to work with many different
tables?
Maybe I should just make a dummy ActiveRecord object and write
class methods to return my special queries, but that seems wrong
somehow.
I would love to use active record as intended but can''t figure out
how.
Thanks,
Lee
--
Naxos Technology
On Sun, 2005-03-06 at 02:32 -0400, LN wrote:> Hi Everyone, > > I''m trying to figure out how to do SQL queries in the Controller. I know > this is not recommended, but I can''t use ActiveRecord because I am > dealing with thousands of a different tables, one for each user, and > I don''t think AR can be made to handle that situation. > > You can assign the table name; but that''s a class variable so any particular > derived class can only represent one table at a time (ie, you can''t have > two ''UserTable'' objects at the same time, one representing UserTable1000 > and the other representing UserTable 1001).While I would say that seems like a really bad layout, I think AR can still handle your needs and it would still be best to put your SQL code down in the model. There is the find_by_sql method that let you pipe in the exact SQL needed to handle your needs. The downside is you will have to implement your own find method and any other lookup methods. -- Steven Critchfield <critch-wQLwMjUOumVBDgjK7y7TUQ@public.gmane.org>
I''ve used find_by_sql in other, more normal AR objects. But it requires overriding table_name, which means that the CLASS can only handle OBJECTS that work with a single UserTableXXXX at a time. In other words I can''t simultaneously have a live object pointing to UserTable1000 and another pointing to UserTable1001, where both of those objects are of the same class. As far as it being a bad design, I have to take exception to that, it''s a legitimate design that is optimized for query speed and the need to handle billions of rows. Regardless, it''s legacy now with a large existing code base, so I''m going to have to find some way to make rails get along with it. I would really like to find a way to get active record to handle this situation, so any suggestions would be welcome. Thanks, Lee On Sun, 06 Mar 2005 01:19:26 -0600, Steven Critchfield wrote> On Sun, 2005-03-06 at 02:32 -0400, LN wrote: > > Hi Everyone, > > > > I''m trying to figure out how to do SQL queries in the Controller. I know > > this is not recommended, but I can''t use ActiveRecord because I am > > dealing with thousands of a different tables, one for each user, and > > I don''t think AR can be made to handle that situation. > > > > You can assign the table name; but that''s a class variable so any particular > > derived class can only represent one table at a time (ie, you can''t have > > two ''UserTable'' objects at the same time, one representing UserTable1000 > > and the other representing UserTable 1001). > > While I would say that seems like a really bad layout, I think AR can > still handle your needs and it would still be best to put your SQL code > down in the model. > > There is the find_by_sql method that let you pipe in the exact SQL > needed to handle your needs. > > The downside is you will have to implement your own find method and any > other lookup methods. > -- > Steven Critchfield <critch-wQLwMjUOumVBDgjK7y7TUQ@public.gmane.org> > > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails-- Naxos Technology
I''ve used find_by_sql in other, more normal AR objects. But it requires overriding table_name, which means that the CLASS can only handle OBJECTS that work with a single UserTableXXXX at a time. In other words I can''t simultaneously have a live object pointing to UserTable1000 and another pointing to UserTable1001, where both of those objects are of the same class. As far as it being a bad design, I have to take exception to that, it''s a legitimate design that is optimized for query speed and the need to handle billions of rows. Regardless, it''s legacy now with a large existing code base, so I''m going to have to find some way to make rails get along with it. I would really like to find a way to get active record to handle this situation, so any suggestions would be welcome. Thanks, Lee On Sun, 06 Mar 2005 01:19:26 -0600, Steven Critchfield wrote> On Sun, 2005-03-06 at 02:32 -0400, LN wrote: > > Hi Everyone, > > > > I''m trying to figure out how to do SQL queries in the Controller. I know > > this is not recommended, but I can''t use ActiveRecord because I am > > dealing with thousands of a different tables, one for each user, and > > I don''t think AR can be made to handle that situation. > > > > You can assign the table name; but that''s a class variable so any particular > > derived class can only represent one table at a time (ie, you can''t have > > two ''UserTable'' objects at the same time, one representing UserTable1000 > > and the other representing UserTable 1001). > > While I would say that seems like a really bad layout, I think AR can > still handle your needs and it would still be best to put your SQL code > down in the model. > > There is the find_by_sql method that let you pipe in the exact SQL > needed to handle your needs. > > The downside is you will have to implement your own find method and any > other lookup methods. > -- > Steven Critchfield <critch-wQLwMjUOumVBDgjK7y7TUQ@public.gmane.org> > > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails-- Naxos Technology
Reasonable Database schema + import script> I would really like to find a way to get active record to handle this > situation, so any suggestions would be welcome.-- Tobi http://www.snowdevil.ca - Snowboards that don''t suck http://www.hieraki.org - Open source book authoring http://blog.leetsoft.com - Technical weblog
Did you miss the part about "performance", "legacy" and "large existing code base?" I really see it as a limitation in rails that all tables must have hardcoded classes to represent them. I know you can change the table name, but you can''t have the same class represent two different tables at the same time -- or can you? Anyone care to stretch their imagination a little and try to come up with an actual creative suggestion? -Lee On Sun, 6 Mar 2005 12:30:01 -0500, Tobias Luetke wrote> Reasonable Database schema + import script > > > I would really like to find a way to get active record to handle this > > situation, so any suggestions would be welcome. > > -- > Tobi > http://www.snowdevil.ca - Snowboards that don''t suck > http://www.hieraki.org - Open source book authoring > http://blog.leetsoft.com - Technical weblog > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails-- Naxos Technology
On Sun, 6 Mar 2005 13:51:55 -0400, LN <ruby-TpSmtbjcwHBsbIuE7sb01tBPR1lH4CV8@public.gmane.org> wrote:> > Did you miss the part about "performance", "legacy" and > "large existing code base?" > > I really see it as a limitation in rails that all tables > must have hardcoded classes to represent them. I know > you can change the table name, but you can''t have the > same class represent two different tables at the same > time -- or can you?I don''t think so, but I''m not sure. Having multiple tables representing the same entity seems like a strange data model anyway, surely partitioning would be more effective? Anyway, I know the pain of silly legacy schemas.> Anyone care to stretch their imagination a little and > try to come up with an actual creative suggestion?Yes, don''t use ActiveRecord with this schema if it''s too hard to do the port. You can still use rails (ActionPack, ActionWebService, webrick, ActionMailer), you''ll just need to create your own model classes, perhaps querying your database with ruby-dbi and creating model objects manually.> -Lee > > On Sun, 6 Mar 2005 12:30:01 -0500, Tobias Luetke wrote > > Reasonable Database schema + import script > > > > > I would really like to find a way to get active record to handle this > > > situation, so any suggestions would be welcome. > > > > -- > > Tobi > > http://www.snowdevil.ca - Snowboards that don''t suck > > http://www.hieraki.org - Open source book authoring > > http://blog.leetsoft.com - Technical weblog > > _______________________________________________ > > Rails mailing list > > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > > http://lists.rubyonrails.org/mailman/listinfo/rails > > -- > Naxos Technology > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails >-- Cheers Koz
On Sun, 6 Mar 2005 10:16:23 -0800, Michael Koziarski wrote> > I don''t think so, but I''m not sure. Having multiple tables > representing the same entity seems like a strange data model anyway, > surely partitioning would be more effective? Anyway, I know the > pain of silly legacy schemas. >A more generic way to think of it is a temp-table "pattern", where you have randomly named temp tables that hold intermediate results, and thus it makes no sense to hard code a class for each possible temporary table. Thanks for the ruby-dbi suggestion, that''s probably what I should do (although I will still use AR for all the more normal parts of the DB). In fact it will probably be easier to port the old Perl code using ruby-dbi. I am going to spend the afternoon digging through the AR source to see if there is some reasonable way to get AR to do what I want. -Lee -- Naxos Technology
> I really see it as a limitation in rails that all tables > must have hardcoded classes to represent them. I know > you can change the table name, but you can''t have the > same class represent two different tables at the same > time -- or can you?Isn''t this what ActiveRecord::Aggregations does? Well, it kind of does. The mapping might be tediously verbose if AR doesn''t automagically map using existing names by default.
On 07/03/2005, at 4:51 AM, LN wrote:> Did you miss the part about "performance", "legacy" and > "large existing code base?" > > I really see it as a limitation in rails that all tables > must have hardcoded classes to represent them.More specifically its ActiveRecord that you are finding difficult to use for your application.> I know > you can change the table name, but you can''t have the > same class represent two different tables at the same > time -- or can you?You can override all the AR::Base methods, so why can''t you? You have hooks for many parts of the object lifecycle, so surely you could do what you need in there. Assuming the other tables are in the same database, and if you had read the AR documentation, you can get access to the connection by calling connection() from your AR class: http://ar.rubyonrails.org/classes/ActiveRecord/Base.html#M000194 Documentation on how to use that connection object is here: http://ar.rubyonrails.org/classes/ActiveRecord/ConnectionAdapters/ AbstractAdapter.html Documentation on the callbacks available are here: http://ar.rubyonrails.org/classes/ActiveRecord/Callbacks.html> Anyone care to stretch their imagination a little and > try to come up with an actual creative suggestion?How do you suggest AR could be changed to be more suitable for your use case? - tim lucas