2
4

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 5 years have passed since last update.

Laravel 五十音順でコレクションを取得する

Posted at

#やりたいこ

  • 五十音順に並び替えてコレクションを取得する
  • name_kanaが NULL場合は、nameの五十音順でデータをけつにつける

#参考記事
SQLで検索する時、空白やNULLのデータを後ろに並べたい
https://qiita.com/AkihikoIkeda/items/0f926f023b06066dc759
#確認環境

  • Laravel5.6
  • PHP7.3
  • MySQL 5.6.39

#コード

<?php

namespace App\Http\Controllers;
use App\User;

class UserController extends Controller
{
    const PAGINATION_PER_PAGE = 50;
    
    $users = User::orderBy(DB::raw(
    "case when name_kana is NULL then '2'" . // 1. NULLの場合は2番目
    " when name_kana = '' then '1'" .        // 2. '' の場合は1番目
    " else '0' end, " .                      // 3. 値が入っていたら0番目
    "name_kana, " .                          // 4. ひらがな五十音順 *1
    "name"                                   // 5. 記号、数字、アルファベット、ひらがな(カナ)、漢字の順
    ))->paginate(self::PAGINATION_PER_PAGE);
}

#要点

// 一行で書くと…
$users = User::orderBy(DB::raw("case when name_kana is NULL then '2' when name_kana = '' then '1' else '0' end, name_kana, name"))->paginate(self::PAGINATION_PER_PAGE);

#やったこと

  • コレクションを取得してsortBy('name_kana')とか… だめだった。
  • *1の記述がないと、適当なならびにならなかった。
2
4
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
2
4

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?