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
_aliasattribute. That makes it possible to change the name of a property in the response, so instead ofvaluethe response will map it todeal_value - Every
belongsToproperty 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 abelongsToproperty you can also use the dot-notation as discussed for theorderByattribute.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 exampledealstatusis a option field, that returns the key of a selected option. To find any potential fitsexphas 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
}
]
}
}