How can I reduce these Prisma queries to a single query?

Question

I need to get the last of user by date udpate without knowing the id beforehand. Post , I can do it with two queries like this:

ctx.prisma.post.update({
  where: {
    id: await ctx.prisma.post
      .findFirst({
        where: { userId: "c2e4c855-768c-48ab-b9c1-155ce1090cd6" },
        orderBy: { date: "desc" },
      })
      .then(post => post.id),
  },
  data: {
    updated: true
  },
})

I've been trying to get it to work as a query but I've had no success, this is the best I've gotten as a SQL clause:

<code>UPDATE Post
SET updated = true
WHERE id IN (
    SELECT * FROM (
        SELECT id
        FROM Post
        WHERE user_id = "c2e4c855-768c-48ab-b9c1-155ce1090cd6"
            AND date = (SELECT MAX(date) from Post)) as x);
</code>

Although this is a clause, it also accesses the database twice. If there is a better way to do this in SQL, I'd be interested in learning it, but I don't want to use Prisma to send raw queries and lose all type safety.

Is what I'm looking for possible, or should I keep it in two queries?

Answer 1

If you only want to update the latest Post for a given user_id:

UPDATE Post
SET updated = true
WHERE user_id = 'c2e4c855-768c-48ab-b9c1-155ce1090cd6'
ORDER BY date DESC, id DESC
LIMIT 1;

I don't know how you would write this in prisma, but this is a SQL query.