Search query

Reference for building search queries used in filters and advanced search

Overview

A search query defines which records Labii should return. You can use queries in list view filters, advanced search, and widgets that support query input.

In general, a query is written as URL parameters.

Query format

A query contains three parts:

[field]__[expression]=value

# Example:
name__icontains=test

You can chain expressions with __.

# Convert date_start to date, then return records on/after 2022-02-01
date_start__asdate__gte=2022-02-01

Query expressions

Supported expressions include:

• contains: Returns results that match the provided value, case sensitive.

• not_contains: Returns results that do not match the provided value, case sensitive.

• icontains: Returns results that match the provided value, regardless of case.

• not_icontains: Returns results that do not match the provided value, regardless of case.

• in: Returns results within the provided list separated by ,.

• jcontains: Returns results that match the provided value with a JSON field.

• not_jcontains: Returns results that do not match the provided value with a JSON field.

• acontains: Returns results where a comma-separated list of values exists within a JSON array field.

• asimilarity: Returns results above a similarity threshold by comparing a JSON field with a comma-separated input string.

  • asimilarity_65: Returns results with over 65% similarity between a JSON field in the model and a comma-separated input string.

  • asimilarity_75: Returns results with over 75% similarity between a JSON field in the model and a comma-separated input string.

  • asimilarity_85: Returns results with over 85% similarity between a JSON field in the model and a comma-separated input string.

  • asimilarity_95: Returns results with over 95% similarity between a JSON field in the model and a comma-separated input string.

• gt: Returns results that are greater than the provided value.

• gte: Returns results that is greater than or equal to the provided value.

• lt: Returns results that is less than the provided value.

• lte: Returns results that is less than or equal to the provided value.

• int: Converts the database value to integer.

• asdate: Converts the database value to a date object.

• asstring: Converts the database value to a string.

• ne: Returns results that are not equal to the provided value.

For bulk ID matching in Advanced Search, use the In lookup expression with values separated by commas, for example: sid__in=id1,id2,id3.

Query by multiple fields

Use & to combine multiple conditions.

name__icontains=test&is_archived=false

JSON field filters

Examples for filtering JSON fields:

• Contains a string value: jsonfield__icontains=test

• Does not contain a string value: jsonfield__not_contains=test

• Contains a key: jsonfield__has_key=test

• Contains an object: jsonfield__jcontains={'name':'test'}

• Does not contain an object: jsonfield__not_jcontains={'name':'test'}

Filter by filter set:

• Single filter set: filter__sid=xxx

• Multiple filter sets with AND: filter__sid=xxx,xxx

• Multiple filter sets with OR: filter__sid__in=xxx,xxx

Filter by JSON query (same structure as filter set):

• jquery={a:1,b:2,c=3}

• jquery={"or": [{sid:aa,data:bb}, {sid:aa,data:bb}]}

• jquery={"and": [{sid:aa,data:bb}, {sid:aa,data:bb}]}

Database JSON fields no longer support the legacy array format:

["or", {sid:aa,data:bb}, {sid:aa,data:bb}]

Use this object format instead:

{"or": [{sid:aa,data:bb}, {sid:aa,data:bb}]}

Examples

1. All unsigned experiments:

   {"is_archived": false, "is_locked": false}

2. All unfinished experiments (at least one signer has not signed):

   {"is_archived": false, "signer__signature__isnull": true}

3. Signed but unfinished experiments (at least one witness has not signed):

   {"is_archived": false, "signer__signature__isnull": true, "is_locked": true}