Eloquent orderByRaw error with two withCount() value on PGSQL

[denizgolbas]

-Likes table migration:

public function up()
  {
      Schema::create('likes', function (Blueprint $table) {
          $table->id();
          $table->bigInteger('user_id')->unsigned();
          $table->boolean('direction');
          $table->morphs('likeable');
          $table->timestamps();
      });
      Schema::table('likes', function ($table) {
          $table->foreign('user_id')->references('id')->on('users')->onDelete('cascade');
      });
  }

Like model:

    public function likeable()
    {
        return $this->morphTo();
    }

In Entry model:

    public function likes(): morphMany
    {
        return $this->morphMany(Like::class, 'likeable')->where('direction', true);
    }

    public function dislikes(): morphMany
    {
        return $this->morphMany(Like::class, 'likeable')->where('direction', false);
    }

    public function likesAndDislikes(): morphMany
    {
        return $this->morphMany(Like::class, 'likeable');
    }

I run this query:

Entry::withCount(['likes', 'dislikes'])
        ->orderByRaw('(likes_count + (dislikes_count * -1)) desc')
        ->paginate();

I got error:

quote
Illuminate\Database\ QueryException
PHP 8.1.3
9.45.1
SQLSTATE[42703]: Undefined column: 7 ERROR: column "likes_count" does not exist LINE 1: ... $4) as "dislikes_count" from "entries" order by (likes_coun... ^
SELECT
"entries".,
(
SELECT
count()
FROM
"likes"
WHERE
"entries"."id" = "likes"."likeable_id"
AND "likes"."likeable_type" = entries
AND "direction" = 1
) AS "likes_count",
(
SELECT
count(*)
FROM
"likes"
WHERE
"entries"."id" = "likes"."likeable_id"
AND "likes"."likeable_type" = entries
AND "direction" = 0
) AS "dislikes_count"
FROM
"entries"
ORDER BY
(likes_count + (dislikes_count * -1)) DESC
limit
15 OFFSET 0

How can I order the model by this formula (likes_count + (dislikes_count * -1)

Note: this query works on mysql. But not on pgsql.

[denizgolbas]

Update:
Problem is PGSQL can not get aliased value from agregeated query. It must be taken from sub-select query.

[whereandhere]

这是来自QQ邮箱的自动回复邮件。   您好，来信已收到，谢谢。祝您生活愉快。

[denizgolbas]

Hello @staudenmeir can you fix this for pgsql builder/grammar for eloquent?

[denizgolbas]

Another sample this query generated by eloquent and it works on mysql:

select 
  "banks"., 
  (
    select 
      count() 
    from 
      "bank_accounts" 
    where 
      "banks"."id" = "bank_accounts"."bank_id" 
      and "bank_accounts"."deleted_at" is null
  ) as "bank_accounts_count", 
  (
    select 
      count(*) 
    from 
      "contact_people" 
      inner join "contactables" on "contact_people"."id" = "contactables"."contact_person_id" 
    where 
      "banks"."id" = "contactables"."contactable_id" 
      and "contactables"."contactable_type" = 'banks'
  ) as "contact_persons_count" 
from 
  "banks" 
where 
  "banks"."deleted_at" is null 
order by 
  bank_accounts_count + contact_persons_count DESC

But postgre syntax like this:

SELECT *
FROM (
  SELECT 
    "banks".*, 
    (
      SELECT 
        count(*) 
      FROM 
        "bank_accounts" 
      WHERE 
        "banks"."id" = "bank_accounts"."bank_id" 
        AND "bank_accounts"."deleted_at" IS NULL
    ) AS "bank_accounts_count", 
    (
      SELECT 
        count(*) 
      FROM 
        "contact_people" 
        INNER JOIN "contactables" ON "contact_people"."id" = "contactables"."contact_person_id" 
      WHERE 
        "banks"."id" = "contactables"."contactable_id" 
        AND "contactables"."contactable_type" = 'banks'
    ) AS "contact_persons_count" 
  FROM 
    "banks" 
  WHERE 
    "banks"."deleted_at" IS NULL 
) AS subquery
ORDER BY 
  subquery.bank_accounts_count + subquery.contact_persons_count DESC

[staudenmeir]

@denizgolbas Laravel can't handle this kind of query for PostgreSQL users automatically.

But postgre syntax like this:

You need to build this query yourself with Eloquent (or the query builder) using fromSub().