Is something like this possible?

Is something like this possible with prisma:

SELECT a.*, s.app_status
FROM application AS a
         JOIN (
    SELECT application_id, app_status, created_at
    FROM application_status
    WHERE created_at = (
        SELECT MAX(created_at)
        FROM application_status AS sub
        WHERE sub.application_id = application_status.application_id
    ) AND app_status IN ('accepted')
) AS s ON a.id = s.application_id
WHERE a.form_id = 'staff'
ORDER BY created_at DESC
LIMIT 10
OFFSET 0;

SELECT a.*, s.app_status
FROM application AS a
         JOIN (
    SELECT application_id, app_status, created_at
    FROM application_status
    WHERE created_at = (
        SELECT MAX(created_at)
        FROM application_status AS sub
        WHERE sub.application_id = application_status.application_id
    ) AND app_status IN ('accepted')
) AS s ON a.id = s.application_id
WHERE a.form_id = 'staff'
ORDER BY created_at DESC
LIMIT 10
OFFSET 0;

I have a table with applications, and a table with the differents status's the applications has. Now, I want to get applications with a specific form_id, with the status, but only the latest status. Then I would like to filter that status, so it's only "pending" or "accpeted for example..

The above query does what I want, but I can't really see how I should do that in prisma.

Prisma•7mo ago•

4 replies

Børge

Is something like this possible?

Is something like this possible with prisma:

SELECT a.*, s.app_status
FROM application AS a
         JOIN (
    SELECT application_id, app_status, created_at
    FROM application_status
    WHERE created_at = (
        SELECT MAX(created_at)
        FROM application_status AS sub
        WHERE sub.application_id = application_status.application_id
    ) AND app_status IN ('accepted')
) AS s ON a.id = s.application_id
WHERE a.form_id = 'staff'
ORDER BY created_at DESC
LIMIT 10
OFFSET 0;

SELECT a.*, s.app_status
FROM application AS a
         JOIN (
    SELECT application_id, app_status, created_at
    FROM application_status
    WHERE created_at = (
        SELECT MAX(created_at)
        FROM application_status AS sub
        WHERE sub.application_id = application_status.application_id
    ) AND app_status IN ('accepted')
) AS s ON a.id = s.application_id
WHERE a.form_id = 'staff'
ORDER BY created_at DESC
LIMIT 10
OFFSET 0;

Is something like this possible?

Similar Threads

Is something like this possible?

Similar Threads

Similar Threads

Similar Threads