Database#

Advanced querying#

The basics on using the QueryBuilder to find the data you are interested in is explained in the finding and querying how-to. This section explains some more advanced methods for querying your database and the QueryBuilder dictionary.

Working with edges#

Filters and projections can be applied to both the vertices of the query path and the edges that connect them. Applying a filter or projection to an edge works the same way as for vertices, but the relevant keyword is now preceded by edge_. Using the ArithmeticAddCalculation calculation job as an example, let’s say we want to query for the first input of the addition, i.e. the Int nodes which have been provided as the input with label x:

from aiida.orm import QueryBuilder
qb = QueryBuilder()
qb.append(CalcJobNode, tag='calcjob')
qb.append(Int, with_outgoing='calcjob', edge_filters={'label': 'x'})

By using the edge_filters keyword argument, we can query for only the inputs that have the label x. Note that any operator that can be used to filter vertices can also be applied to edges. Say we want to find all input Int nodes that are not connected to the CalcJobNode’s via an edge with label x:

qb = QueryBuilder()
qb.append(CalcJobNode, tag='calcjob')
qb.append(Int, with_outgoing='calcjob', edge_filters={'label': {'!==': 'x'}})

Here, the equality operator == is negated by prepending an exclamation mark !. See the reference table below for a table with all operators. Similar to filters, we can project information of the edge using the edge_project keyword argument:

qb = QueryBuilder()
qb.append(CalcJobNode, tag='calcjob')
qb.append(Int, with_outgoing='calcjob', edge_project='label')

In the example above, we are querying for the edge labels of the incoming Int nodes of all CalcJobNode’s.

Ordering and limiting results#

You can order the results of your query by the properties of the entity. Say you want to return the list of Int outputs from all CalcJobNode’s, sorted by the time they were created in descending order, i.e. the most recent first:

qb = QueryBuilder()
qb.append(CalcJobNode, tag='calcjob')
qb.append(Int, with_incoming='calcjob')
qb.order_by({Int: {'ctime': 'desc'}})

This can also be used to order your results based on values in a (nested) dictionary, such as the attributes column. However, as the QueryBuilder cannot infer the type of the value in this case, you have to cast the type:

qb = QueryBuilder()
qb.append(CalcJobNode, tag='calcjob')
qb.append(Int, with_incoming='calcjob')
qb.order_by({Int: {'attributes.value': {'order': 'asc', 'cast': 'i'}}})

The query above will return all Int nodes that are output of all CalcJobNode’s, in ascending order of their value, i.e. from small to big. Note that in this case you have to specify the order operation with a dictionary, where the order key details how you want to order the query results and the cast key informs the QueryBuilder of the attribute type. A list of the available cast types and their aliases can be found in the table below:

Python type	Alias	SQLAlchemy type
int	i	Integer
float	f	Float
bool	b	Boolean
str	t	String
dict	j	JSONB
datetime.datetime	d	DateTime

You can also order using multiple properties by providing a list of dictionaries that each specify one sorting operation:

qb = QueryBuilder()
qb.append(CalcJobNode, tag='calcjob')
qb.append(Int, with_incoming='calcjob')
qb.order_by({Int: [{'attributes.value': {'order': 'asc', 'cast': 'f'}}, {'ctime': 'desc'}]})

Here the Int nodes will first be sorted by their value in ascending order. Nodes for which the value is equal are subsequently sorted by their modification time in descending order.

Finally, you can also limit the number of query results returned with the limit() method. Suppose you only want the first three results from our query:

qb = QueryBuilder()
qb.append(CalcJobNode)
qb.limit(3)

This can be easily combined with the order_by method in order to get the last three CalcJobNode’s that were created in the database:

qb = QueryBuilder()
qb.append(CalcJobNode)
qb.limit(3)
qb.order_by({CalcJobNode: {'ctime': 'desc'}})

Reference tables#

List of all operators:

Operator	Datatype	Example	Explanation
`==`	all	`'id': {'==': 123}`	Filter for equality
`in`	all	`'name': {'in': ['foo', 'bar']}`	Filter for values that are in the given list.
`>,<,<=,>=`	float, integer, date	`'ctime': {'<': datetime(2016, 03, 03)}`	Filter for values that are greater or smaller than a certain value
`like`	string	`'name': {'like': 'label%'}`	Filter for matching substrings where `%` and `_` are wildcards. To match a literal `%` or `_` escape it by prefixing it with `\\`.
`ilike`	string	`'name': {'ilike': 'lAbEl%'}`	Case insensitive version of `like`.
`or`	list of expressions	`'id': {'or': [{'<': 12}, {'==': 199}]}`	A list of expressions where at least one should be matched.
`and`	list of expressions	`'id': {'and': [{'<': 12}, {'>': 1}]}`	A list of expressions where all should be matched.
`has_key`	dict	`'attributes': {'has_key': 'some_key'}`	Filter for dictionaries that contain a certain key.
`of_type`	any	`'attributes.some_key': {'of_type': 'bool'}`	Filter for values of a certain type.
`of_length`	lists	`'attributes.some_list': {'of_length': 4}`	Filter for lists of a certain length.
`shorter`	lists	`'attributes.some_list': {'shorter': 4}`	Filter for lists that are shorter than a certain length.
`longer`	lists	`'attributes.some_list': {'longer': 4}`	Filter for lists that are longer than a certain length.
`contains`	lists	`'attributes.some_key': {'contains': ['a', 'b']}`	Filter for lists that should contain certain values.

New in version 2.6: Logical expression syntax

In the new logical expression syntax for filters and projections, the above operations are distributed by type. When using the autocompletion feature in constructing query expressions, only operations associated with the type of the queried field will be presented.

For example, Node.fields.uuid is a string type, and as such, Node.fields.uuid. will, in addition to == and in_, only suggest like and ilike.

As mentioned in the section about operator negations all operators can be turned into their associated negation (NOT operator) by adding a ! in front of the operator.

New in version 2.6: Programmatic filter negation

In the new logical expression syntax, negation is defined with ~, such that ~(Node.fields.some_field < 1) is equivalent to Node.fields.some_field >= 1.

Note

The form of (negation) operators in the rendered SQL may differ from the ones specified in the QueryBuilder instance. For example, the !== operator of the QueryBuilder will be rendered to != in SQL.

List of all relationships:

Entity from	Entity to	Relationship	Explanation
Node	Node	with_outgoing	One node as input of another node
Node	Node	with_incoming	One node as output of another node
Node	Node	with_descendants	One node as the ancestor of another node (Path)
Node	Node	with_ancestors	One node as descendant of another node (Path)
Node	Group	with_node	The group of a node
Group	Node	with_group	The node is a member of a group
Node	Computer	with_node	The computer of a node
Computer	Node	with_computer	The node of a computer
Node	User	with_node	The creator of a node is a user
User	Node	with_user	The node was created by a user
User	Group	with_user	The node was created by a user
Group	User	with_group	The node was created by a user
Node	Log	with_node	The log of a node
Log Node	Node Comment	with_log with_node	The node has a log The comment of a node
Comment	Node	with_comment	The node has a comment
User	Comment	with_user	The comment was created by a user
Comment	User	with_comment	The creator of a comment is a user

Converting the QueryBuilder to/from a dictionary#

Important

In aiida-core version 1, this dictionary was accessed with QueryBuilder.queryhelp, which is now deprecated.

The QueryBuilder class can be converted to a dictionary and also loaded from a dictionary, for easy serialisation and storage. Once you have built your query using the appender method explained in the finding and querying for data how-to and the advanced sections above, you can easily store your query by saving the QueryBuilder.as_dict() dictionary as a JSON file for later use:

import json
from aiida.orm import QueryBuilder

qb = QueryBuilder()
qb.append(CalcJobNode)

with open("querydict.json", "w") as file:
    file.write(json.dumps(qb.as_dict(), indent=4))

To use this dictionary to instantiate the QueryBuilder, you can use the from_dict class method:

with open("querydict.json", "r") as file:
    query_dict = json.load(file)

qb = QueryBuilder.from_dict(query_dict)

Alternatively, you can also use a dictionary to set up your query by specifying the path, filters and projections and constructing the dictionary by hand. To do this, you have to specify:

the path: Here, the user specifies the path along which to join tables as a list of dictionaries, where each list item identifies a vertex in your path. You define the vertex class with the cls key:
```
query_dict = {
    'path':[
        {'cls': Data}
    ]
}
```
Each entity in the query has to have a unique tag. If the tag is not provided, it is set to the name of the class. However, this will not work if you choose the same class twice in the query. In this case you have to provide the tag using the tag key:
```
query_dict = {
    'path':[
        {
            'cls':Node,
            'tag':'node_1'
        },
        {
            'cls':Node,
            'tag':'node_2'
        }
    ]
}
```
You also have to detail some information on the vertex edges, in order to connect them correctly. There are several redundant ways this can be done:
- You can specify that this node is an input or output of another node preceding the current one in the list. That other node can be specified by an integer or the class or type. The following examples are all valid joining instructions, assuming there is a structure defined at index 2 of the path with tag “struc1”:
```
edge_specification = query_dict['path'][3]
edge_specification['with_incoming'] = 2
edge_specification['with_incoming'] = StructureData
edge_specification['with_incoming'] = 'struc1'
edge_specification['with_outgoing']  = 2
edge_specification['with_outgoing']  = StructureData
edge_specification['with_outgoing']  = 'struc1'
```
- query_dict['path'][<i>]['direction'] = integer
  
  If any of the above specs (“with_outgoing”, “with_incoming”) were not specified, the key “direction” is looked for. Directions are defined as distances in the tree. 1 is defined as one step down the tree along a link. This means that 1 joins the node specified in this dictionary to the node specified on list-item before as an output. Direction defaults to 1, which is why, if nothing is specified, this node is joined to the previous one as an output by default. A negative number reverse the direction of the link. The absolute value of the direction defines the table to join to with respect to your own position in the list. An absolute value of 1 joins one table above, a value of 2 to the table defined 2 indices above. The two following dictionaries yield the same query:
```
from aiida.orm import TrajectoryData
from aiida_quantumespresso.calculations.pw import PwCalculation
from aiida.orm import Dict
query_dict_1 = {
    'path': [
        {
            'cls':PwCalculation
        },
        {
            'cls':TrajectoryData
        },
        {
            'cls':Dict,
            'direction':-2
        }
    ]
}

# returns same query as:

query_dict_2 = {
    'path':[
        {
            'cls':PwCalculation
        },
        {
            'cls':TrajectoryData
        },
        {
            'cls':Dict,
            'with_outgoing':PwCalculation
        }
    ]
}

# Shorter version:

query_dict_3 = {
    'path':[
        Dict,
        PwCalculation,
        TrajectoryData,
    ]
}
```
what to project: Determining which columns the query will return:
```
query_dict = {
    'path':[PwCalculation],
    'project':{
        PwCalculation:['user_id', 'id'],
    }
}
```
If you are using JSONB columns, you can also project a value stored inside the json:
```
query_dict = {
    'path':[
        PwCalculation,
        StructureData,
    ],
    'project':{
        PwCalculation:['state', 'id'],
    }
}
```
Returns the state and the id of all instances of PwCalculation where a structures is linked as output of a relax-calculation. The strings that you pass have to be name of the columns. If you pass an asterisk (‘*’), the query will return the instance of the AiidaClass.

the filters: Filters enable you to further specify the query. This is an example for a query for structures that were added after a certain time (say last 4 days) and have an id larger than 50:

from aiida.common import timezone
from datetime import timedelta

query_dict = {
    'path':[
        {'cls':PwCalculation}, # PwCalculation with structure as output
        {'cls':StructureData}
    ],
    'filters':{
        StructureData:{
            'ctime':{'>':  timezone.now() - timedelta(days=4)},
            'id':{'>': 50}
        }
    }
}

If you want to include filters and projections on links between nodes, you will have to add these to filters and projections in the query dictionary. Let’s take an example from before and add a few filters on the link:

query_dict = {
    'path':[
        {'cls':PwCalculation, 'tag':'relax'}, # PwCalculation with structure as output
        {'cls':StructureData, 'tag':'structure'}
    ],
    'filters':{
        'structure':{
            'id':{'>': 50}
        },
        'relax--structure':{
            'label':{'like':'output_%'},
        }
    },
    'project':{
        'relax--structure':['label'],
        'structure':['label'],
        'relax':['label', 'uuid'],
    }
}

Notice that the tag for the link, by default, is the tag of the two connecting nodes delimited by two dashes ‘–’ and the order DOES matter.

Alternatively, you can choose the tag for the edge in the path when defining the entity to join using edge_tag:

query_dict = {
    'path':[
        {'cls':PwCalculation, 'tag':'relax'},         # Relaxation with structure as output
        {
            'cls':StructureData,
            'tag':'structure',
            'edge_tag':'ThisIsMyLinkTag'     # Definining the link tag
        }
    ],
    'filters':{
        'structure':{
            'id':{'>': 50}
        },
        'ThisIsMyLinkTag':{                  # Using this link tag
            'label':{'like':'output_%'},
        }
    },
    'project':{
        'ThisIsMyLinkTag':['label'],
        'structure':['label'],
        'relax':['label', 'uuid'],
    }
}

Limits and offset can be set directly like this:

query_dict = {
    'path':[Node],
    'limit':10,
    'offset':20
}

That query_dict would tell the QueryBuilder to return 10 rows after the first 20 have been skipped.