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?
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?
*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.
