Laravel Question posted in
The official Laravel Documentation can be found here.

How to sort/order query based on presence in array in laravel?

I want to sort/order a query in laravel. For example I have a model where ‘id’ ranges from 1 to 100. And I have an array of ids of selected model elements eg. [1,3,12,32,…].

I want to sort my model based on its id being present in the array. For ascending order the elements not present in the array will come first and for descending order the element present in the array will come first.

I have tried to use callback function in orderBy() clause but it doesn’t work.

Tags:

2

Answers


  1. 0

    You can directly order by IN in your query

    $ids = [1,3,12,32];
Model::query()
    ->orderBy(DB::raw('id IN('.implode(',', $ids).')'), 'desc')
    ->paginate(); // or get()

    A safer way would be

    Model::query()
    ->orderBy(DB::raw('id IN(:ids)'), 'desc')
    ->addBinding(['ids' => implode(',', $ids)])
    ->paginate(); // or get()
    Login or Signup to reply.
  2. 0

    In Laravel, if you want to sort a query based on whether an ID is present in a given array, you can achieve this using a combination of orderByRaw and conditional sorting. Here’s how you can accomplish sorting based on whether an ID is present in an array:

    Scenario

    You have a model with id values ranging from 1 to 100, and an array of selected IDs (e.g., [1, 3, 12, 32, …]). You want to sort your results such that:

    • Ascending Order: Elements not present in the array come first.
    • Descending Order: Elements present in the array come first.

    Solution

    To accomplish this, you need to use a CASE statement within orderByRaw to prioritize records based on whether their IDs are in the array.

    Here’s how you can do it:

    1. Prepare Your Array: Ensure your array of IDs is available. For this
      example, let’s assume it’s stored in a variable $selectedIds.
    2. Query with Sorting: Use orderByRaw to create a custom sorting clause
      based on the presence of IDs in the array.

    Example Code

    // Example array of selected IDs
$selectedIds = [1, 3, 12, 32];

// Convert array to a comma-separated list for SQL query
$idsList = implode(',', $selectedIds);

// Query with sorting based on the presence in the array
$results = YourModel::query()
    ->orderByRaw("FIELD(id, $idsList) DESC, id ASC") // For descending order with selected IDs first
    ->get();

// For ascending order where IDs not in the array come first
$resultsAsc = YourModel::query()
    ->orderByRaw("FIELD(id, $idsList) DESC, id ASC") // This will place selected IDs first, modify if needed
    ->get();

    This approach should give you the flexibility to sort based on the presence of IDs in your array and order the results according to your needs.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search