Adam, I'm not a big fan of unions for just the reason you encountered. And polymorphic associations are frowned upon as they are overused in general and developers forget about accounting for their special circumstances when querying, but your use-case seems to me to be a valid one for using polymorphic associations. I'd suggest reading:
http://sequel.rubyforge.org/rdoc/files/doc/advanced_associations_rdoc.html and go to the Polymorphic Associations section and learn how to properly implement with Sequel. If you want to avoid polymorphic associations after reading the above, you have two basic ways to implement what you want... 1) have a GUID instead of a ID for the parent tables that can potentially cross paths between two parent classes. I think, but am not sure, that Postgres lets you have one sequence that can be assigned to two or more tables, which would keep your id's unique between all the tables that use the single sequence counter. If so, this is a good solution to your problem and prevents parent IDs from clashing. 2) Use composite keys (id + parent_model_name) to uniquely identify comments belonging to specific parents without worrying about IDs clashing. In large implementations, this slows down queries, so if you know you're going huge on the data, best to do composite keys as two integer fields rather than integer + varchar fields. Michael On Mon, Aug 26, 2013 at 2:38 PM, Adam Gamble <[email protected]> wrote: > Hi Jeremy et al, > > Just had a chance to try out unionising those two comments tables, however, > I notice that in creating the union the comment id's become redundant as > e.g. {id: 1} exists for both the `photo_comment` and the `photoset_comment` > tables. Is there a trick to creating mutually unique keys for tables in > union? > > Not too far into it, my query is also insufficient.. > > ``` > PhotoSetComment.where(:photoset_id => sid) > .union(PhotoComment.where(:photo_id => pid)) > ``` > > Whereas I actually need comments for each `Photo.where(:photoset_id => > sid)`. Should I chain unions to do this, or does Ruby have some nice syntax > to enumerate each Photo into a union? > > Thanks, > > Adam > > > On Thursday, 22 August 2013 16:22:00 UTC+1, Adam Gamble wrote: >> >> Many thanks :) >> >> On Thursday, 22 August 2013 16:09:44 UTC+1, Jeremy Evans wrote: >>> >>> On Thursday, August 22, 2013 2:49:59 AM UTC-7, Adam Gamble wrote: >>>> >>>> Thanks Jeremy– Unions may be a good call, equally it'd be nice to have >>>> just one 'Comment' table.. so to explain my situation I have a table >>>> 'PhotoSet' and a 'Photo' table.. a user should be able to comment on a Set, >>>> or a Photo –but for the purposes of the application the two should be >>>> aggregated in a feed. >>>> >>>> As Photo's are many-to-one with the PhotoSet. I could have comments just >>>> linked to the PhotoSet, where a comment can reference a Photo, otherwise it >>>> becomes a general comment on the set.. make sense? >>> >>> >>> I personally think you would be better off with both photo_comments and >>> photoset_comments tables. You don't want a foreign_key in your photoset >>> table referencing the comments table, since that lets you only have a single >>> comment per photoset. Instead, the photo_comments table would have a >>> foreign key referencing photos, and the photoset_comments table would have a >>> foreign key referencing photosets. This allows you to have multiple >>> comments per photo and per photoset. >>> >>> Thanks, >>> Jeremy > > -- > You received this message because you are subscribed to the Google Groups > "sequel-talk" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [email protected]. > To post to this group, send email to [email protected]. > Visit this group at http://groups.google.com/group/sequel-talk. > For more options, visit https://groups.google.com/groups/opt_out. -- http://codeconnoisseur.org -- You received this message because you are subscribed to the Google Groups "sequel-talk" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/sequel-talk. For more options, visit https://groups.google.com/groups/opt_out.
