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/CADGZSScNA1XN9ZaBZP8b-_-rqfgPa%3DEVn2mX3xB5WbO17s-Yfw%40mail.gmail.com.
