Split column_searchable_list into differente fields on frontend.

[iagobalmeida]

Hey there, is there any out of the box way of spliting the different searchable columns into different fields in the frontend?

If not, what class should I be extending to be able to change the way the search query is interpreted?

[aminalaee]

Hey, can you explain what you mean by splitting the searchable columns in the frontend?
You can control the query in the backend side, but not sure if that's what you are looking for.

[iagobalmeida]

What I tried to explain is something like this, instead of a single input to search for all the fields, have different fields for each field in the column_searchable_list list.

[aminalaee]

I get it now, I think it's a nice UI improvement, I will take a look into this.

[aminalaee]

You can start from here: https://github.com/aminalaee/sqladmin/blob/main/sqladmin/templates/list.html#L49
And after the UI there's a minor backend change to handle key, value instead of a single input.

[iagobalmeida]

Hey there!

So, I finally had some time to invest on this. I made a really simple solution, but it can be implemented through some kind of flag to switch between the search modes.

The first change is in the ModelView.search_query function:

def search_query(self, stmt: Select, term: List[str]) -> Select:
  fields = self.column_searchable_list
  values = term
  expressions = [
      getattr(self.model, fields[v_i]).like(text(f'"%{v}%"')) for v_i, v in enumerate(values) if v
  ]

  params = {}
  for f_i, f in enumerate(fields):
      if values[f_i]:
          params[f] = values[f_i]
  ret = stmt.filter(and_(*expressions)).params(params)

  return ret

So, instead of receiving a str as term, the function now receives a List[str], which are the values for each column_searchable_list item.
There are some other changes, just enough to make everything work.

Then, in order to receive a List[str] in the search_query function, I changed the behavior of the Admin.list function:

@login_required
async def list(self, request: Request) -> Response:
    """List route to display paginated Model instances."""

    await self._list(request)

    model_view = self._find_model_view(request.path_params["identity"])

    page = int(request.query_params.get("page", 1))
    page_size = int(request.query_params.get("pageSize", 0))
    sort_by = request.query_params.get("sortBy", None)
    sort = request.query_params.get("sort", "asc")

    search_list = [request.query_params.get(
        column, None) for column in model_view.column_searchable_list if column in model_view.model.__dict__]
    searching = False
    for s in search_list:
        if s:
            searching = True
            break 

    pagination = await model_view.list(page, page_size, search_list, sort_by, sort)
    pagination.add_pagination_urls(request.url)

    context = {
        "request": request,
        "model_view": model_view,
        "pagination": pagination,
        "searching": searching
    }

    return self.templates.TemplateResponse(model_view.list_template, context)

Basically, for each column_serachable_list item, it attempts to retrieve its value from the query_params, but only if that item is contained in the model.__dict__.

Then, in the list.html template, there a loop that iterates the column_serachable_list and creates an input for each item, contained in a form with GET method, which will be responsable for setting the query_params.
There's also a <a href="?"></a> used to clear the search that only shows up when the user is searching.

{% if model_view.column_searchable_list %}
  <form action="" method="GET" class="col row justify-content-end">
      {% for column in model_view.column_searchable_list %}
      <div class="col-md-3 text-muted">
        <div class="input-group">
          <input type="text" class="form-control" name="{{column}}" placeholder="{{ column | title }}" value="{{ request.query_params.get(column, '') }}">
        </div>
      </div>
    {% endfor %}
    <div class="btn-group col">
      <button id="search-button" class="btn " type="submit">Buscar</button>
      {% if searching %}
        <a href="?" id="search-button" class="btn" ><i class="fa-solid fa-times"></i></a>
      {% endif %}
    </div>
  </form>
{% endif %}

There's a lot to improve on the solution and pretend to keep updating this thread until I get to the final solution.

[aminalaee]

Hi,

that’s a good start. Wouldn’t it be easier if you changed the signature of search_query method?
Something like ‘def search_query(self, stmt, query: Dict[str, str])’?

[iagobalmeida]

Sure, that was the first approach I could figure out, i've applied the Dict[str,str] idea and also create a helper __extract_request_model_search_list function to make everything cleaner:

#application.Admin
def __extract_request_model_search_list(self, request: Request, model_view: ModelView):
    return dict([f for f in [(c, request.query_params.get(c, None)) for c in model_view.column_searchable_list if c in model_view.model.__dict__] if f[1] != None])

@login_required
async def list(self, request: Request) -> Response:
    """List route to display paginated Model instances."""

    await self._list(request)

    model_view = self._find_model_view(request.path_params["identity"])

    page = int(request.query_params.get("page", 1))
    page_size = int(request.query_params.get("pageSize", 0))
    sort_by = request.query_params.get("sortBy", None)
    sort = request.query_params.get("sort", "asc")

    search_list = self.__extract_request_model_search_list(
        request, model_view)
    searching = len(search_list) > 0

    pagination = await model_view.list(page, page_size, search_list, sort_by, sort)
    pagination.add_pagination_urls(request.url)

    context = {
        "request": request,
        "model_view": model_view,
        "pagination": pagination,
        "searching": searching
    }

    return self.templates.TemplateResponse(model_view.list_template, context)

That makes the ModelView.search_query a lot shorther.

def search_query(self, stmt: Select, term: Dict[str, str]) -> Select:
    expressions = [
        getattr(self.model, c).like(text(f'"%{v}%"')) for [c, v] in term.items()
    ]
    return stmt.filter(and_(*expressions)).params(term)

Now I'm currently working on typing the inputs that are generated in the list.html, not going for a complex validation but just use date input for datetime related fields and number for numberOnly fields.