Jeremy, I thought I had responded earlier, but I just have to say—thank you so, so much. I am humbled by the incredible level of support you provide to your community (to say nothing of the quality of your software and documentation).
Huge fan. On Monday, October 26, 2020 at 1:31:38 PM UTC+8 Jeremy Evans wrote: > On Sun, Oct 25, 2020 at 10:18 PM Ryan Lue <[email protected]> wrote: > >> Hi Jeremy, >> >> I've got a problem and there are two possible solutions, neither of which >> I suspect is currently supported by Sequel, so let me try to pose both as >> succinctly as possible: >> >> 1. Is it possible (or, alternately, not a good idea in the first place) >> to set up many-to-many associations without a join table? >> > > You can setup a one_to_many association that for a relation that is > many_to_many. In the example below: > > ContainmentLevel.one_to_many :sites, :primary_key=>:pixel_id, > :key=>:pixel_id > > >> 2. Is it possible (or, alternately, not a good idea in the ifrst place) >> to use the CsvSerializer plugin to export non-model datasets to csv? >> > > CsvSerializer, being a plugin and not an extension, only works with model > datasets. But your code with association_left_join should work with the > association defined above. > > Thanks, > Jeremy > > >> >> *Why?* >> >> My application's DB contains historical air contaminant levels at various >> sites. Here is a simplified schema: >> >> | sites | >> |---------+-----------+----------| >> | site_id | site_name | pixel_id | >> >> | contaminant_levels | >> |----------+------+------| >> | pixel_id | date | pm25 | >> >> where pixel_id corresponds to a lat/lng coordinate on a map. (Sometimes, >> there are many sites with the same pixel_id.) >> >> I want to retrieve all contaminant data for a given site within a given >> date range, and optionally export it to CSV. Currently, I am using raw SQL: >> >> DB[<<~SQL.chomp].group_by { |record| record[:site_name] } >> SELECT site_name, date, pm25 >> FROM contaminant_levels >> LEFT JOIN sites >> ON contaminant_levels.pixel_id = sites.pixel_id >> WHERE #{other_conditions} >> SQL >> >> but the CsvSerializer plugin is for models and not datasets (AFAICT), so >> I was hoping to do something like this with models: >> >> ContaminantLevel.association_left_join(:sites) >> .where(site_name: 'French Quarter') >> .where { date < Date.new(2005, 7, 3) } >> >> but I can't make the many_to_many association work without a join table. >> Of course, it wouldn't be much work to create one: >> >> | contaminant_level_sites | >> |-----------+-------------| >> | site_id | pixel_id | >> >> but I suppose it just feels like an unnecessary indirection. >> >> *So back to the questions:* >> >> Does Sequel support what I'm trying to do? If not, is what I'm trying to >> do a bad idea? >> >> -- >> 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 view this discussion on the web visit >> https://groups.google.com/d/msgid/sequel-talk/645cbd81-b73a-45da-badb-454813e562e6n%40googlegroups.com >> >> <https://groups.google.com/d/msgid/sequel-talk/645cbd81-b73a-45da-badb-454813e562e6n%40googlegroups.com?utm_medium=email&utm_source=footer> >> . >> > -- 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 view this discussion on the web visit https://groups.google.com/d/msgid/sequel-talk/72bebccf-6d02-4452-8124-96cf6fbaa39dn%40googlegroups.com.
