Back

Getting relation subquery


Hello! There is some problem with ordering eloquent query results by field from related table. Every solutions which I can find suggest me to make joins to it. I don't think it is good way. In my company we have many projects on laravel and for any case of ordering be related field we need to write it again and result doesn't looks cute. I'm trying to make some universal solution but I see strange behavior:

Here is model:

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\SoftDeletes;

class Category extends Model
{
    use SoftDeletes;

    ....

    public function translation() {
        $lang = session('lang', 'en');

        return $this->hasOne(CategoryTranslation::class)->where('locale', $lang);
    }
}

And when I'm trying to make scope for ordering:

    public function scopeOrderByRelated($query, $orderField, $desc = 'DESC')
    {
        $entities = explode('.', $orderField);

        $fieldName = array_pop($entities);
        // do not pay attention to it, in the future I'm going to do here a recursive nesting in order to have support for complex relationships, but now I'm just taking the nearest relation
        $relationName = array_shift($entities);

        $relation = $this->__callStatic($relationName, []);

        $subQuery = $relation->getQuery()->toSql();
        $subQuery = str_replace('*', $fieldName, $subQuery);

        $query->selectSub($subQuery, 'orderedField')
            ->addSelect("{$this->getTable()}.*")
            ->orderBy('orderedField', $desc);
    }

The problem is that this $relation->getQuery()->toSql() returns following sql

select title from "category_translations" where "category_translations"."category_id" is null and "category_translations"."category_id" is not null and "locale" = ?

This part of sql looks strange. "category_translations"."category_id" is null and "category_translations"."category_id" is not null

Expected behavior:

$subQuery = $relation->getQuery()->toSql();

should return

select title from "category_translations" where category_translations.category_id = categories.id and "locale" = ?

if I get this result, then without any problems I can implement the sorting on the connected table. Or may be –°an you suggest the best way to get a subquery of relation?

Laravel version: 5.6 Php version: 7.1 Sql: PostgreSql 9.6.4

Jonas Staudenmeir replied 1 month ago

It's a bit hacky, but it works:

Category::withCount(['translation as title' => function($query) {
    $query->select('title');
}])->orderBy('title')->get();

Sign in to participate in this thread!



We'd like to thank these amazing companies for supporting us