How to set `null` value in sqladmin `form_ajax_refs` form?

In sqladmin I have a model, let's name it Token, in which I have creator that relationship with the User who created that Token. In the admin panel creator by default is realized with a select html tag.

Now there are so many Users. If I want to change the User of the Token, there are so many options rows in the select tag with Users.

I convert that select HTML tag to an ajax form using form_ajax_refs in TokenAdmin.py. Now it looks like:

    form_ajax_refs = {
        "created_by": {
            "fields": ["id", "username", "account"],
            "order_by": "id"
        }
    }

It's working perfectly. I can change the Users of the Token. But the main problem is that I need to set a Null value, which means the Token does not belong to any user. I can only change the User in input but can't set a Null or empty value. How do I do that? Can anyone help?

I faced the same issue, and here’s how I resolved it.

When you use form_ajax_refs, the create_ajax_loader() function is invoked in the __init__(self) method of the ModelView class. This function returns an instance of the QueryAjaxModelLoader class for the column you want to work with via AJAX.

When a character is entered into the form, an AJAX request is generated. At the point of handling this request, the get_list(...) method in QueryAjaxModelLoader is called first, which retrieves a list of records from the database. Then, the format(...) method is called for each record in the list to generate a JSON response for the frontend.

In the SQLAdmin code, I found the following snippet:

class SelectField(fields.SelectField):
    def process_formdata(self, valuelist: List[str]) -> None:
        if valuelist:
            if valuelist[0] == "__None":
                self.data = None

This means that if you pass "__None" to backend as the chosen record, it will be interpreted as None. Based on this, I created the following solution:

Solution: Custom QueryAjaxModelLoader and ModelView

Below is the code for a patched version of QueryAjaxModelLoader that supports a special None value and properly formats it:

from typing import TYPE_CHECKING, Any, Dict, List

from sqladmin import ModelView
from sqladmin.ajax import QueryAjaxModelLoader, DEFAULT_PAGE_SIZE
from sqladmin.helpers import get_object_identifier
from sqlalchemy import String, cast, inspect, or_, select

if TYPE_CHECKING:
    from sqladmin.models import ModelView


class PatchedQueryAjaxModelLoader(QueryAjaxModelLoader):
    class __Empty:  # Special class to represent "empty" values
        pass

    def __init__(
        self,
        name: str,
        model: type,
        model_admin: "ModelView",
        **options: Any,
    ) -> None:
        super().__init__(name, model, model_admin, **options)
        self.none_value_label = options.get("none_value_label", "—")  # Default label for None
        self.allow_blank = options.get("allow_blank", False)

    def format(self, model: type) -> Dict[str, Any]:
        if not model or isinstance(model, str):
            return {}

        if isinstance(model, self.__Empty):
            return {"id": "__None", "text": self.none_value_label}  # Format None value

        return {"id": str(get_object_identifier(model)), "text": str(model)}

    async def get_list(self, term: str, limit: int = DEFAULT_PAGE_SIZE) -> List[Any]:
        stmt = select(self.model)  # type: ignore

        filters = [cast(field, String).ilike("%%%s%%" % term) for field in self._cached_fields]
        stmt = stmt.filter(or_(*filters))

        if self.order_by:
            stmt = stmt.order_by(self.order_by)

        stmt = stmt.limit(limit)
        result = await self.model_admin._run_query(stmt)  # noqa
        if self.allow_blank:
            return [self.__Empty()] + result  # Add the "empty" option to the results
        return result


def create_ajax_loader(
    *,
    model_admin: "ModelView",
    name: str,
    options: dict,
) -> QueryAjaxModelLoader:
    mapper = inspect(model_admin.model)  # type: ignore

    try:
        attr = mapper.relationships[name]
    except KeyError:
        raise ValueError(f"{model_admin.model}.{name} is not a relation.")

    remote_model = attr.mapper.class_
    return PatchedQueryAjaxModelLoader(name, remote_model, model_admin, **options)


class PatchedModelView(ModelView):
    def __init__(self) -> None:
        super().__init__()
        self._form_ajax_refs = {}
        for name, options in self.form_ajax_refs.items():
            self._form_ajax_refs[name] = create_ajax_loader(
                model_admin=self, name=name, options=options
            )

Example Usage

Here’s how you can use the patched classes in your admin view:

class TokenAdmin(PatchedModelView, model=Token):
    form_ajax_refs = {
        "created_by": {
            "fields": ["id", "username", "account"],
            "order_by": "id",
            "none_value_label": "No creator",  # Label for None
            "allow_blank": True,              # Allow the blank/None option
        }
    }
    ...

How It Works

1. Special __Empty class: A custom __Empty class is used to represent the "None" value in the dropdown.
2. Custom Loader: The get_list method adds the __Empty value to the result list when allow_blank is enabled.
3. Custom Formatting: The format method is overridden to ensure __None is passed as the id for the "None" value.

With this implementation, the "None" option will always be the first result in the search dropdown, making it easy for the user to select it if needed.