Hello, I''m trying to use a view in sqlite to display my data. I can''t use ActiveRecord''s object-relation mapping because my relations go more than one level out. I have a central table.. which has a foreign key to table2.. table2 has a foreign key to table3 and table3 has a foreign key to table4. I''ve heard that I can''t map more than one level deep. To get around this problem (and not have to put sql in my controller and break OO-design principals) I''ve created a view that does all the required joins. I then created a model that maps on to that view. The problem is that when I display data out of that model all the rows come back as "0.0". Is there some special option I need to get views working? Thanks, Geoffeg
On May 2, 2005, at 12:10 PM, Geoffrey Gallaway wrote:> Hello, > > I''m trying to use a view in sqlite to display my data. I can''t use > ActiveRecord''s object-relation mapping because my relations go more > than > one level out. I have a central table.. which has a foreign key to > table2.. table2 has a foreign key to table3 and table3 has a foreign > key > to table4. I''ve heard that I can''t map more than one level deep. To get > around this problem (and not have to put sql in my controller and break > OO-design principals) I''ve created a view that does all the required > joins. I then created a model that maps on to that view. The problem is > that when I display data out of that model all the rows come back as > "0.0". Is there some special option I need to get views working? >Not sure about that, but there is another option: put the specialized SQL in a method of your model: class MyModel < ActiveRecord::Base def complex_relation MyModel.find_by_sql("SELECT ...") end end Would that work? - Jamis
> Not sure about that, but there is another option: put the specialized > SQL in a method of your model: > > class MyModel < ActiveRecord::Base > def complex_relation > MyModel.find_by_sql("SELECT ...") > end > end > > Would that work? >It would, but I really prefer to keep this kind of thing in the database (the view). :)> - Jamis > > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails >
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Geoffrey Gallaway wrote:> It would, but I really prefer to keep this kind of thing in the database > (the view). :)That doesn''t make sense.. the database is the Model in MVC structure... - -- David Morton Maia Mailguard server side anti-spam/anti-virus solution: http://www.maiamailguard.com -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.5 (GNU/Linux) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFCdpbfSIxC85HZHLMRAsxIAJwMWH4gOBvRkCPgy7li32jah7ZjnwCfQVoa ZRUZfNZeWFHe+Nfq3DKUTn8=Ew43 -----END PGP SIGNATURE-----
On 5/3/05, Geoffrey Gallaway <geoffeg-jOAbkWVBHf1AfugRpC6u6w@public.gmane.org> wrote:> Hello, > > I''m trying to use a view in sqlite to display my data. I can''t use > ActiveRecord''s object-relation mapping because my relations go more than > one level out. I have a central table.. which has a foreign key to > table2.. table2 has a foreign key to table3 and table3 has a foreign key > to table4. I''ve heard that I can''t map more than one level deep. To get > around this problem (and not have to put sql in my controller and break > OO-design principals) I''ve created a view that does all the required > joins. I then created a model that maps on to that view. The problem is > that when I display data out of that model all the rows come back as > "0.0". Is there some special option I need to get views working?No, as far as I''m aware what you''re doing should be fine. Have a look at the SQL that ActiveRecord is generating, it''ll be in development.log. Run that SQL from the sqlite command prompt and look at the data that''s returned However there really is no need to do all this denormalisation> I have a central table.. which has a foreign key to > table2.. table2 has a foreign key to table3 and table3 has a foreign key > to table4. I''ve heard that I can''t map more than one level deepYou can''t map *one* class to 4 tables without a view. But given that you have 4 separate entities, you should really have 4 classes (at least). Then you can navigate the associations as needed.> Thanks, > Geoffeg > > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails >-- Cheers Koz
> Geoffrey Gallaway wrote: > > It would, but I really prefer to keep this kind of thing in the database > > (the view). :) > > That doesn''t make sense.. the database is the Model in MVC structure... >I meant the database view.> > - -- > David Morton > Maia Mailguard server side anti-spam/anti-virus solution: > http://www.maiamailguard.com > -----BEGIN PGP SIGNATURE----- > Version: GnuPG v1.2.5 (GNU/Linux) > Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org > > iD8DBQFCdpbfSIxC85HZHLMRAsxIAJwMWH4gOBvRkCPgy7li32jah7ZjnwCfQVoa > ZRUZfNZeWFHe+Nfq3DKUTn8> =Ew43 > -----END PGP SIGNATURE----- > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails