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

user_13597441 0
Viewed 588

Apologies for the bad title, struggling to think of another.

Currently, I have 2 tables, publication and publicationStatus.

A publication will looking something like:

{
    "id": "ckyil950d00027v2str5ljo7h",
    "url_slug": "ckyil950e00037v2s4mqxvaho",
    "type": "PEER_REVIEW",
    "title": "Intersting review",
    "content": "Content is optional at this stage",
    "doi": "1093/ajae/aaq063",
    "createdBy": "test-user-1",
    "createdAt": "2022-01-17T11:12:50.845Z",
    "updatedAt": "2022-01-17T11:12:50.847Z",
    "publicationStatus": [
        {
            "status": "LIVE",
            "createdAt": "2022-01-19T11:12:50.846Z",
            "id": "ckyil950e00047v2sx4urbfte"
        },
        {
            "status": "DRAFT",
            "createdAt": "2022-01-17T11:12:50.846Z",
            "id": "ckyil950e00047v2sx4urbfth"
        }
    ],
    "user": {
        "id": "test-user-1",
        "firstName": "Test",
        "lastName": "User 1"
    }
}

Where publication has a 1 to many relationship with publicationStatus.

What I need to do is a find query where it only returns a publication if the latest publicationStatus for that publication, has a status of LIVE.

Any ideas?

Edit: The closest I could come to is this psuedo code:

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

This code does not work, but demonstrates a picture of what I'm trying to achieve.

prisma
1 Answers

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.

user_88558440
Related