On current fake data, produces output like the following: {Cell(id=3, name='C0001'), Cell(id=1, name='C0001')}. Use with python -i to explore. Needs obvious expansion/generalization for other resources.

filter-by-resolved-vars.py

from collections import defaultdict
import sys

from sqlalchemy import create_engine  # change to oslo_db
from sqlalchemy.orm import sessionmaker
from sqlalchemy.sql.expression import tuple_

from craton.db.sqlalchemy import models

engine = create_engine(sys.argv[1] if len(sys.argv) > 1 else 'mysql+pymysql://craton:craton@localhost/craton') #, echo=True)
Session = sessionmaker(bind=engine)
session = Session()


def matching_resources(resource_cls, get_descendants, kv):
    if not kv:
        # perhaps throw an error here: we really should not be called
        # if kv is empty, because no vars= query
        return set()

    kv_pairs = list(kv.items())
    matches = defaultdict(set)
    
    # this query can be readily generalized by doing things like json matches, or just testing for the presence of a key;
    # but for now just find all variables that explicitly match one or more key value pairs
    q = session.query(models.Variable).\
        filter(tuple_(models.Variable.key, models.Variable.value).in_(kv_pairs))

    for variable in q:
        match = matches[(variable.key, variable.value)]
        if isinstance(variable.parent, resource_cls):
            match.add(variable.parent)
        match.update(get_descendants(variable.parent))

    if not matches:
        return set()
    _, first_match = matches.popitem()
    if matches:
        return first_match.intersection(*matches.values())
    else:
        return first_match


# change into some sort of lookup table as we generalize

def matching_cells(kv):
    def get_desc(parent):
        if isinstance(parent, models.Project) or isinstance(parent, models.Region) or isinstance(parent, models.Cloud):
            return parent.cells
        else:
            return []

    return matching_resources(models.Cell, get_desc, kv)



print(matching_cells({"openstack_release": "juno", "cell_capabilities": "flavor_classes=performance2"}))