davidpickering
commented
4 years ago This concept was discussed on 4/15/2020 by me and @PhilNorman2
We also need to introduce the concept of filtering results based on the service rules of an agency.
- I am an agency in zip code 43123, I only serve people in zip codes 43123, 43126, 43146.
- I am an agency in zip code 43123, I will serve anyone in a Franklin County zip code (43002, 43004, 43016, 43017, 43026, 43054, 43062, 43064, 43065, +64 additional results)
The data structure holding the service limitation information is in the event_service_geographies table. The table defines which areas the agency has stated it serves. The table holds N+1 (many) limitations for a single event:
- event_id - the event (entry in the events_table) the geography is enabled for
- geo_type_id - the type of geography the entry is for (e.g. Zip Code)
- geo_value - the specific geography the entry is for (e.g. zip code 43123)
The below SQL outlines this procedure, building off of the SQL outlined in this other issue comment.
SELECT locations.loc_name, locations.loc_nickname, events.event_id, events.event_name, events.event_nickname, events.zip FROM events INNER JOIN locations ON events.loc_id = locations.loc_id INNER JOIN ( SELECT county_from_zip.fips, county_from_zip.county, zip_codes.zip_code, zip_codes.address_percent, zip_codes.priority, zip_codes.latitude, zip_codes.longitude FROM zip_codes AS county_from_zip INNER JOIN zip_codes AS zip_codes ON county_from_zip.fips = zip_codes.fips WHERE county_from_zip.zip_code = 43054 GROUP BY zip_codes.zip_code ) AS derived_zips ON events.zip = derived_zips.zip_code INNER JOIN event_service_geographies ONevents.event_id = event_service_geographies.event_id WHERE events.status_id = 1 AND events.status_publish_event = 1 AND event_service_geographies.status_id = 1 AND event_service_geographies.geo_value = '43123'
Currently, the Beta of the Pantry Finder (Searching for resource events by zip code) does not appear to be using the correct SQL structure/logic.
Example Zip Codes that were tested, along with which pantries generated a result: 43015 - Delaware (Central OH Result set) 43123 - Grove City (Central Ohio Result Set) 43001 - Alexandria/Licking County (Central Ohio Result Set) 43017 - Dublin (Central Ohio Result Set) 43811 - Coshocton County (Central Ohio Result Set) 44023 - Cleveland Area (No Results)
Based on testing the six zip codes above we concluded that the input of the zip code is finding the foodbank, and then finding all of the agencies connected to the foodbank.
The correct process we would like to follow is outlined in this other issue comment.
The below query correctly represents the path from an input zipcode->all counties the input zipcode touches->all zip codes in those counties->all events linked to those zip codes.
The below SQL uses 43054. Test data also exists for 43123, 43008, 43030, and 43046. 43046 is a good one to see what is going on. 43046 is in Licking County. This zip code also crosses into Fairfield county. The inner select will show this logic. Then, you can use the zip_code from that inner select to tie to the events table. The main goal is then that the pantry events need to include zip codes that are part of the county of the zip code searched. The logic should NOT use the food banks table.
`SELECT locations.loc_name, locations.loc_nickname, `events`.event_name, `events`.event_nickname, `events`.zip FROM `events` INNER JOIN locations ON `events`.loc_id = locations.loc_id INNER JOIN ( SELECT county_from_zip.fips, county_from_zip.county, zip_codes_from_cnty.zip_code, zip_codes_from_cnty.address_percent, zip_codes_from_cnty.priority, zip_codes_from_cnty.latitude, zip_codes_from_cnty.longitude FROM zip_codes AS county_from_zip INNER JOIN zip_codes AS zip_codes_from_cnty ON county_from_zip.fips = zip_codes_from_cnty.fips WHERE county_from_zip.zip_code = 43054 GROUP BY zip_codes_from_cnty.zip_code ) as derived_zips ON `events`.zip = derived_zips.zip_code WHERE `events`.status_id = 1 AND `events`.status_publish_event = 1` AND events.status_publish_event_dates = 1