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 fields are only
supported to get aggregated data from the related limetype and is discussed in
the section Relation aggregates below.
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¶
lime-query also 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:
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
}
]
}
}
Relation aggregates¶
Beta Feature flag
It's also possible to use aggregates on hasmany properties, both to get
calculated values and to filter on those values. For example, it's possible to
create a query that will fetch all persons that have more than five active
deals and get the total sum of the value of the deals connected to each person.
The syntax for the relation aggregates is similar to the rest of the query
syntax. When a hasmany property is specified in the responseFormat section,
it requires an object to be specified that describes the response format of the
aggregates, and what to aggregate on.
query = {
'limetype': 'person',
'responseFormat': {
'object': {
'name': None,
'deal': {
'<KEY>': '<HASMANY-DSL>'
}
}
}
}
<KEY>here can be anything as long as it is not the same name as a property on the related limetype. It will both be used in the response format of the result, and can also be used to reference the result when filtering on the aggregated value.<HASMANY-DSL>specifies what to aggregate on.
Counting all deals for each person
The aggregate object specifies that the total number of deals for each
person should be returned for the count key.
The following query
query = {
'limetype': 'person',
'responseFormat': {
'object': {
'name': None,
'deal': {
'count': {
'aggregate': {
'op': 'COUNT'
}
}
}
}
}
}
will return something like the following
Specifying type of aggregate¶
When specifying what to aggregate on, the same aggregates and syntax as in the
general aggregates section can be used. As with the general aggregations,
SUM, AVG, MIN and MAX also requires a key attribute specifying what
property to aggregate on. COUNT does not require the key attribute.
Summarize the value of all deals for each person
This example builds upon the previous one. Another key has been added that will include the sum of all the deals.
The following query
query = {
'limetype': 'person',
'responseFormat': {
'object': {
'name': None,
'deal': {
'count': {
'aggregate': {
'op': 'COUNT'
}
},
'sum': {
'aggregate': {
'op': 'SUM',
'key': 'value'
}
}
}
}
}
}
will return something like the following
Note
The GROUP operator is not available for use when aggregating on relation
properties
Specifying a filter for the aggregates¶
It is possible to include a filter for the aggregates to limit the objects that
are used to calculate the aggregated value. E.g. in the example above, perhaps
only deals with a specific status should be included. This can be achieved by
including the filter attribute. The syntax for the filter attribute is
exactly the same as for the generic filter for the whole query.
Aggregate on specific deals
In this example, the query from the previous examples has been modified to
only aggregate on deals where dealstatus is equal to agreement.
query = {
'limetype': 'person',
'responseFormat': {
'object': {
'name': None,
'deal': {
'count': {
'aggregate': {
'op': 'COUNT'
},
'filter': {
'key': 'dealstatus',
'op': '=',
'exp': 'agreement'
}
},
'sum': {
'aggregate': {
'op': 'SUM',
'key': 'value'
},
'filter': {
'key': 'dealstatus',
'op': '=',
'exp': 'agreement'
}
}
}
}
}
}
Note
The aggregated values are calculatd using subqueries, and the context for
these subqueries will be the related property. This means that there is no
need to specify the relation name on the keys in the filter, the property
name on the relation is enough. E.g. even though the top query will be
selecting persons, dealstatus is enough to use on the filter for the
aggregate instead of using deal.dealstatus.
Filter on the aggregated data¶
The filter on the top level of the query can also reference the keys created on the relation aggregates to filter on the result of the calculated values.
Filter on the aggregated values
This query will return all persons that have more than five deals in status
agreement
query = {
'limetype': 'person',
'responseFormat': {
'object': {
'name': None,
'deal': {
'count': {
'aggregate': {
'op': 'COUNT'
},
'filter': {
'key': 'dealstatus',
'op': '=',
'exp': 'agreement'
}
},
'sum': {
'aggregate': {
'op': 'SUM',
'key': 'value'
},
'filter': {
'key': 'dealstatus',
'op': '=',
'exp': 'agreement'
}
}
}
}
},
'filter': {
'key': 'deal.count',
'op': '>',
'exp': 5
}
}
Ambiguous queries
It's possible to create ambiguous queries unless special care is taken when chosing a name for the key in the relation aggregate.
Take the following query as an example
query = {
'limetype': 'person',
'responseFormat': {
'object': {
'name': None
}
},
'filter': {
'key': 'deal.value',
'op': '>',
'exp': 1000
}
}
This query will select all persons where any of the connected deals have a value that is greater than 1000. If the query is modified to also include the sum of all the deals, it could look like this
query = {
'limetype': 'person',
'responseFormat': {
'object': {
'name': None,
'value': {
'aggregate': {
'op': 'SUM',
'key': 'value'
}
}
}
},
'filter': {
'key': 'deal.value',
'op': '>',
'exp': 1000
}
}
Note that the key for the aggregate is value, which also happens to be
a property on the deal limetype.
The query is now ambiguous since it's not clear if it will filter on persons where any of the connected deals have a value greater than 1000, or persons where the total sum of their deals is greater than 1000. For this reason, it is not allowed to give the key of a relation aggregate the same name as the name of a property on the related limetype.
Aggregating the aggregated values¶
Finally, it's also possible to create aggregates of all the aggregated values. For example if the number of connected deals for each person is returned, it's possible to get the total count of these deals.
Aggregating the aggregated values
This query will return all persons that have more than five deals in status
agreement, and will include the total number of deals all these persons
have together.
query = {
'limetype': 'person',
'responseFormat': {
'object': {
'name': None,
'deal': {
'count': {
'aggregate': {
'op': 'COUNT'
},
'filter': {
'key': 'dealstatus',
'op': '=',
'exp': 'agreement'
}
},
'sum': {
'aggregate': {
'op': 'SUM',
'key': 'value'
},
'filter': {
'key': 'dealstatus',
'op': '=',
'exp': 'agreement'
}
}
}
},
'aggregates': {
'deals': {
'totalCount': {
'op': 'SUM',
'key': 'deal.count'
}
}
},
},
'filter': {
'key': 'deal.count',
'op': '>',
'exp': 5
},
}
This query will return something like this