title: LEFT JOINS and aggregation in a single Prisma query
body: <p>I have a database with multiple tables that frequently need to be queried with <code>LEFT JOIN</code> so that results contain aggregated data from other tables. Snippet from my Prisma schema:</p>
  <pre><code>model posts {
    id                Int      @id @unique @default(autoincrement())
    user_id           Int
    movie_id          Int      @unique
    title             String   @db.Text
    description       String?  @db.Text
    tags              Json?
    created_at        DateTime @default(now()) @db.DateTime(0)
    image             String?  @default(&quot;https://picsum.photos/400/600/?blur=10&quot;) @db.VarChar(256)
    year              Int
    submitted_by      String   @db.Text
    tmdb_rating       Decimal? @default(0.0) @db.Decimal(3, 1)
    tmdb_rating_count Int?     @default(0)
  }
  
  model ratings {
    id         Int       @unique @default(autoincrement()) @db.UnsignedInt
    entry_id   Int       @db.UnsignedInt
    user_id    Int       @db.UnsignedInt
    rating     Int       @default(0) @db.UnsignedTinyInt
    created_at DateTime  @default(now()) @db.DateTime(0)
    updated_at DateTime? @db.DateTime(0)
  
    @@id([entry_id, user_id])
  }
  </code></pre>
  <p>If I wanted to return the average rating when querying <code>posts</code>, I could use a query like:</p>
  <pre><code>SELECT 
      p.*, ROUND(AVG(rt.rating), 1) AS user_rating
  FROM
      posts AS p
          LEFT JOIN
      ratings AS rt ON rt.entry_id = p.id
  GROUP BY p.id;
  </code></pre>
  <p>I'm not exactly sure how/whether I can achieve something similar with Prisma, because as it stands right now, it seems like this would require two separate queries, which isn't optimal because there is sometimes the need for 2 or 3 joins or <code>SELECT</code>s from other tables.</p>
  <p>How can I make a query/model/something in Prisma to achieve the above?</p>