Prisma, select row from table 1, depending on the latest foreign key in table 2

Solution 1:

Unfortunately, I don't think it's possible to do what you're hoping directly with a Prisma Query at the moment. I can suggest two possible workarounds though:

Approach 1: Fetch the most recent publicationStatus along with the publication and check the status inside your node application.

This is what the query would look like:

    let publication = await prisma.publication.findFirst({
        where: {
            id
        },
        include: {
            publicationStatus: {
                orderBy: {
                    createdAt: 'desc'
                },
                take: 1
            }
        }
    });

    // check publication.publicationStatus[0].status and handle appropriately

Approach 2: Write a raw SQL query using the queryRaw method.

For a single publication, I think it would be easier to use approach 1. However, if you want to return all LIVE publications (you mentioned this in a comment), the performance characteristics of approach 1 might be undesirable.