How to order models by polymorphic relation?

I have Product, ProductAction, Purchase and ExcessStock models. Product hasMany ProductAction with a relation 'actions'. ProductAction morphTo Purchase, ExcessStocks etc. with a relation 'actionable'.

Now in Product Class i want to set the last ProductAction by 'processed_at' column of actionable relation like this:

public function lastEnteringAction()
{
    return $this->hasOne(ProductAction::class)
    ->where('quantity_in', '<>', null)
    ->orderBy('actionable.processed_at', 'desc');
}

How can i solve it? Thank you.

kevinbui

4 years ago

Best Answer

Level 41

As I understand, if we want to order by a field in a different table, we got to join the tables together.

I got a pretty similar example. In the following example, questions and answers can both have many likes. I want to order the likes by the created_at field from the questions and answers tables:

SELECT likes.*, 
CASE 
    WHEN questions.id IS NOT NULL then questions.created_at
    WHEN answers.id IS NOT NULL then answers.created_at
    ELSE NULL
END AS likable_created_at
FROM likes 
  LEFT JOIN questions ON likes.likable_id = questions.id AND likes.likable_type = 'App\\Question'
  LEFT JOIN answers ON likes.likable_id = answers.id AND likes.likable_type = 'App\\Answer'
ORDER BY likable_created_at;

So we got to implement joins with multiple conditions.

Back to your problem, lets create a query scope in the ProductAction model:

use Illuminate\Database\Eloquent\Builder;

class ProductAction extends Model
{
    public function scopeOrderByProcessedAt(Builder $query, $direction = 'asc')
    {
        return $query->selectRaw('
            product_actions.*,
            CASE 
                WHEN purchases.id IS NOT NULL then purchases.processed_at
                WHEN excess_stocks.id IS NOT NULL then excess_stocks.processed_at
            ELSE NULL
            END AS actionable_processed_id
        ')
        ->leftJoin('purchases', function ($join) {
            $join->on('product_actions.actionable_id', '=', 'purchases.id')
                ->on('actionable_type', '=', 'App\\Models\\Purchase'),
        })
        ->leftJoin('excess_stocks', function ($join) {
            $join->on('product_actions.actionable_id', '=', 'excess_stocks.id')
                ->on('actionable_type', '=', 'App\\Models\\ExcessStock'),
        })
        ->orderBy('actionable_processed_id', $direction);
    }
}

Then you can get the last entering action like this:

public function lastEnteringAction()
{
    return $this->hasOne(ProductAction::class)
        ->whereNotNull('quantity_in')
        ->orderByProcessedAt('desc');
}

That's how I solve the problem, not sure whether there are simpler solutions.

To be more efficient, pls also checkout the Has One Of Many relationship.

2 likes