rubyforgood/abalone

Report: Count of live animals in the program that are larger than 25mm

Closed this issue · 8 comments

CraigJZ commented

Create queries that return a count of live animals in the program that are larger than 25mm by cohort, by facility and by organization.

Data for this is collected on a subset on animals and would need to be extrapolated to a full group. Some discussion with the stakeholder may be needed to ensure this is done accurately.

Depends on #715 (making mortality available to Blazer queries)

web-kat commented

Claiming 🥳

CraigJZ commented

Thanks @web-kat!!

web-kat commented

Hey @CraigJZ, when you have a second I have a few questions:

  1. Do we want to return a count for a specific cohort / organization / facility OR for all of the available cohorts / organizations / facilities?
  2. If we do want the count for a specific cohort / organization / facility, what field do we want to look these up by? Name?
  3. I see that an animal can have many measurements, is it safe to assume that the last measurement for an animal is their current measurement?
CraigJZ commented

Hi @web-kat. Apologies for the delayed reply. I've added answers for each questions below 😄

  1. A count for a specific facility would be ideal
  2. For similar queries, I've been using id, but name would work too and name is probably more intuitive for stakeholders to use as an example for future queries.
  3. Yes, that is a safe assumption.

If you need any additional info or have other questions, let me know. 😄

web-kat commented

Pull request to add seed data opened: here

  • 👍1
web-kat commented

Screen Shot 2021-10-13 at 3 14 13 PM

@CraigJZ here is a screenshot of the queries that could be used to return the count specified in this issue by the desired subset. We've made it flexible so that it is easy to replace the organization ID, facility ID, or cohort ID (the associated measurement IDs will need to be updated as well)

Let me know if this solves the problem listed in the issue and/or if you would like to talk through these queries some more.

  • 👍1
CraigJZ commented

Sorry - I accidentally closed this when I merged the associated PR.

Yes! This looks like it will resolve the issue. The flexibility of it is really helpful too. 😄

I don't think we need to check the animals.organization_id on line 29 because the row level security should prevent returning data from other organizations. Having it there won't hurt anything, though, so I'd be cool with leaving it as is.

I've added the new seed data to the staging server, so this one should be ready to add there. Thank you for all the work on this issue!

CraigJZ commented

Added to staging app 🎉