Skip to content

Lime query

Lime query is a tool to easily gather a particular set of limeobjects. This tutorial provides an introduction to how to use it and to the format that is required for a query.

Execute a query

The result of any of the following queries can be tested by calling lime_query.execute_query like this:

import lime_query

lime_query.execute_query(query, limeapp.database.connection, 
                            limeapp.limetypes, limeapp.acl, limeapp.user)
limeapp has to be a lime_application.application.LimeApplication

A simple query

The simplest query needs to contain at least a limetype and a responseFormat.

query = {
    'limetype': 'deal',
    'responseFormat': {
        'object': {
            'name': None,
            'value': None,
            'company': None
        }
    }
}

This query requests all objects of the limetype deal and expects to get name, value and company in the response. All keywords within object have to be valid property names.

It might look confusing that all the property values are set to None. But this only says, that the query has no closer specification for a property. In case of a simple type like a string, None is gonna be replaced by its value in the response. If it’s a belongsTo field like company, the returned value is the primary key. hasmany and hasandbelongstomany fields are not supported as output properties.

The query above should resolve in a response format like this:

{
    'objects': [
        {
            'name': '20 cykelkomponenter',
            'value': 0.0,
            'company': 1011
        },
        {
            'name': 'Basemetrics Oy - järjestelmän käyttöönotto',
            'value': 50000.0,
            'company': 1009
        },
        {
            'name': 'Basemetrics Oy - tekninen konsultointi',
            'value': 20000.0,
            'company': 1009
        },
    ]
}

More specifications on a property

Let’s have a look at how you can specify output properties in more detail:

query = {
    'limetype': 'deal',
    'responseFormat': {
        'object': {
            'name': None,
            'value': {
                '_alias': 'deal_value'
            },
            'company': {
                'name': None
            }
        }
    }
}
  • Every property accepts an _alias attribute. That makes it possible to change the name of a property in the response, so instead of value the response will map it to deal_value
  • Every belongsTo property can be specified by properties of that limetype. The response now will return a company object with the company’s name.

Limit and offset

As mentioned earlier the query is supposed to return all objects of a limetype. But maybe you have already realized that the response returns less objects than existing entries. That’s because the query gets executed with a default limit of 100. In order to change that you can specify your own limit attribute in the query.

To enable paging of a result, you can also add a offset property. This will skip the first X entries of a result.

With those attribute included the query now looks like this:

query = {
    'limetype': 'deal',
    'responseFormat': {
        'object': {
            'name': None,
            'value': {
                '_alias': 'deal_value'
            },
            'company': {
                'name': None
            }
        }
    },
    'limit': 50,
    'offset': 10
}

Order your result

Instead of a result you should have gotten the following compile error when executing this last query: MSSQL requires an order_by when using an OFFSET or a non-simple LIMIT clause. Let’s fix this by adding a orderBy specification to the query.

query = {
    'limetype': 'deal',
    'responseFormat': {
        'object': {
            'name': None,
            'value': {
                '_alias': 'deal_value'
            },
            'company': {
                'name': None
            }
        }
    },
    'limit': 50,
    'offset': 10,
    'orderBy': [
        {'company.name': 'ASC'},
        {'probability': 'DESC'},
    ]
}

You can either order by simple property types or describe a simple type of a relation property with a dot-notation. In this case the result is gonna be ordered ascending by the the company’s name. If it’s the same name, the probability of the deal decides the secondary order (descending). ASC and DESC are the only valid values for this.

Add a filter

In most cases you might only be interested in limeobjects that fit a specific condition, so let’s look into how we can filter them.

A simple expression

If you only have one condition for your query, you can add a filter with an expression object like this:

query = {
    'limetype': 'deal',
    'responseFormat': {
        'object': {
            'name': None,
            'value': {
                '_alias': 'deal_value'
            },
            'company': {
                'name': None
            }
        }
    },
    'limit': 50,
    'offset': 10,
    'orderBy': [
        {'company.name': 'ASC'},
        {'probability': 'DESC'},
    ],
    'filter': {
        'key': 'dealstatus',
        'op': '=',
        'exp': 'contact'
    }
}

Notice here, that key, op and exp are all mandatory and require specific values:

  • key: its value has to fit a property name of the limetype. On a belongsTo property you can also use the dot-notation as discussed for the orderBy attribute.
  • op: describes the operation, that should be executed on the property. It’s required to have one of the following values: AND, OR, =? (like or equals), = , >, >=, IN,<, <=, ?, != (not equals), ! (logical negation)
  • exp: the value to compare with. This obviously has to match the type of the property. In our example dealstatus is a option field, that returns the key of a selected option. To find any potential fits exp has to match the key of one of the options on the property.

In short the query above returns limeobjects with a dealstatus that’s set to contact.

Combine several expressions

If one expression isn’t enough to describe the subset of limeobjects you want you can also combine several expressions in the same filter:

query = {
    'limetype': 'deal',
    'responseFormat': {
        'object': {
            'name': None,
            'value': None,
            'quotesent': None,
        }
    },
    'limit': 50,
    'offset': 10,
    'orderBy': [
        {'company.name': 'ASC'},
        {'probability': 'DESC'},
    ],
    'filter': {
        'op': 'OR',
        'exp': [
            {
                'key': 'value',
                'op': '>',
                'exp': 10000
            },
            {
                'key': 'quotesent',
                'op': '>',
                'exp': '2016-01-01T00:00:00+02:00'
            }
        ]
    }
}

Instead of a single expression they’re now listed in an array, but each expression on its own still has to follow the same rules as discussed earlier. In addition you need to define whether the expressions should be combined with an AND or an OR operation.

Use a existing filter

A lime application can manage a list of filters. You can find all existing filters with this endpoint <DOMAIN>/<APP-NAME>/api/v1/filter as well as the conditions of a specific filter by calling <DOMAIN>/<APP-NAME>/api/v1/filter/<FILTER-ID>.

Instead of repeating the conditions in your query, you can get a filter and insert it.

import lime_filter


repo = lime_filter.get_repository(limeapp)
filter = repo.get_filter('webclient.deal.pipeline')

query = {
    'limetype': 'deal',
    'responseFormat': {
        'object': {
            'name': None,
            'value': {
                '_alias': 'deal_value'
            },
            'company': {
                'name': None
            }
        }
    },
    'limit': 50,
    'offset': 10,
    'orderBy':[
        {'company.name': 'ASC'},
        {'probability': 'DESC'},
    ]
}

query['filter'] = filter.dsl

Add aggregates

Last but not least lime-query supports operations on the complete dataset which can be described as aggregates. Before we go through every possible operation, let’s discuss the general format first:

In addition to object the responseFormat accepts the key aggregates. The next two levels of keywords can be completely freely chosen. They are going to be included in the response, so choose something that’s fits the data structure you’re working with.

query = { 
    'limetype': 'deal',
    'responseFormat': {
        'aggregates': {
            'describeSetOfOperations': {
                'describeOperation': {
                    'op': 'SUM',
                    'key':'value' 
                },
                    'describeAnotherOperation': {
                        'op': 'AVG',
                        'key': 'value' 
                }
            },
        },
    }, 
    'filter': {
        'key': 'dealstatus',
        'op': '=',
        'exp': 'contact' 
    }
}

The response format above would resolve into this response:

{
    'aggregates': {
        'describeSetOfOperations': [
            {
                'describeOperation': 1000000.0,
                'describeAnotherOperation': 58823.529411764706
            }
        ]
    }
}

Each operation then needs an op attribute that can be COUNT, GROUP, SUM or AVG and in most cases a key describing the property that should be used in the operation.

SUM operation

As you might expect the SUM operator adds up all values for a property. If the type of the property doesn’t support an addition, this operation can return a OperationalError.

AVG operation

The AVG operator calculates the average for all values of a property. This can resolve in a OperationalError, as already discussed for the SUM operator.

MAX operation

The MAX operator takes the highest value of a property. This can resolve in a OperationalError, as already discussed for the SUM operator.

MIN operation

The MIN operator takes the smallest value of a property. This can resolve in a OperationalError, as already discussed for the SUM operator.

COUNT operation

The COUNT operation simply returns the amount of entries that matches the query. This ignores limit and offset, so the number doesn’t have to fit the amount included in objects. Since this doesn’t concerns a specific property, it’s the only operation that works without a key attribute.

    query = {
        'limetype': 'deal',
        'responseFormat': {
            'aggregates': {
                'all': {
                    'count': {
                        'op': 'COUNT',
                    },
                },
            },
        },
        'filter': {
            'key': 'dealstatus',
            'op': '=',
            'exp': 'contact'
        }
    }

The response for this would be:

{'aggregates': {'all': [{'count': 17}]}}

GROUP operation

The GROUP operator return an object for every unique value it finds for a specific property. Mostly you want to combine this with at least one other operation in the same operation set, because those extra operations are going to be executed for each group.

This query

query = {
    'limetype': 'deal',
    'responseFormat': {
        'aggregates': {
            'grouped': {
                'status': {
                    'op': 'GROUP',
                    'key': 'dealstatus'
                },
                'count': {
                    'op': 'COUNT'
                },
                'value_sum': {
                    'op': 'SUM',
                    'key': 'value'
                }
            },
        },
    },
    'filter': {
        'key': 'value',
        'op': '>',
        'exp': 10000
    }
}

will return this response:

{
    'aggregates': {
        'grouped': [
            {
                'status': 'contact',
                'count': 1,
                'value_sum': 1000000.0
            },{
                'status': 'tender', 
                'count': 2, 
                'value_sum': 520000.0
            },{
                'status': 'agreement', 
                'count': 1, 
                'value_sum': 50000.0
            }
        ]
    }
}