entrouvert
/
docbow
16
0
Fork 0
Code Issues Pull Requests Releases Activity

sql: optimize the hand written queries (#49580)
gitea/docbow/pipeline/head Build started... Details

This commit is contained in:
Pierre Ducroquet 2022-05-23 17:42:49 +02:00
parent f2dd0da209
commit 7209ea81a1
1 changed files with 9 additions and 10 deletions
Show Stats Download Patch File Download Diff File Expand all files Collapse all files

19
docbow_project/docbow/sql.py
View File

@ -68,11 +68,10 @@ GET_UNSEEN_DOCUMENTS_SQL = '''SELECT d.id
FROM docbow_document AS d
INNER JOIN docbow_mailbox AS mb
ON mb.outbox = %%s AND mb.document_id = d.id AND mb.owner_id IN %s
LEFT JOIN docbow_seendocument as sd
ON sd.document_id = d.id AND sd.user_id = %%s
LEFT JOIN docbow_deleteddocument AS dd ON
dd.document_id = d.id AND dd.user_id = %%s
WHERE dd.id IS NULL AND sd.id IS NULL AND (d.private = %%s OR (mb.owner_id = %%s AND d.private = %%s))
WHERE
NOT EXISTS(SELECT 1 FROM docbow_deleteddocument dd WHERE dd.document_id = d.id AND dd.user_id = %%s)
AND NOT EXISTS(SELECT 1 FROM docbow_seendocument sd WHERE sd.document_id = d.id AND sd.user_id = %%s)
AND (d.private = %%s OR (mb.owner_id = %%s AND d.private = %%s))
GROUP BY d.id, d.date
ORDER BY d.date
'''
@ -81,9 +80,9 @@ GET_DOCUMENTS_SQL = '''SELECT d.id
FROM docbow_document AS d
INNER JOIN docbow_mailbox AS mb ON
mb.outbox = %%s AND mb.document_id = d.id AND mb.owner_id IN %s
LEFT JOIN docbow_deleteddocument AS dd ON
dd.document_id = d.id AND dd.user_id = %%s
WHERE dd.id IS NULL AND (d.private = %%s OR (mb.owner_id = %%s AND d.private = %%s))
WHERE
NOT EXISTS(SELECT 1 FROM docbow_deleteddocument dd WHERE dd.document_id = d.id AND dd.user_id = %%s)
AND (d.private = %%s OR (mb.owner_id = %%s AND d.private = %%s))
GROUP BY d.id, d.date
ORDER BY d.date'''
@ -93,8 +92,8 @@ GET_TRASH_DOCUMENTS_SQL = '''SELECT d.id
INNER JOIN docbow_mailbox AS mb ON
mb.outbox = %%s AND mb.document_id = d.id AND mb.owner_id IN %s
LEFT JOIN docbow_deleteddocument AS dd ON
dd.document_id = d.id AND dd.user_id = %%s
WHERE dd.soft_delete AND (d.private = %%s OR (mb.owner_id = %%s AND d.private = %%s))
dd.document_id = d.id AND dd.user_id = %%s AND dd.soft_delete
WHERE (d.private = %%s OR (mb.owner_id = %%s AND d.private = %%s))
GROUP BY d.id, d.date
ORDER BY d.date'''