P
Prisma•2w ago
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;
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.
3 Replies
Prisma AI Help
Prisma AI Help•2w ago
You're in no rush, so we'll let a dev step in. Enjoy your coffee, or drop into #ask-ai if you get antsy for a second opinion!
Nurul
Nurul•2w ago
I think you would need to use Raw Queries or TypedSQL for this kind of query
Børge
BørgeOP•16h ago
I have started experimenting with TypedSQL. But I don't know, how I would make the status, an argument. I'm using MySQL: 
-- @param {String} $1:formId
SELECT a.*, s.*, u.*
FROM application AS a
JOIN (
    SELECT *
    FROM application_status AS s1
    WHERE s1.created_at = (
        SELECT MAX(s2.created_at)
        FROM application_status AS s2
        WHERE s2.application_id = s1.application_id
    )
) AS s ON a.id = s.application_id
JOIN web_users AS u ON a.discord_id = u.discord_id
WHERE a.form_id = ?
AND s.app_status IN ('accepted', 'pending')
ORDER BY s.created_at DESC;
-- @param {String} $1:formId
SELECT a.*, s.*, u.*
FROM application AS a
JOIN (
    SELECT *
    FROM application_status AS s1
    WHERE s1.created_at = (
        SELECT MAX(s2.created_at)
        FROM application_status AS s2
        WHERE s2.application_id = s1.application_id
    )
) AS s ON a.id = s.application_id
JOIN web_users AS u ON a.discord_id = u.discord_id
WHERE a.form_id = ?
AND s.app_status IN ('accepted', 'pending')
ORDER BY s.created_at DESC;
I can see in the docs that you can pass in arrays, but only for PostgreSQL. How would I do it with MySQL?

Did you find this page helpful?