Does ActiveRecord have a way of handling multiple field keys?
e.g. To pull the desired record out of table b i do:
select * from b where a.k1=b.k1 and a.k2 = b.k2
craig duncan wrote:> Does ActiveRecord have a way of handling multiple field keys? > > e.g. To pull the desired record out of table b i do: > select * from b where a.k1=b.k1 and a.k2 = b.k2Replying to my own message... no one has a clue about this? I searched through the docs and also (tried to) look at the code... to no avail. Even if there''s no built in provision for this there must be some way of handling this better than just pulling out all the records matching one of the keys and then selecting (in ruby) the record that matches the other key field. craig
On 22 Apr 05, at 10:07 PM, craig duncan wrote:> craig duncan wrote: >> Does ActiveRecord have a way of handling multiple field keys? >> e.g. To pull the desired record out of table b i do: >> select * from b where a.k1=b.k1 and a.k2 = b.k2 > > Replying to my own message... no one has a clue about this? > I searched through the docs and also (tried to) look at the code... to > no avail. > Even if there''s no built in provision for this there must be some way > of handling this better than just pulling out all the records matching > one of the keys and then selecting (in ruby) the record that matches > the other key field.I feel like I''m missing something obvious, but doesn''t the :conditions options do what you want? b.find(:all, :conditions => "a.k1 = b.k1 AND a.k2 = b.k2") Or you could create a view in the database. -- -asbjxrn
Asbjørn Bjørnstad wrote:> > I feel like I''m missing something obvious, but doesn''t the :conditions > options do what you want?Maybe you''re not the one missing the "obvious". It hadn''t occurred to me that i could use sql in the :conditions argument which refers to fields unrelated to the class/table context of the object being accessed. In other words, the example in the documentation shows part of a WHERE clause where every field involved is from the table corresponding to the object whose find method is being called. What you are implying is that i can put _anything_ i want to in the :conditions argument, via explicit reference to other tables (by actual table name)? I had *no* idea. I suppose it makes sense to have this be the fallback if the simplistic access mechanisms aren''t powerful enough. craig
On 22 Apr 05, at 10:48 PM, craig duncan wrote:> Asbjørn Bjørnstad wrote: >> I feel like I''m missing something obvious, but doesn''t the >> :conditions options do what you want? > > Maybe you''re not the one missing the "obvious". It hadn''t occurred to > me that i could use sql in the :conditions argument which refers to > fields unrelated to the class/table context of the object being > accessed. In other words, the example in the documentation shows part > of a WHERE clause where every field involved is from the table > corresponding to the object whose find method is being called. What > you are implying is that i can put _anything_ i want to in the > :conditions argument, via explicit reference to other tables (by > actual table name)? I had *no* idea. I suppose it makes sense to > have this be the fallback if the simplistic access mechanisms aren''t > powerful enough.doh, I think you might be right, you can''t do it that way as table "a" will be missing from the "FROM" part of the query. But there is always find_by_sql... b.find_by_sql("SELECT b.* FROM a, b WHERE a.k1 = b.k1 AND a.k2 = b.k2") -- -asbjxrn
Craig, your original message had a few gaps in that you didn''t clearly state the result you were looking for. When you say "multi-field" key it sounds like what I''d call a composite key... Patient: it hurts when I try to put my ankle behind my head Doctor: well, don''t do that ActiveRecord (AR) makes the most common relationship cases simple - each row has a unique identity that is expressed in a single field, out-of-the-box relationships are based on the single-field key. If the relationship between two tables depends on a.x = b.x and a.y = b.y you *might* be going beyond AR''s out-of-the-box goodness for transparently maintaining relationships. That''s not to say it can''t be done, it just means that there may be extra work needed - anything ranging from a tweak to your association definitions (has_many et al) to actively fighting the framework every step of the way... (which imho falls into the category of "well, don''t do that"). So, fill in the gaps - describe your models, the relationships you''re trying to create and some sample SQL that would give you the records you''re looking for in a certain scenario. Perhaps then someone can comment on how to proceed and what the trade-offs might be. Trevor On 22-Apr-05, at 7:48 AM, craig duncan wrote:> Asbjørn Bjørnstad wrote: >> I feel like I''m missing something obvious, but doesn''t the >> :conditions options do what you want? > > Maybe you''re not the one missing the "obvious". It hadn''t occurred to > me that i could use sql in the :conditions argument which refers to > fields unrelated to the class/table context of the object being > accessed. In other words, the example in the documentation shows part > of a WHERE clause where every field involved is from the table > corresponding to the object whose find method is being called. What > you are implying is that i can put _anything_ i want to in the > :conditions argument, via explicit reference to other tables (by > actual table name)? I had *no* idea. I suppose it makes sense to > have this be the fallback if the simplistic access mechanisms aren''t > powerful enough. > > craig > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails
On 4/22/05, Trevor Squires <trevor-k8q5a0yEZAgS+FvcfC7Uqw@public.gmane.org> wrote:> > If the relationship between two tables depends on a.x = b.x and a.y > b.y you *might* be going beyond AR''s out-of-the-box goodness for > transparently maintaining relationships. > > That''s not to say it can''t be done, it just means that there may be > extra work needed - anything ranging from a tweak to your association > definitions (has_many et al) to actively fighting the framework every > step of the way... (which imho falls into the category of "well, don''t > do that"). >The advice of "don''t do that" makes a lot of sense for new development, but I think your going to run into this a lot with legacy database schemas. it''s pretty standard in traditional database design to use multiple keys to express parent/child relationships. One possibility in these cases is to add a new primary key (keep the old key as non-key data). You might also need to add additional foriegn key fields as well to refer back to a multi-key parent. _______________________________________________ Rails mailing list Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org http://lists.rubyonrails.org/mailman/listinfo/rails
Trevor Squires wrote:> Craig, > > your original message had a few gaps in that you didn''t clearly state > the result you were looking for.I wasn''t looking for results. I was looking for information. Does AR have any support for this?> When you say "multi-field" key it > sounds like what I''d call a composite key...Yes... i think that''s the common term for this (couldn''t remember it offhand).> If the relationship between two tables depends on a.x = b.x and a.y = > b.y you *might* be going beyond AR''s out-of-the-box goodness for > transparently maintaining relationships.Well, that''s a question, then, isn''t it?> That''s not to say it can''t be done, it just means that there may be > extra work needed - anything ranging from a tweak to your association > definitions (has_many et al) to actively fighting the framework every > step of the way... (which imho falls into the category of "well, don''t > do that").But composite keys are (imo) fairly common. So... does AR support them? I know how i could change my application. But i want to know what tools i have to work with first. craig
craig duncan wrote:> Trevor Squires wrote: >> your original message had a few gaps in that you didn''t clearly state >> the result you were looking for. > > I wasn''t looking for results. I was looking for information. Does AR > have any support for this?Active Record was built with the expectation of single-column integer auto-incrementing primary keys. This provides a clear, consistent convention for new apps-- a common theme with Rails-- but does not adequately address the needs of legacy databases or of relational purists. But, good news: there''s no fundamental limitation preventing legacy support. Any of these restrictions may be relaxed as soon as someone with the need adds (or sponsors) the necessary support to AR. Best, jeremy
On Apr 23, 2005, at 2:26, Jeremy Kemper wrote:> This provides a clear, consistent convention for new apps-- a common > theme with Rails-- but does not adequately address the needs of legacy > databases or of relational purists. > > But, good news: there''s no fundamental limitation preventing legacy > support. Any of these restrictions may be relaxed as soon as someone > with the need adds (or sponsors) the necessary support to AR. >I don''t care about the relational purists (does anybody?). However, i do care about legacy applications and the notion that maybe something or somebody is restricting me from mucking with the database. Mine might be one of hundreds of applications accessing a database... adding an id field might not be an option. This issue is one of the major show stoppers preventing us to adapt Rails full scale - and i get the feeling we won''t be the only ones. Now, i also believe that it should be pretty easy to add. All we need is an additional layer of abstraction for ''id''. Nik _______________________________________________ Rails mailing list Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org http://lists.rubyonrails.org/mailman/listinfo/rails
On Sun, 2005-04-24 at 17:51 +0700, nikolaus heger wrote:> Now, i also believe that it should be pretty easy to add. All we need > is an additional layer of abstraction for ''id''.Actually this would be a rather messy situation. First, it''s not only the ''id'' that would need adjustment - all the areas that store the column which contains the ''id'' would need to be modified. We would also need to deal with how we would auto-increment the fields (and which ones would be auto-incremented). Also, the entire associations area would need major modifications to handle the concept of multiple key fields. This would also need a complete duplication of the majority of tests in order to make sure this doesn''t break anything along the way with existing/new adapters. Impossible? Nope - but it is not a simple task either.... John W Higgins develop-U23jnKMpDSxBDgjK7y7TUQ@public.gmane.org> > Nik > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails