On Tuesday, March 31, 2015 at 12:30:08 PM UTC-7, Frank Kjerstein wrote: > > I am still new to Ruby, Sinatra, Sequel and PostgreSQL. So my wordings, > concepts, understanding and explanations may be a bit "off". > > I need an optimization to keep running on Heroku and stay under the 10.000 > row limitation. So I enjoyed the presentation on pg_array and > pg_array_associations, of course. > > Anyway, my question goes like this: > > Can I use pg_array and pg_array_associations to eliminate a join table AND > still keep a single attribute associated with the referenced_id? > > Right now I have this join table: > DB.create_table :user_trackers do > foreign_key :user_id, :users, null: false > foreign_key :tracker_id, :trackers, null: false > primary_key [:user_id, :tracker_id], :unique=>true > column :timestamp, DateTime, null: false > end > > It's the join table that fills up the Heroku database, not the user table > or tracker table. > > *Solution needed* > I wish to use pg_array_associations to store the tracker_ids in the user > table rows, *along with the timestamp* for each tracker_id. > > Is this possible/feasible? > > I guess this would perhaps need two pg_arrays in the user table rows, one > for tracker_ids and their corresponding timestamps, and these pg_arrays > should then be kept in *identical order**, so I can zip these two arrays > together to find the timestamp for each tracker_id? > > * *so that entry 1 in the tracker_ids pg_array has its timestamp > attribute as entry 1 in the timestamp pg_array, and entry 2 in the > tracker_ids pg_array has its timestamp attribute as entry 2 in the > timestamp pg_array, and so on.* > > There is probably a lot wrong with my approach and (untested) > understanding of how to implement and use pg_array and > pg_array_associations. > > I'm very open to being educated. >
I would advise against keeping two pg_arrays and trying to keep them associated the the way you described. Instead, have a single pg_array of a composite type that contains a foreign key referencing the other table, and a timestamp. That's a better way to model it, IMO. You won't be able to use the pg_array_associations plugin, but Sequel supports building and parsing the array of composite types. You can use a similar approach to the one pg_array_associations uses to get all associated records without using a join table, and figure out the timestamp for each associated record by looking for the entry for the matching foreign key in the pg_array. Hopefully that helps, if not, please ask followup questions. 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/d/optout.
