Sorting Users by Related Table Columns in Laravel API

Hi,
I want to implement sorting on my users table using some columns from related tables — for example, date_of_birth from the profiles table and status from the tickets table — combined with columns from the users table itself.

Currently, I’m constructing the URL like this:
http://localhost:8000/authors?search=Co&searchBy=first_name&sort=profiles.date_of_birth,users.status

In this format, the part before the dot represents the table name, and the part after the dot represents the column I want to sort by. Is this approach correct?

Think also for safety from injection i can make a array inside the class that contains the feilds that i want to sort with and check if value provided is included in the array of sortable other than that i can ignore

Notes:

There’s a one-to-one relationship between the users and profiles tables.
There’s a one-to-many relationship between the users and tickets tables.

included my UsersFilters class where there are sort method

<?php

namespace App\Filters;

use Illuminate\Database\Eloquent\Builder;
use Illuminate\Support\Str;

class UsersFilter extends BaseFilter
{
    protected array $searchFields = [
        'first_name',
        'last_name',
        'email',
        'username',
    ];


    protected array $relations = [
        'profiles',
        'tickets',
    ];

    protected array $sortable = [
        'users.status',
        'users.created_at',
        'users.updated_at',
        'users.email_verified_at',
        'profiles.date_of_birth',
        'profiles.gender',
        'profiles.created_at',
        'profiles.updated_at',
        'tickets.status',
        'tickets.created_at',
        'tickets.updated_at',
    ];


    protected function filter(array $filters = []): Builder
    {

        return $this->builder;
    }

    public function DOB(...$ranges): Builder
    {

        return $this->builder;
    }

    protected function sort(...$fields): Builder
    {
        $sortableFields = explode(',', ...$fields);

        foreach ($this->relations as $relation) {
            $this->builder->join($relation, $this->modelTable . '.id', '=', $relation . '.user_id');
        }

        foreach ($sortableFields as $field) {

            $table = Str::before($field, '.');

            $column = Str::after($field, '.');


            if (!in_array(Str::after($field, '-'), $this->sortable)) {
                continue;
            }

            if (Str::startsWith($field, '-')) {
                clock(Str::after($table . '.' . $column, '-'));
                $this->builder->orderByDesc(Str::after($table . '.' . $column, '-'));
            } else {
                $this->builder->orderBy(Str::after($table . '.' . $column, '-'));
            }

        }


        return $this->builder;
    }
}

LaryAI

4 months ago

Level 58

Certainly! Here’s how you can handle sorting users by related table columns in Laravel:

1. Accept Sort Parameters

Your URL pattern (e.g., sort=profiles.date_of_birth,users.status) is reasonable and extendable for this use case.

2. Parse the Sort Parameters

Split on commas to get each sort, then on the dot to separate the relation/table and column.

3. Apply Sorting with Joins

You will need to join the related tables to be able to sort on their columns.

Example Implementation

Assuming you have these relationships:

User hasOne Profile
User hasMany Tickets

Here’s how you might implement it in a controller:

public function index(Request $request)
{
    $query = User::query();

    // Example: sort=profiles.date_of_birth,users.first_name,tickets.status
    $sorts = explode(',', $request->input('sort', ''));

    // Handle joining related tables only if needed:
    foreach ($sorts as $sort) {
        if (!$sort) continue;

        [$table, $column] = explode('.', $sort);

        if ($table === 'profiles') {
            // Profiles: one-to-one, so left join
            $query->leftJoin('profiles', 'users.id', '=', 'profiles.user_id');
            $query->addSelect('profiles.date_of_birth');
        }

        if ($table === 'tickets') {
            // Tickets: one-to-many, usually you need aggregation (e.g., latest ticket status)
            // Let's assume you want the MAX status (latest):
            $query->leftJoin('tickets', 'users.id', '=', 'tickets.user_id');
            $query->addSelect('tickets.status');
        }

        if ($table === 'users') {
            // Already in users table
            $query->addSelect("users.$column");
        }
    }

    // Now, apply the order bys (after table joins, to avoid ambiguous columns)
    foreach ($sorts as $sort) {
        if (!$sort) continue;

        [$table, $column] = explode('.', $sort);

        $query->orderBy("$table.$column");
    }

    // Make sure to use distinct users if multiple tickets
    $users = $query->distinct()->paginate();

    return response()->json($users);
}

Important Notes

Ambiguous columns: If columns have the same name in different tables, always use the full table.column format.
One-to-many (tickets): Sorting by a related table with one-to-many requires defining which ticket you want to sort by (e.g., latest, earliest). You may need to join a subquery (e.g., latest_tickets) instead of the tickets table directly.
Performance: Only join when necessary.

Bonus: Use Eloquent Scopes

For cleaner code, consider adding sorting logic in Eloquent query scopes.

Summary:
Your approach with the sort parameter is solid. Just remember to join the related tables in your query builder and handle one-to-many relationships carefully (aggregate or pick a specific ticket). The code above shows a general approach to handle this requirement.

minaremonshaker

4 months ago

Level 6

I am sorry community I forgot it's Christmas today، marry Christmas to you all and wish you all happy new year

Please or to participate in this conversation.