group by a column & count total foreign relations within prisma

tl;dr

the problem

db schema.

I have two prisma models,

Browser with name and version & id
Redirect with browserID (that has a 1-m relation with Browser [a single browser type can be assigned to many redirects]), some other similar 1-m xyzID.

what i wanna do.

I want to

group browsers by name (say (opera, 12.0) & (opera, 10.0) can live under the same group opera) and I wanna sum the total redirects that are attached to the browser. So, Using the same opera example if (opera, 12.0) has 10 redirects & (opera, 10.0) has 15 redirects. My count should be 25.
In the same query. I also want to filter by Redirect ID. So, I still want to count by grouping but only count those browsers who are connected to my Redirect.

The final result should be like,

[
   { name: "opera", count: 25 }, // opera 12.0's 10 redirects & opera 10.0's 15 redirects
    // ...
]

what i've tried.

It's not possible to do this with prisma normally as much I've tried so I attempted to queryRaw. My query looked like this but it doesn't work.

SELECT
  "public"."Browser"."name",
  COUNT(*) as count
FROM
  "public"."Browser"
JOIN
  "public"."Redirect" ON "public"."Browser"."id" = "public"."Redirect"."browserID"
WHERE
  "public"."Redirect"."id" = ${input.id} AND "public"."Redirect"."id" IS NOT NULL
GROUP BY
  "public"."Browser"."name"

I've also tried DISTINCT & stuff but this doesn't work either.

If anyone knows this stuff, let me know!

Solution

Okay! I think "groupBy" & counting of foreign fields is not possible using prisma without using queryRaw. To achieve the above result. I wrote the following raw query & it works as expected.

SELECT
  "public"."Browser"."name" AS name,
  COUNT("public"."Redirect"."id") AS count
FROM
  "public"."Browser"
JOIN
  "public"."Redirect" ON "public"."Browser".id = "public"."Redirect"."browserID"
WHERE
  "public"."Redirect"."urlID" = ${input.id}
GROUP BY
  "public"."Browser"."name"
ORDER BY
  count DESC

The result,

[
  { name: 'Firefox', count: 27n },
  { name: 'Opera', count: 25n },
  { name: 'Chrome', count: 20n },
  { name: 'IE', count: 16n },
  { name: 'Safari', count: 13n }
]