Useful ATQL queries

Examples of common ATQL queries

Last documents created

SELECT * FROM Document ORDER BY creation_date DESC

Documents created today sorted by date

SELECT * FROM Document WHERE creation_date > TODAY ORDER BY creation_date DESC

Documents modified in the last 7 days

SELECT * FROM Document WHERE modified_date > NOW(-1w)

Latest documents created by my user

SELECT * FROM Document WHERE author = CURRENT_USERLatest documents created by user 'foo'

Latest documents created by user 'foo'

SELECT * FROM Document WHERE author = 'foo' ORDER BY creation_date DESC 

Documents created in the month of January 2024

SELECT * FROM Document WHERE creation_date > '2024-01-01' AND creation_date < '2024-01-31'

Documents with a specific life cycle status

SELECT * FROM my_form WHERE life_cycle_state = 'Expirado'

Replace my_form with the name of your form. The form name is not enclosed in quotes.

Replace 'Expired' with the lifecycle status you want to filter by.

Documents with a given field value

SELECT * FROM my_form WHERE metadata.my_field = 'field_value'

Replace my_form with the name of your form. The form name is not enclosed in quotes. Replace my_field with the API name of your field and field_value with the field value you want to filter.

You can read more about API v1 and ATQL at this link:

https://athento.atlassian.net/wiki/spaces/ATHENTO/pages/3224240171/API+v1+RESTful 

Security

ATQL is a proprietary language of the Athento framework that resembles SQL in its reserved words, but is NOT SQL. Many security teams make the mistake of thinking that you can perform SQL injection with ATQL, however, this is not possible because there is never a transformation from ATQL to SQL.

The ATQL language is interpreted internally in Athento and converted to Django ORM queries or ElasticSearch queries, depending on the search engine used.

You can read more about SQL injection protection in Django at the following link

https://docs.djangoproject.com/en/5.1/topics/security/#sql-injection-protection

Troubleshooting ATQL

The query does not return any documents but they are in the manager

In this case, check the following:

1. Check that the documents are indexed (if you use Elastic Search).
2. If there are date type fields in the search, check if the format is correct. Try temporarily changing these fields to text format.
3. Check that the user is active in the team and has read permissions on the space where the documents to be searched are located.

The query returns more documents than I expect

1. If you are filtering on fields that you expect to be empty, be sure to include the metadata.XXX IS NULL condition.
2. If you are filtering on fields that you expect to have some value, check that you are not sending an empty string condition. For example, if you are sending metadata.XXX = 'YYYYY', check that the variable you are sending as a condition has some value.