Hi @nix0x00 👋

Thank you for raising this question.

Now that prisma has launched relations feature, how can I actually utilize it.

To use joins, you will need to enable the new relationLoadStrategy, you'll first need to add the preview feature flag to the generator block of your Prisma Client:

generator client {
  provider        = "prisma-client-js"
  previewFeatures = ["relationJoins"]
}

Once that's done, you'll need to re-run prisma generate for this change to take effect and pick a relation load strategy in your queries.
Here is an example that uses the new join strategy:

const usersWithPosts = await prisma.user.findMany({
  relationLoadStrategy: "join", // or "query"
  include: {
    posts: true,
  },
});

You should note that you can choose between database level join and application level join. With database level join, you would use join as the value for the relationLoadStrategy and with application level join, you would use query. You can take a look at this blog post to learn more.

can following query be converted to prisma query so that I don't have to use raw query.

If you want to convert this raw SQL query into a Prisma query, you might need to use the groupBy method provided by Prisma. But, as of now, Prisma does not support grouping on relations or counting based on a relation, as mentioned in the Github discussion. So at the moment, it's not currently possible to convert your raw SQL query into a Prisma query due to the limitations of the groupBy method.

If this answers your question, it would be great if you could mark this Discussion as answered to indicate that it has been resolved.

Otherwise please let us know how else we can help you further or close the Discussion if it was resolved in some other way 🙏