モデルに実装したメソッドを利用してorderByしたい

Question

Laravel 5.4 を使用したWebアプリを作成中です。
基本的な仕組みは出来たのですが、並べ替えについて質問です。

class Item extends Model
{
    public function myPrice () {
        if (\Auth::User()->role == 'admin') {
            $result = $this->master_price;
        } else {
            $data = $this->prices()->where('user_id', \Auth::User()->id)->first();
            $result = (is_null($data) ? $this->master_price : $data->price);
        }
        return $result;
    }

    public function prices()
    {
        return $this->hasMany(Price::class);
    }
}

Controller内で、このモデルクラスのmyPrice()を元に「orderBy」をかけたく思います。
※追記ここから
サンプルにする為コードを単純化していたのですが、単純にしすぎていました。
実際は上記のようなリレーションを挟んでの処理になります。
「該当のリレーションデータが存在しない場合は自クラスのデータを使う」という処理が入ります。
（自クラスのデータは絶対に入っています）
後出し仕様になってしまって申し訳ありません。
※追記ここまで

$itemList = Item::where('aaa', 'bbb')->orderBy('updated_by', 'desc')->paginate(20);
とりあえず上記コードが動くことは確認しました。
が、orderBy('myPrice', 'asc')にすると（当然ながら）動きません。

Controller側でmyPriceと同じ条件分岐をすればいい話なのですが、出来れば同じ処理を２つ書きたくありません。
Item::where('aaa', 'bbb')->get()したデータを元に配列作成、それをソートする方法もあると思いますが、
そうした場合paginateが使えないのでは？と思っています。

何か良い方法があればお教え頂けると助かります。

Comments

[masaya_ohashi]

疑問ですが、pricesがhasManyになっているのはどうしてでしょう？myPriceは1個しかデータがない想定で動作しているようですが、myPriceは「複数pricesレコードがあった場合、先頭1件を使う」という仕様なのでしょうか？

[masaya_ohashi]

もう一点、データベースはMySQLですか？

[sakura_hana]

１つのアイテムに対し、複数のユーザーが異なる価格を付ける為このような形としています（１ユーザーが１つのアイテムに付ける価格は１つだけです）。データベースはMySQLです。

Answer 1

masaya_ohashiさんの回答を元に試行錯誤した結果、解決しました。
一部エラー等出ていたのでscopeメソッドの中身だけ変更して動作するようにしました。

//Itemクラス内
public function scopeOrderByMyPrice($query) {
    if (\Auth::User()->role == 'admin') {
        return $query->orderBy('master_price', 'asc');
    }
    else {
        $query
            ->selectRaw('COALESCE((SELECT price FROM prices WHERE prices.item_id = items.id and prices.user_id = ? limit 1), items.master_price) as myPrice', [\Auth::User()->id])
            ->addSelect('items.*')
            ->orderBy('myPrice', 'asc')
            ;
        return $query;
    }
}

変更した所

• メソッド名を「scopeOfOrderByMyPrice」から「scopeOrderByMyPrice」へ（Ofが不要）
• admin時は直接並べ替え
• 「:」を使用するプリペアドステートメントだとエラーが出たので「?」を使うように変更
• joinだとpricesのデータが無い場合アイテムデータも表示されない、leftJoinだとアイテムデータが重複表示される、groupByは何故かエラーが出る……ので、価格だけselectで追加取得を試みてCOALESCEで反映
• COALESCEはraw内でしか使えないようなのでselectからselectRawへ変更

Comments

[masaya_ohashi]

おー、お見事です。Ofはすいませんでした。なにを見て私はOfを追加したのか…

Answer 2

scopeを使ってはどうでしょう？本来は独自のwhere句を書くための構文ですが、$queryを受け取れるのでこういった使い方もできるかもしれません(未検証)。

class Item extends Model
{
    // roleによって使用するカラム名を切り分ける
    private function myPriceColumnName() {
        if (\Auth::User()->role == 'admin') {
            return 'master_price';
        } else {
            return 'my_price';
        }
    }

    public function myPrice () {
        $column = $this->myPriceColumnName();
        return $this->$column; // カラム名を変数を使って指定
    }

    public function scopeOrderByMyPrice($query) {
        return $query->orderBy($this->myPriceColumnName(), 'asc'); // scope処理でorderByを付ける
    }
}

# 使い方
$itemList = Item::where('aaa', 'bbb')
    ->orderByMyPrice() // scopeXXXという名前のメソッドはクエリビルダでこうやって呼べる
    ->paginate(20);

仕様変更後

正直仕様が複雑すぎて、PHP側の処理とSQL側の処理を共通化して書くとか無理です…同じ動作をするように力技でSQLを書くしかないかと思います。

全然動作確認してないので、このまま正しく動くかどうかはわかりませんが…joinとかDB::rawのサブクエリを使えばどうにかできるかと思います。

class Item extends Model
{
    public function myPrice () {
        if (\Auth::User()->role == 'admin') {
            $result = $this->master_price;
        } else {
            $data = $this->prices()->where('user_id', \Auth::User()->id)->first();
            $result = (is_null($data) ? $this->master_price : $data->price);
        }
        return $result;
    }

    public function prices()
    {
        return $this->hasMany(Price::class);
    }

    public function scopeOrderByMyPrice($query) {
        if (\Auth::User()->role == 'admin') {
            $query
                ->select(
                    'items.*', // itemsテーブルのカラム全てと
                    'items.master_price AS my_price' // master_priceをmy_priceとして追加
                    );
        }
        else {
            $query
                ->join(\DB::raw('(SELECT item_id, price FROM prices WHERE user_id = :user_id) AS prices', ['user_id' => \Auth::User()->id]), 'prices.item_id', 'items.id') // pricesからuser_idの一致するレコードを引き出し、itemsのidと紐付ける
                ->groupBy('prices.item_id') // pricesのitem_idでグルーピングすることでpricesの結合を1件に絞り込む
                ->select(
                    'items.*', // itemsテーブルのカラム全てと
                    'COALESCE(prices.price, items.master_price) AS my_price' // joinしたpricesのpriceがあればpriceを、なければmaster_priceをmy_priceとして追加
                    );
        }
        return $query->orderBy('my_price', 'asc'); // my_price順でソート
    }
}

使い方は仕様変更前と同じです。Model内の実装だけが変わります。

Comments

[sakura_hana]

回答ありがとうございます。scopeは知らなかったのでとても参考になります。
ただ、申し訳無いのですが実際には単純にカラム名を指定する訳ではない状況でして、この場合どうしたらいいのか現在こちらでも調べています。
後出し仕様で申し訳ありませんが、ご確認頂ければ幸いです。

[masaya_ohashi]

うーん、この仕様ですとどうあがいても複雑なクエリ生成をせざるを得ないかと思います。とりあえず書いてみます。

[sakura_hana]

ご協力ありがとうございました！　お手数をお掛けして申し訳ありません。お陰様で解決出来ました。
Laravelで複雑なクエリを書いたのは初めてだったので苦労はしましたが、使い方が分かってきたように思います。
scope、COALESCEは初めて知った要素だったので大変ありがたかったです。また何かありましたら宜しくお願い致します。