TALL Stack: Please Sir, I want some more

Date Created: 09-Apr-2022
(Last Updated: 03-Jul-2022)

Oliver Twist asking for more

Fondo Antiguo de la Biblioteca de la Universidad de Sevilla from Sevilla, España, CC BY 2.0, via Wikimedia Commons

The Problem

I have multiple places where I wanted to provide "load more" functionality:

  • The index page for observations
  • The index page for taxa
  • The search results page for taxa
  • Observations on taxon pages
  • Observations that are not identified to species level in checklists
  • The photo picker I use for selecting photos for taxa and checklists

I was very keen to have a single, generic component for loading more items. I was also very keen to avoid reloading the whole set of data every time the [Load More] button is clicked. I also wanted to ensure the [Load More] button was displayed only when necessary.

I also display different types of output for the different places. For observations, it is a collection of observation-tile components; for taxa, a collection of taxon-tile components; and for photos, a collection of taxon-thumbnail components.

In addition, the WHERE conditions and sorting may be different in each case. This is even dynamic for the observations and taxa index pages, where there is a filter, so the WHERE conditions can be changed by the user, and for observations only, the sort can be changed, too. For example, in a checklist it simply presents observations that are identified directly against the given taxon. However, the taxon filter on observations index page is for selecting the root taxon, and all observations identified as that taxon or any of its descendant taxa are presented. The most complex scenario is for the photo picker which includes photos from descendant taxa and direct observations in the same list.

This would be significantly easier if it was possible to pass an Illuminate\Database\Eloquent\Builder into a Livewire component. But it isn't and for good reasons.

The Basic Load More Functionality

The basics of my load more functionality are based on this answer by shortbrownman to a question on the Livewire forum.

Basically, it uses two Livewire components LoadMore and LoadMoreButton to handle everything. It also uses an Eloquent Paginator so it knows when there are more pages and uses this to decide whether or not to display the LoadMoreButton component after rendering the list of whatever is returned from the LoadMore component. The LoadMoreButton component has a Boolean flag loadMore which, when false, causes it to render its own view (the button) or, when true renders a new LoadMore component, which may or may not include a LoadMoreButton component, depending on whether or not there are still more items to load. I won't include that code here because I will be showing my actual implementation below, but all the basic codes is at the link above. If you have a single need for load more functionality, that solution will suffice. Even with multiple needs, you can create multiple components, one for each scanrio. This article is about making a generic load more component.

Making it Generic

It seemed to me that the next best option to passing an Illuminate\Database\Eloquent\Builder into a Livewire component is to pass the SQL that the builder would have generated. The basic principle is to pass the SQL query as a string with everything except the LIMIT and OFFSET, which are used for pagination, add those in the render() method of the Livewire component, and convert it to an Illuminate\Pagination\Paginator to pass to the view. Simple!

The LoadMore component

class LoadMore extends Component
{
    public string $modelName;
    public string $modelComponent;
    public array $sortColumns;
    public array $sortOrder;
    public string $sqlStatement;
    public int $perPage;
    public int $page;
    public array $bindings;
    public int $itemKey;

    protected $listeners = ['filterChanged'];

    public function mount (
        string $modelName,
        string $modelComponent,
        int $perPage,
        array $sortColumns = ['created_at', 'id'],
        array $sortOrder = ['DESC', 'ASC'],
        string $sqlStatement = '',
        array $bindings = [0],
        int $page = 1,
        int $itemKey = 0
    ) {
        /* If no SQL statement is passed, make a default statement based on passed parameters */
        if (empty($sqlStatement)) {
            $modelClass = 'App\\Models\\' . $modelName;
            $query = $modelClass::where('id', '>', 0);
            foreach ($sortColumns as $idx => $sortColumn) {
                $query->orderBy($sortColumn, $sortOrder[$idx]);
            }
            $sqlStatement = $query->toSql();
        }
        $this->modelName = $modelName;
        $this->modelComponent = $modelComponent;
        $this->perPage = empty( $perPage ) ? config('app.observations_per_section') : $perPage;
        $this->sort = $sortColumns;
        $this->sortOrder = $sortOrder;
        $this->sqlStatement = $sqlStatement;
        $this->bindings = $bindings;
        $this->page = empty( $page ) ? 1 : $page;
        $this->itemKey = $itemKey;
    }

    public function render()
    {
        /* Add limit and offset to the query based on the current page and items per page */
        $limit  = $this->perPage + 1;
        $offset = ($this->page - 1) * $this->perPage;
        $sqlStatement = "{$this->sqlStatement} limit {$limit} offset {$offset}";

        /* Run the query using the Eloquent Builder, if a modelName is provided, else the DB facade */
        if (empty($modelClass)) {
            $rows = DB::select($sqlStatement, $this->bindings);
        } else {
            $modelClass = 'App\\Models\\' . $this->modelName;
            $rows = $modelClass::fromQuery($sqlStatement, $this->bindings);
        }
        $paginated_rows = new Paginator($rows, $this->perPage, $this->page);

        return view('livewire.load-more', [
            'rows' => $paginated_rows,
        ]);
    }

    public function filterChanged(string $sqlStatement, array $bindings)
    {
        $this->sqlStatement = $sqlStatement;
        $this->bindings = $bindings;
        $this->page = 1;
    }
}

There are quite a few properties but it doesn't actually do very much. A key point to note is the modelName property, which is the unqualified name of the appropriate Eloquent model. This is used to ensure I return a proper collection of models, rather than stdClass objects, which would be returned if I simply used the DB facade. However, it also supports queries that are not model based, by passing an empty string for the modelName property. I use this for my TaxonPhotoPicker component, which does not need hydrated models.

The mount() method creates a base query in the case that none is passed. On my site, this happens from the index views for both Observations and Taxa because the default is to show unfiltered results. Note that I am still using the Builder to create the query and then using its toSql() method to get the generated SQL as a string. The sortColumns and sortOrder properties are used only in the generation of the base query. Thereafter, calling code passes in SQL statements that already include the necessary ORDER BY clause.

In the render() method, I work out what OFFSET and LIMIT to add and append them to the SQL. Once the SQL statement is complete, I use the either Builder::fromQuery() via the Model class to get a collections of models or DB::select() to get a collection of stdClass objects. The collection is then passed into the constructor of Illuminate\Pagination\Paginator and the resulting object passed to the view.

It is also worth pointing out the itemKey parameter. This is used to enable unique values for wire:key. As things stand, I only need to use this on checklists because they can have multiple LoadMore components on the same page.

The component is 'listening' for the filterChanged event, which is emitted by my Livewire filter components (more on those below) and passes the modified SQL statement and its bindings.

The Blade for the LoadMore component is like this:

<div class="inline">
    @if ( count($rows) === 0 )
        <div class="border border-red-800 rounded-lg p-3 bg-red-200 text-center">
            {{ __("No records found") }}
        </div>
    @else
        @foreach ( $rows as $row )
            <x-dynamic-component :component="$modelComponent" :model=$row />
        @endforeach

        @if ( $rows->hasMorePages() )
            <livewire:load-more-button
                :model-name="$modelName"
                :model-component="$modelComponent"
                :per-page="$perPage"
                :page="$page"
                :sql-statement="$sqlStatement"
                :bindings="$bindings"
                :item-key="$itemKey"
                wire:key="{{ $modelName }}-l-m-b-{{ $itemKey }}-{{ $page }}"
            />
        @endif
    @endif
</div>

It very simply loops through the items and renders them using the modelComponent property of the LoadMore component via Laravel Blade's built-in dynamic component. Then, if the paginator has more rows, it renders a LoadMoreButton component and passes it the necessary parameters for loading the next set of items. Note the use of class="inline". This is because I am using this to render "tiles" or photos, left-to-right, top-to-bottom rather than just top-to-bottom. It is possible that the previously loaded items did not end at the end of a "row", and I want my newly added items to continue directly after the last item, rather than starting a new row.

You can also see the way the wire:key is generated to ensure it is unique. It is always very important to ensure your Livewire components have a unique wire:key when more that one instance of the same component can appear on the same page. If you ever get errors relating to trying to access the fingerprint property on null, the cause is likely to be non-unique keys on your page.

The LoadMoreButton component

The LoadMoreButton component contains the same code as the LoadMore component but with some extra logic in the render() method.

public function render()
{
    if ($this->loadMore) {
        $limit  = $this->perPage + 1;
        $offset = ($this->page - 1) * $this->perPage;
        $sqlStatement = "{$this->sqlStatement} limit {$limit} offset {$offset}";

        /* Run the query using the Eloquent Builder, if a modelName is provided, else the DB facade */
        if (empty($this->modelName)) {
            $rows = DB::select($sqlStatement, $this->bindings);
        } else {
            $modelClass = 'App\\Models\\' . $this->modelName;
            $rows = $modelClass::fromQuery($sqlStatement, $this->bindings);
        }
        $paginated_rows = new Paginator($rows, $this->perPage, $this->page);

        return view('livewire.load-more', [
            'rows' => $paginated_rows,
        ]);
    } else {
        return view('livewire.load-more-button');
    }
}

The main difference is that it decides whether to render its own view or the view of LoadMore component. This is done using the loadMore bool property, which defaults to false, cause the button to be rendered. The loadMore() method is very simple but crucial to this operation.

public function loadMore()
{
    $this->loadMore = true;
}

The view is like this.

<div class="inline">
    <x-button type="button" wire:click="loadMore" wire:loading.attr="disabled" class="mb-3">Load More</x-button>
    <div wire:loading wire:target="loadMore" class="inline ml-3">
        Loading...
    </div>
</div>

So, when the button is clicked, the loadMore() method of the component is called, which sets the loadMore property to true causing the component to re-render but this time redering the view of LoadMore component with the next set of items. If there are still more items available, a new LoadMoreButton component will be rendered, too, and so on. Note the use of wire:loading, which is a great little feature of Livewire. It very easily allows us to display something (in this case, just the text "Loading...") while and operation (determined by wire:target) is in progress, automatically disappearing when the operation is complete.

The index page for observations

The index() method of the ObservationController class is like this.

public function index()
{
    return view('observations.index', ['perPage' => config('app.observations_per_page')]);
}

It simply passes the value of the perPage parameter to the view.

The view is like this.

<x-app-layout title="Observations">
    <x-slot name="header">
        <livewire:filter-observations />
    </x-slot>

    <div class="max-w-7xl mx-auto py-6 px-4 sm:px-6 lg:px-8">
        <livewire:load-more
            model-name="Observation"
            model-component="observation-tile"
            :sort-columns="['observed_at', 'id']"
            :per-page="$perPage"
            wire:key="o-l-m-1"
        />
        <x-up-to-top />
    </div>
</x-app-layout>

The view passes the perPage, along with modelName, modelComponent and sortColumns on to the LoadMore component. It relies on the default query that the LoadMore component will generate.

The query only gets modified if the filter is used. The FilterObservations component does the work of generating the appropriate SQL statement based on the selected filter options.

class FilterObservations extends Component
{
    public string $dateFrom;
    public string $dateTo;
    public ?int $rootTaxon;
    public string $sortBy;
    public string $sortOrder;

    const SORT_OPTIONS = [
        'Time Observed' => 'observed_at',
        'Time Recorded' => 'added_to_inat_at',
        'Time Updated' => 'updated_in_inat_at'
    ];
    const SORT_ORDERS = ['Ascending' => 'ASC', 'Descending' => 'DESC'];
    public array $defaultSort = ['by' => 'Time Observed', 'order' => 'Descending'];

    public function mount()
    {
        $this->dateFrom = '';
        $this->dateTo = '';
        $this->rootTaxon = 0;
        $this->sortBy = $this->defaultSort['by'];
        $this->sortOrder = $this->defaultSort['order'];
    }

    public function render()
    {
        $query = Observation::where('id', '>', 0);
        if (!empty($this->rootTaxon)) {
            $rootTaxon = Taxon::find($this->rootTaxon);
            $rootTaxonDescendantIds = array_values($rootTaxon->descendants->pluck('id')->toArray());
            $rootTaxonDescendantIds[] = $rootTaxon->id;
            $query->whereIn('taxon_id', $rootTaxonDescendantIds);
        }
        if ( ! empty($this->dateFrom) ) {
            $timeFrom = $this->dateFrom . ' 00:00:00';
        }
        if ( ! empty($this->dateTo) ) {
            $timeTo = $this->dateTo . ' 23:59:59';
        }
        if ( !empty($timeTo) && !empty($timeFrom) ) {
            $query->whereBetween('observed_at', [ $timeFrom, $timeTo ]);
        } elseif ( !empty($timeFrom) ) {
            $query = $query->where('observed_at', '>=', $timeFrom );
        } elseif ( !empty($timeTo) ) {
            $query->where('observed_at', '<=', $timeTo );
        }
        $sortBy = empty($this->sortBy) ? 'observed_at' : self::SORT_OPTIONS[$this->sortBy];
        $sortOrder = empty($this->sortOrder) ? 'DESC' : self::SORT_ORDERS[$this->sortOrder];
        $query->orderBy($sortBy, $sortOrder)->orderBy('id', $sortOrder);

        $this->emit('filterChanged', $query->toSql(), $query->getBindings());

        return view('livewire.filter-observations');
    }
}

Basically, all this is doing is dynamically building a query based on the filters. The query is built in the render() method. It has only two types of filter: one based on the observation date; and the other on the root taxon. It also has the option to sort by one of the three dates (when the observation took place, when it was added to iNaturalist, and when it was last updated in iNaturalist) and to choose between ascending or descending order. One of the great things about the Builder is that you can gradually and conditionally build up your query as long as you are calling methods that return a Builder (i.e. until you call any method to get the results).

It starts by adding a dummy WHERE clause. This is simply to give us a Builder object, to which we can add the rest of our query. The date filter processing is a little more complex than I need because my date picker always returns a range, so the >= and <= to options are redundant. But, if I ever change the way I allow dates to be picked, this will already cope with it. So, basically, all it does is use Builder->whereBetween() to add a BETWEEN WHERE clause based on the selected date range, if a date range has been selected. The taxon filter gets an array of the ids of the descendant taxa of the root taxon (picked in the filter) and uses the Builder->whereIn() to add a IN WHERE clause.

The descendants are retrieved using a method on the HierarchicalModel class, which is inherited by the Taxon model.

public function getDescendantsAttribute()
{
    return static::where('set_start', '>', $this->set_start)
        ->where('set_end', '<', $this->set_end)
        ->orderBy('set_start', 'asc')
        ->get();
}

Note that this the Nested Sets Model, which is explained in another article.

The new SQL statement and its bindings are emited via the filterChanged, they will be picked up by the any LoadMore and LoadMoreButton components, and the set of observation tiles will be changed accordingly.

The index page for taxa

For my Taxa index page, the pattern is much the same, with the controller setting the value for $perPage. The index view is extremely simply.

<x-app-layout title="Taxa">
    <x-slot name="header">
        <livewire:filter-taxa />
    </x-slot>

    <div class="max-w-7xl mx-auto py-6 px-4 sm:px-6 lg:px-8">
        <livewire:load-more
            model-name="Taxon"
            model-component="taxon-tile"
            :per-page="$perPage"
            wire:key="t-l-m-1"
        />
        <x-up-to-top />
    </div>
</x-app-layout>

It relies on the default SQL statement and sort, so there is very little to pass to the LoadMore filter. The FilterTaxa component is very similar to the FilterObservations component, just with simpler query generation because it has a simpler filter (only root taxon, and no sort option).

The search results page for taxa

For the taxa search, the same principle is applied but, again, just a different query. This is the search() method from the controller.

public function search(Request $request)
{
    $request->validate([
        'term' => 'required',
    ]);

    $term = $request->input('term');

    $query = Taxon::where(function($query) use ($term) {
        $query->where('scientific_name', 'like', '%' . $term . '%')
            ->orWhere('common_name', 'like', '%' . $term . '%');
    })->orderBy('created_at', 'DESC')->orderBy('id', 'DESC');

    $view_parameters = [
        'term' => $term,
        'sqlStatement' => $query->toSql(),
        'bindings' => $query->getBindings(),
        'perPage' => config('app.taxa_per_page'),
    ];

    return view('taxa.search', $view_parameters);
}

Here we just match any taxa where either the scientific or common name contains the search term.

The view is like this.

<x-app-layout title="Taxa Search Results for '{{ $term }}'">
    <x-slot name="header">
        <h1 class="font-semibold text-xl text-gray-800 leading-tight">{{ __("Taxa Search Results for") }} '{{ $term }}'<h1>
    </x-slot>

    <div class="max-w-7xl mx-auto py-6 px-4 sm:px-6 lg:px-8">
        <livewire:load-more
            model-name="Taxon"
            model-component="taxon-tile"
            :per-page="$perPage"
            :sql-statement="$sqlStatement"
            :bindings="$bindings"
            wire:key="t-l-m-1"
        />
        <x-up-to-top />
    </div>
</x-app-layout>

Observations on taxon pages

Each taxon page contains a list of observations identified as that taxon or any of it's descendants. The relevant code from the show() method of the controller is like this:

public function show(Taxon $taxon)
{
    $rootTaxonDescendantIds = array_values($taxon->descendants->pluck('id')->toArray());
    $rootTaxonDescendantIds[] = $taxon->id;
    $query = Observation::whereIn('taxon_id', $rootTaxonDescendantIds)
        ->orderBy('observed_at', 'DESC')
        ->orderBy('id', 'DESC');

    return view(
        'taxa.show',
        compact('taxon'))
            ->with('sqlStatement', $query->toSql())
            ->with('bindings', $query->getBindings())
            ->with('perPage', config('app.observations_per_section')
    );
}

The relevant part of the view is like this.

<x-card collapsible=true >
    <x-slot name="title">
        <h2>Observations</h2>
    </x-slot>
    <livewire:load-more
        model-name="Observation"
        model-component="observation-tile"
        :per-page="$perPage"
        :sql-statement="$sqlStatement"
        :bindings="$bindings"
        wire:key="o-l-m-1"
    />
</x-card>

Observations in checklists

Generally, each checklist is a list of species or subspecies level taxa organised taxanomically. In a perfect world that would suffice. However, in some taxonomic groups identifying to species level is very difficult or even impossible from photos alone. For observations that are identified only to genus level or above, I show the list of observations against the taxon to which they are identified. I use the LoadMore component just in case there are a lot, which can be the case for some taxa. For this, each taxon requires its own SQL query. Fortunately, the way SQL statements are generated and bound later means that all of these SQL statements are of the same form, with a '?' as a placeholder for the taxon ID.

SELECT * FROM observations WHERE taxon_id = ? ORDER BY observed_at DESC, id DESC;

This means that I can generate the SQL statement in the controller, and simply provide the binding of the taxon ID in the view. Here's the show() method.

public function show(Checklist $checklist)
{
    $pathPrefix = Taxon::find($checklist->taxon_id)->path . '.';
    $query = Observation::where('taxon_id', 0)->orderBy('observed_at', 'DESC')->orderBy('id', 'DESC');
    $perPage = config('app.observations_per_section');
    return view('checklists.show', compact('checklist'))
        ->with('pathPrefix', $pathPrefix)
        ->with('sqlStatement', $query->toSql())
        ->with('perPage', $perPage);
}

The relevant parts of the view are like this.

@foreach ( $checklist->taxa as $taxon )
    @php
        $observationCount = count($taxon->observations);
    @endphp
    @if ( $observationCount > 0 && $taxon->rank_level > 10 )
        <x-card collapsible=true>
            <x-slot name="title">
                <h2>Unknown {{ $taxon->scientific_name }}</h2>
            </x-slot>
            <livewire:load-more
                model-name="Observation"
                model-component="observation-tile"
                :perPage="$perPage"
                :sql-statement="$sqlStatement"
                :bindings="[$taxon->id]"
                :item-key="$taxon->id"
                wire:key="o-l-m-{{ $taxon->id }}-1"
            />
        </x-card>
    @endif
@endforeach

Because the SQL statement is fixed, all it does in the loop is pass on the parameters from the controller and bind the current $taxon->id. Note the :item-key, which I mentioned earlier is to ensure that the LoadMoreButton component (if one is required) can have a unique wire:key.

The taxon photo picker

The only other place I use the LoadMore component is for picking the photo for a taxon or checklist. For this, I have a TaxonPhotoPicker Livewire component. It needs a slightly more complex query because it includes photos from the direct child taxon, plus photos from all observations directly identified as the current taxon. The render() method is like this.

public function render()
{
    $rootTaxon = Taxon::find($this->rootTaxon);
    $includedTaxa = $this->getIncludedTaxa($rootTaxon);
    $directQuery = Taxon::join('observations', 'observations.taxon_id', '=', 'taxa.id')
        ->join('observation_photos', 'observation_photos.observation_id', '=', 'observations.id')
        ->select('taxa.scientific_name', 'observations.id', 'observations.observed_at', 'observation_photos.url')
        ->where('taxa.id', $this->rootTaxon)
        ->where('observation_photos.sequence', 0);
    $query = Taxon::join('observations', 'observations.id', '=', 'taxa.image_observation_id')
        ->join('observation_photos', 'observation_photos.observation_id', '=', 'observations.id')
        ->select('taxa.scientific_name', 'observations.id', 'observations.observed_at', 'observation_photos.url')
        ->where('observation_photos.sequence', 0);
    if ( count($includedTaxa) > 0 ) {
        $query->whereIn('taxa.id', $includedTaxa);
    } else {
        /* If there are no taxa to include, add a WHERE clause to prevent all taxa from being selected. */
        $query->where('taxa.id', 0);
    }
    $query->union($directQuery)->orderBy('observed_at', 'DESC')->orderBy('id', 'DESC');

    $view_parameters = [
        'sqlStatement' => $query->toSql(),
        'bindings' => $query->getBindings(),
        'perPage' => config('app.photos_per_page'),
    ];

    return view('livewire.taxon-photo-picker', $view_parameters);
}

The $directQuery gets the photos from directly related observations. The other query gets the photos from the children of the current taxa and uses union() to combine the two resultsets before sorting. The getIncludedTaxa() method gets the list of taxa to include. For checklists, this can involve removing excluded taxa from the list. It's code is not really relevant to this article.

The view is very simple.

<div>
    <div class="bg-white mt-1 rounded-lg shadow-lg p-4 w-full md:container mx-auto" style="font-size: 0;">
        <livewire:load-more
            model-name=""
            model-component="taxon-thumbnail"
            :per-page="$perPage"
            :sql-statement="$sqlStatement"
            :bindings="$bindings"
            wire:key="t-p-l-m-1"
        />
    </div>
</div>

Note the empty string passed to the modelName parameter of the LoadMore component. This causes the component to use the Illuminate\Support\Facades\DB instead of the Illuminate\Database\Eloquent\Builder because we don't need hydrated models from this query.

Wrapping Up

With only a little extra work, it is possible to work around the problem of not being able to pass Builder objects to Livewire components, by simply generating the SQL and passing it as a string to the component. The key part is the render() method of the LoadMore and LoadMoreButton components. Here's the method from the LoadMore component, as a reminder.

public function render()
{
    /* Add limit and offset to the query based on the current page and items per page */
    $limit  = $this->perPage + 1;
    $offset = ($this->page - 1) * $this->perPage;
    $sqlStatement = "{$this->sqlStatement} limit {$limit} offset {$offset}";

    /* Run the query using the Eloquent Builder, if a modelName is provided, else the DB facade */
    if (empty($modelClass)) {
        $rows = DB::select($sqlStatement, $this->bindings);
    } else {
        $modelClass = 'App\\Models\\' . $this->modelName;
        $rows = $modelClass::fromQuery($sqlStatement, $this->bindings);
    }
    $paginated_rows = new Paginator($rows, $this->perPage, $this->page);

    return view('livewire.load-more', [
        'rows' => $paginated_rows,
    ]);
}