MSSQL: Cannot perform aggregate. Is Not Null vs != Null & eloquent.

mssql error 130

Aku perlu dapatkan data daripada satu view yang mana ada dua jenis data yang dimasukkan bersama. Ideanya adalah jika planner_id adalah null maka data external_appli akan di ambil. Dan nilai akhir yang aku mahu dapatkan adalah jumlah tambah antara planner_id dan external_appli berdasarkan tahun dan jenis (tidak ditunjukkan di dalam table di bawah).

planner_id external_appli
null 1
1 null

#1 : is not null

select
  case when planner_id is not NULL
    then count(DISTINCT planner_id)
       else count(DISTINCT external_appli)
       end as total,
  course_type, course_year from list_user_to_course
where (ca_status is null AND status = 4) or (ca_status = 4)
group by course_year, course_type;

Kaedah pertama adalah dengan membuat query mudah yang mana mengira planner_id atau external_appli mengikut condition jika planner_id null atau tidak. Malangnya dengan kaedah ini MSSQL akan mengeluarkan satu ralat:

[S0001][130] Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

Yang membawa maksud query tidak boleh dijalankan disebabkan planner_id tidak dimasukkan sebagai salah satu elemen di dalam group by. Tetapi jika aku masukkan planner_id, hasil query akan tidak menepati kehendak kerana hasil akan terpecah mengikut planner_id dan perlu melakukan kiraan tambahan di bahagian aplikasi (PHP kes ini).

#2: != null

select
  case when planner_id != NULL
    then count(DISTINCT planner_id)
       else count(DISTINCT external_appli)
       end as total,
  course_type, course_year from list_user_to_course
where (ca_status is null AND status = 4) or (ca_status = 4)
group by course_year, course_type;

Query kedua adalah hampir sama kecuali pada baris kedua yang menukarkan daripada is not null kepada != null. Sebenarnya aku kurang pasti apakah perbezaan antara kedua ni. Tetapi apabila menggunakan kaedah ini, MSSQL tidak mengeluarkan sebarang notis ataupun ralat dan hasil query juga menepati kehendak.

Malangnya apabila ditukarkan kepada bentuk Eloquent dan dijalankan, terdapat ralat seperti cara #1 dan ini amat mengelirukan aku kerana di dalam konsol MSSQL #2 tiada masalah tetapi apabila melalui eloquent, ada masalah.

Ada dua punca yang aku suspek berlakunya masalah ini.

  1. PHP-MSSQL-PDO driver ada bug. Kerana daripada konsol MSSQL tiada masalah.
  2. Eloquent Laravel ada bug. Kerana tidak salah aku konsol MSSQL aku juga gunakan driver yang sama. Kalau tak salah.

#3: select from subquery

SELECT
  sum(total) AS total,
  course_type,
  course_year
FROM (
       SELECT
         count(DISTINCT planner_id) AS total,
         course_type,
         course_year
       FROM list_user_to_course
       WHERE ((ca_status IS NULL AND status = 4) OR (ca_status = 4)) AND planner_id IS NOT NULL
       GROUP BY course_year, course_type
       UNION ALL
       SELECT
         count(DISTINCT external_appli) AS total,
         course_type,
         course_year
       FROM list_user_to_course
       WHERE ((ca_status IS NULL AND status = 4) OR (ca_status = 4)) AND external_appli IS NOT NULL
       GROUP BY course_year, course_type
     ) x
GROUP BY course_year, course_type;

Setelah melihat sekitar stackoverflow aku merasakan aku perlu menukar query kepada subquery. Ideanya adalah untuk mengira jumlah berdasarkan hasil tambah antara dua query yang dibuat secara union all. Dan daripada query tersebut hasilnya adalah sama seperti apa yang aku harapkan.

Bonus: Eloquent untuk #3

Aku kongsi eloquent yang aku gunakan. Mana tahu ada orang boleh beri cara yang lebih mudah daripada ini.

// query pertama
$q = DB::table('list_user_to_course')
    ->select('course_type', 'course_year')
    ->addSelect(DB::raw('count(DISTINCT planner_id) AS total'))
    ->where(function ($a) {
        $a->whereNull('ca_status')
            ->whereRaw('status = 4');
    })
    ->orWhereRaw('ca_status = 4')
    ->groupBy('course_type', 'course_year');

// query ke dua
$w = DB::table('list_user_to_course')
    ->select('course_type', 'course_year')
    ->addSelect(DB::raw('count(DISTINCT external_appli) AS total'))
    ->where(function ($s) {
        $s->whereNull('ca_status')
            ->whereRaw('status = 4');
    })
    ->orWhereRaw('ca_status = 4')
    ->groupBy('course_type', 'course_year')
    ->unionAll($q); // link antara query pertama dengan kedua menggunakan union all

// kira jumlah menggunakan kaedah subquery
return $e = DB::table(DB::raw("({$w->toSql()}) as x"))
    ->select('course_type', 'course_year')
    ->addSelect(DB::raw('sum(total) as total'))
    ->groupBy('course_type', 'course_year')
    ->get();

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.