==================== Building Recipes ==================== .. include:: ../../incomplete.rst Recipes are the heart of data services for slices. dataservices.generator module ================================ Recipe Ingredients ================== Ingredients are the raw material for making reusable SQLAlchemy queries. We define ingredients in the data service base class in the ``dimension_shelf`` and ``metric_shelf``. These ingredients can then be used across all your data services. .. code-block:: python def MyBaseService(RecipeServiceBaseV3): dimension_shelf = { # A dictionary of reusable dimensions. The key is the "id" # of the dimension and the value is a Dimension object } metric_shelf = { # A dictionary of reusable metrics. The key is the "id" # of the dimension and the value is a Metric object } automatic_filter_keys = (...) # A tuple or list of values from the dimension_shelf # these will automatically be used for filters and show up in global filters Types of Ingredients ==================== Dimension Dimensions are used for grouping data. LookupDimension A dimension that takes an expression and a python dictionary to look the values up against. For instance, the following would change state abbreviations into full U.S. state names. If the state abbreviation wasn't found "State not found" would be returned. .. code-block:: python LookupDimension(Census.state, { "AL": "Alabama", "AK": "Alaska", ... }, default="State not found") IdValueDimension A dimension that takes two expressions. The first is used as the id of an item and the second as the displayed label of that thing. Imagine you had students. There might be multiple people named "Sally Baker". These would have the same label but different ids and would show up as different values in the frontend. EncryptedDimension A dimension that is encrypted at rest in the database. It will be decrypted using a secret when it is displayed in juicebox. Encrypted dimension injects a formatter in the first formatters position that decrypts the value. Any other formatters will have access to the decrypted value. Metric Metrics count and aggregate values. SumIfMetric(conditional_expression, summing_expression) Adds up the value in ``summing_expression ``if the ``conditional_expression`` is true. For instance, to count up the total amount of sales with status='complete', you could use: .. code-block:: python SumIfMetric(MyTable.status == 'complete', MyTable.sales CountIfMetric(conditional_expression, counting_expression) Counts the number of values in ``counting_expression`` where ``conditional_expression`` is true. DivideMetric(numerator_expression, denominator_expression) Divides two expressions while avoiding problems that can occur when you divide by zero. Building a recipe =============================== You will use your ingredients to build recipes to supply data for a slice. .. code-block:: python self.dimensions = ('age', 'city') self.metrics = ('sales_dollars', 'sales_count') self.recipe().metrics(*self.metrics).dimensions(*self.dimensions)\ .filters(Filter(MyTable.state == 'Georgia')) .metrics(*list of metrics*) Add one or more metrics to the recipe. Use the keys that you defined on the ``metric_shelf``. .dimensions(*list of dimensions*) Add one or more dimensions to the recipe. Use the keys from the ``dimension_shelf``. .filters(*list of filters*) Add one or more filters to the recipe. These can be keys from the ``filter_shelf`` or filter objects you create just for the recipe using Filter(expression) .order_by(*list of dimensions or metrics to order by*) A list of dimension or metric keys that determines the order results should appear in. The default is values appear in ascending order, but if you put a '-' sign before the key, it will sort in descending order. .apply_user_filters(*True or False*) Should user filters be applied to this recipe. The default is True. .apply_stack_filters(*True or False*) Should stack filters be applied to this recipe. The default is True. .apply_automatic_filters(*True or False*) Should automatic filters be applied to this recipe. The default is True. .include_automatic_filter_keys(*a list of keys*) Limit the keys from *self.automatic_filter_keys* that will apply for automatic_filters to the provided list. There are two synonyms for this function *limit_global_filters_to* and *limit_automatic_filters_to* .exclude_automatic_filter_keys(*a list of keys*) Exclude certain keys in *automatic_filter_keys* from being applied .compare(*recipe, suffix*) Add a comparison recipe. Comparison recipes will be matched to the base recipe by the ``dimensions`` in the comparison recipe. The ``metrics`` in the comparison recipe will be added to each row with a suffix. .blend(*recipe*) Add a blend recipe. Blend recipes will be matched to the base recipe by looking at the tables used in the two recipes and matching constraints (primary keys and foreign keys). Only rows that are in both the blend recipe and the base recipe will be returned .full_blend(*recipe*) Similar to ``.blend()`` but only rows that are in the base recipe will be returned. If there is no match in the blend recipe, the blend recipe values will be None. Using a recipe ============== Once you have a recipe you can either look at the values or use it to generate the slice response. .all() A list of all rows in the data. The row will have a property for each dimension and metric used in the recipe. The will also have a property ``{{dimension_key}}_id`` which is the id for for each dimension. If formatters are used there will be a ``{{key}}_raw``, the unformatted value of that ingredient. .one(), .first() An object representing the first row returned. .render() Render the recipe in a proper response for this slice type. .to_sql() See the SQL that this recipe generated. Debugging recipes ================= In juicebox3 apps, a frontend debugging view is available. This view is visible to superusers or if the recipe renders with parameter show_debug=True. Client implementations like HealthStream can override FruitionUser.can_see_dataservice_debug to customize who sees the debug views. This debugging view is not available in HIPAA environments (where ``ALLOW_QUERY_CACHING=False`` in settings). .. code-block:: python def build_response(self): self.metrics = ('dollars', 'avg', 'gteed', 'per_gteed', 'age', 'yrs') self.dimensions = ('name', 'pos', 'team', 'start_year', 'end_year', 'free_agent', 'signed') recipe = self.recipe().metrics(*self.metrics).dimensions(*self.dimensions) self.response['responses'].append(recipe.render(show_debug=True)) .. image:: ./images/debug_view_link.png :width: 600 px The debug view is a modal dialog that shows automatic filters, custom filters, ingredients and the generated sql. .. image:: ./images/debug_view_expanded.png :width: 600 px Supporting pagination ===================== Slices can support pagination. To do this they need to do three things. - The data service for the slice must use a recipe renderer. - The recipe must include an ``.order_by()`` parameter. - Pagination must be enabled for the slice in the ``stack.yaml`` configuration. For more details see :ref:`pagination`. Here's a sample recipe that would work .. code-block:: python def build_response(self): self.metrics = ('dollars', 'avg', 'gteed', 'per_gteed', 'age', 'yrs') self.dimensions = ('name', 'pos', 'team', 'start_year', 'end_year', 'free_agent', 'signed') recipe = self.recipe().metrics(*self.metrics)\ .dimensions(*self.dimensions).order_by('name') self.response['responses'].append(recipe.render()) When the data service returns the response for the paginated slice, it needs to tell the front-end which page of data it is returning and how many total items exist. This information is injected by the data service into the response's ``config.pagination`` when the data service runs: .. code-block:: python { 'config': { 'pagination': { page: 1, pageSize: 20, totalItems: 78 }, 'data': [{ 'name': 'items', 'values': [] }], 'metadata': {}, 'name': 'Untitled', 'templateContext': {}, 'version': '3' } This data service might look like this when displayed in the front end. Here we are on the first page of a total of four. .. image:: ./images/pagination_widget.png :width: 600 px Searching and sorting in pagination ----------------------------------- Pagination may also support searching and sorting. For instance a details table slice allows users to search for a term and to sort each column. When you use pagination, this searching and sorting must happen in the recipe in the data service. Data services that support pagination will have a paginator property on the data services. You can access the user's requested search term and sort order as follows. This recipe support custom sorting and searching on all dimensions. .. code-block:: python def build_response(self): # get the optional search query and sorts from the paginator search_term = self.paginator.q sorts = self.paginator.sort # If no sorts are provided sort by nane if not sorts: sorts = ('name',) self.metrics = ('dollars', 'avg', 'gteed', 'per_gteed', 'age', 'yrs') self.dimensions = ('name', 'pos', 'team', 'start_year', 'end_year', 'free_agent', 'signed') recipe = self.recipe().metrics(*self.metrics)\ .dimensions(*self.dimensions).order_by(*sorts) self.response['responses'].append(recipe.render()) Search will automatically search all dimensions used in the recipe. If you want to limit the search to fewer values change the paginator's ``search_keys``. Here's an example that limits searching to only the ``name`` and ``team``: .. code-block:: python def build_response(self): # Limit search to name and team. Don't support custom sorting. self.paginator.search_keys = ('name', 'team') self.metrics = ('dollars', 'avg', 'gteed', 'per_gteed', 'age', 'yrs') self.dimensions = ('name', 'pos', 'team', 'start_year', 'end_year', 'free_agent', 'signed') recipe = self.recipe().metrics(*self.metrics)\ .dimensions(*self.dimensions).order_by('name') self.response['responses'].append(recipe.render())