Add support for cell_methods to explorer

[aidanheerdegen]

Now that cell_methods are properly supported in querying (#252) need to expose this information and allow it to be selected in ExperimentExplorer.

[aidanheerdegen]

@angus-g I've started looking into how this might be done, and gone down quite the rabbit hole of SQLAlchemy queries.

When a variable is selected in the explorer I need to populate a drop-down menu of possible options for cell_methods.

As far as I can tell it isn't possible to select an item of an association table, but it is possible to filter on them, so I can construct a query like this:

import cosima_cookbook as cc
from cosima_cookbook.database import NCExperiment, CFVariable, NCFile, NCVar, NCAttribute
session = cc.database.create_session()
experiment = '01deg_jra55v140_iaf'
variable = 'sea_level'
q = (
    session.query(
        NCVar
    )
    .join(NCFile.experiment)
    .join(NCFile.ncvars)
    .join(NCVar.variable)
    .filter(NCExperiment.experiment == experiment)
    .filter(CFVariable.name == variable)
    .filter(NCVar.attrs.any(NCAttribute.name=='cell_methods'))
)

and then iterate over the result to get all the cell_methods:

print({res.attrs['cell_methods'] for res in q.all()})

with the correct result:

{'time: mean', 'time: point'}

Is there a way to do this query directly?

[angus-g]

Yes, to make it a little more readable (and avoid a million joins), you could do something like the following to set up a subquery:

subq = (
    session.query(NCVar.id)
    .join(NCFile.experiment)
    .join(NCFile.ncvars)
    .join(NCVar.variable)
    .filter(NCExperiment.experiment == experiment)
    .filter(CFVariable.name == variable)
).subquery()

And then just query out the value attribute as you want. You can do it directly with a single query (but you get a tuple result for each row, I still don't know how to get it to not do that...):

q = (
    session.query(NCAttributeString.value)
    .join(NCAttribute._value)
    .join(subq)
    .filter(NCAttribute.name == "cell_methods")
    .group_by(NCAttribute.value_id)
)

Or you can skip the extra join and just get an NCAttribute object in each row, which will automatically lazily query out the value, i.e.

q = (
    session.query(NCAttribute)
    .join(subq)
    .filter(NCAttribute.name == "cell_methods")
    .group_by(NCAttribute.value_id)
)

{res.value for res in q.all()}

[aidanheerdegen]

Awesome, thanks.