<a id="choosing-and-counting-objects"></a>
<a id="querying-objects"></a>
# Choosing and Counting Objects

A Draft holds sets of objects: its Forms are `draft.als_forms_set`, its Fields are `draft.als_fields_set`, and so on through the [Object Model Reference](attribute_reference.html.md#attribute-reference). You do not have to read a whole set and pick through it yourself. You can query it: `filter` it to just the objects you want, `order_by` the attribute you want, or `count` them. A Draft can hold over ten thousand Fields, so asking for less is much faster than reading them all.

The same methods work in a Document Template and in an [Import Script](../../organizations/import_scripts.html.md#organization-import-scripts), and they are written the same way in both. In a template:

```jinja
{% for field in draft.als_fields_set.filter(ControlType="CheckBox") %}
{{ field.FieldOID }}
{% endfor %}
```

and in a script:

```python
draft = importer.context.draft
checkboxes = draft.als_fields_set.filter(ControlType="CheckBox")
for field in checkboxes:
    importer.log(field.FieldOID)
```

The examples on the rest of this page are written for a script. In a template, put the same call inside `{{ }}` or `{% %}`.

<a id="the-methods-you-can-use"></a>
## The methods you can use

These are all the methods a set of objects offers. Anything else is refused, and the message lists these names.

| Method | What it gives you |
| --- | --- |
| `all()` | Every object in the set |
| `filter(...)` | The objects that match all the conditions you give |
| `exclude(...)` | The objects that do not match |
| `get(...)` | The one object that matches. It stops with an error if none match or more than one does |
| `first()` | The first object, or nothing if the set is empty |
| `last()` | The last object, or nothing if the set is empty |
| `count()` | How many objects there are, without reading them |
| `exists()` | Whether there are any objects at all |
| `order_by(...)` | The same objects in the order you name. Put `-` in front of a name to reverse it |
| `reverse()` | The same objects in the opposite order |
| `distinct()` | The set with duplicates removed. See [Removing duplicates](#querying-distinct) below |
| `values(...)` | For each object, a dictionary of the attributes you name |
| `values_list(...)` | For each object, the attributes you name. With `flat=True` and one name, just the values |
| `select_related(...)` | The same objects, with the related object you name read at the same time |
| `prefetch_related(...)` | The same objects, with the related set you name read at the same time |
| `only(...)` | The same objects, reading only the attributes you name until you ask for others |
| `defer(...)` | The same objects, leaving out the attributes you name until you ask for them |
| `none()` | An empty set |
| `union(...)` | The objects in this set or in another set of the same kind |
| `intersection(...)` | The objects in both this set and another set of the same kind |
| `difference(...)` | The objects in this set but not in another set of the same kind |

Most of these give you back another set, so you can call one after another: `draft.als_fields_set.filter(ControlType="CheckBox").order_by("FieldOID").count()`.

<a id="choosing-objects"></a>
## Choosing objects

`filter` keeps the objects that match. `exclude` keeps the ones that do not. `count` and `exists` answer without reading the objects at all:

```python
fields = importer.context.draft.als_fields_set.filter(DraftFieldActive=True)
not_text = fields.exclude(ControlType="Text")
importer.log(f"{fields.count()} active fields, {not_text.count()} of them not Text")
if not fields.filter(IsLog=True).exists():
    importer.log("No log fields")
```

Give several conditions and an object has to match all of them.

<a id="inactive-forms-and-fields"></a>
<a id="id1"></a>
### Inactive Forms and Fields

A Form or Field that is not Active is switched off in Medidata Rave. A study behaves as though it does not exist, and nothing in the study can switch it back on. Changing a Field's visibility shows or hides it, but does not make an inactive Field active.

A Draft's sets still hold its inactive Forms and Fields. Most reports leave them out, as the example above does, with `filter(DraftFormActive=True)` on a set of Forms or `filter(DraftFieldActive=True)` on a set of Fields.

<a id="conditions-other-than-equals"></a>
### Conditions other than "equals"

Add two underscores and a word to the attribute name to compare a different way. To go through a related object, name it with two underscores too: `Form__OID` is the OID of the Field's Form. These work with any attribute the Object Model Reference lists for that kind of object, not only the ones in the examples: `OID__startswith` on a set of Forms works as `FieldOID__startswith` does on a set of Fields.

```python
fields = importer.context.draft.als_fields_set
on_dm = fields.filter(Form__OID="DM")
dates = fields.filter(FieldOID__startswith="DT", ControlType="DateTime")
choices = fields.filter(ControlType__in=["RadioButton", "DropDownList"])
importer.log(f"{on_dm.count()} on DM, {dates.count()} date fields, {choices.count()} choice fields")
```

The ones you are most likely to want:

| Written as | Matches when the value |
| --- | --- |
| `FieldOID="AGE"` | is exactly `AGE` |
| `FieldOID__iexact="age"` | is `age`, ignoring capitals |
| `FieldOID__contains="DT"` | contains `DT` (`icontains` ignores capitals) |
| `FieldOID__startswith="DT"` | starts with `DT` (`endswith` for the end) |
| `ControlType__in=["Text", "LongText"]` | is one of the values in the list |
| `Ordinal__gt=5` | is greater than 5 (`gte`, `lt` and `lte` for the others) |
| `DataDictionary__isnull=True` | is not set at all, here a Field with no Data Dictionary |
| `last_updated__year=2026` | is a date in 2026 |

You can only go through related objects that the Object Model Reference lists. A name it does not list, or one that leads to an object it does not list, is refused, and the message names the part it refused.

<a id="putting-objects-in-order"></a>
## Putting objects in order

`order_by` takes the attributes to sort by, first one first. Put `-` in front for largest or latest first:

```python
for form in importer.context.draft.als_forms_set.order_by("-Ordinal"):
    importer.log(f"{form.OID} has {form.als_fields_set.count()} fields")
```

Here is a full example: the CheckBox Fields of a Draft, grouped under their Form. Asking for `select_related("Form")` reads each Field's Form along with it, rather than once per Field:

```python
draft = importer.context.draft
checkboxes = (
    draft.als_fields_set.filter(ControlType="CheckBox").select_related("Form").order_by("Form__Ordinal", "Ordinal")
)

current_form = None
for field in checkboxes:
    if field.Form.OID != current_form:
        current_form = field.Form.OID
        importer.log(f"Form {current_form}")
    importer.log(f"  {field.FieldOID}")
```

<a id="getting-one-object"></a>
## Getting one object

`first` gives you nothing, rather than an error, when the set is empty, so check for it. `get` is for an object you know is there exactly once:

```python
forms = importer.context.draft.als_forms_set
first_form = forms.order_by("Ordinal").first()
if first_form is not None:
    importer.log(f"The first form is {first_form.OID}")

demographics = forms.get(OID="DM")
importer.log(demographics.DraftFormName)
```

<a id="getting-just-the-values"></a>
## Getting just the values

`values_list` gives you the values of the attributes you name, without the objects around them. With one name and `flat=True` you get a plain list of values:

```python
fields = importer.context.draft.als_fields_set
field_oids = list(fields.values_list("FieldOID", flat=True))
control_types = fields.order_by("ControlType").values_list("ControlType", flat=True).distinct()
importer.log(f"{len(field_oids)} fields using {', '.join(control_types)}")
```

<a id="removing-duplicates"></a>
<a id="querying-distinct"></a>
### Removing duplicates

Each kind of object has an order it is listed in by default, and `distinct` only treats two objects as the same when they also agree on the attributes used for that order. So `fields.values_list("ControlType", flat=True).distinct()` can still list `CheckBox` once for every CheckBox Field. Put the values in an order of your own first, as the example above does with `order_by("ControlType")`, and each one is listed once.

<a id="what-is-not-offered"></a>
## What is not offered

A set of objects in TrialGrid is only for reading.

- There is no `update`, `delete` or `create`. A Document Template only reads, and an Import Script asks for changes with `importer.execute` so that you can see them before they are made.
- There is no `annotate` or `aggregate`. To count the Fields on each Form, ask each Form: `form.als_fields_set.count()`.
- `latest`, `earliest`, `iterator`, `in_bulk`, `contains` and `get_or_create` are not offered either. Use `order_by` with `first`, or a loop, instead.

Asking for a method that is not offered stops with a message listing the ones that are.

<a id="sorting-by-something-you-work-out"></a>
## Sorting by something you work out

`order_by` sorts by attributes the objects already have. To sort by something you work out, like how many Fields a Form has, read the objects and sort them yourself. In an Import Script you can write your own functions, and use `lambda` and list comprehensions:

```python
def describe(form):
    return f"{form.OID} ({form.als_fields_set.count()} fields)"

forms = list(importer.context.draft.als_forms_set.all())
largest_first = sorted(forms, key=lambda form: form.als_fields_set.count(), reverse=True)
importer.log(", ".join([describe(form) for form in largest_first]))
```

In a Document Template, collect what you worked out and sort it with Jinja's `sort` filter. Collect into a `namespace`: a list started with `{% set %}` outside a loop is not changed by a `{% set %}` inside it.

```jinja
{% set found = namespace(forms=[]) %}
{% for form in draft.als_forms_set.all() %}
{% set found.forms = found.forms + [(form.als_fields_set.count(), form.OID)] %}
{% endfor %}
{% for count, oid in found.forms|sort(reverse=True) %}
{{ oid }}: {{ count }} fields
{% endfor %}
```

<a id="further-reading"></a>
## Further reading

These methods come from Django, the framework TrialGrid is built on, and the [Django QuerySet reference](https://docs.djangoproject.com/en/6.0/ref/models/querysets/) explains the condition syntax in more depth. That reference describes many methods that are not offered here. The list on this page is the one that applies.