We are currently solving the following issue within our repository. Here is the issue text:
--- BEGIN ISSUE ---
En mode connecté, on fait une requête pour CHAQUE MP RECU !
Quand on est connecté, on passe par ce bout de code sur **toutes** les pages :

zds-site/templates/base.html ligne 258 : `{% with topics=user|interventions_privatetopics %}`

Il fait cette requête pour **chaque MP que vous avez reçu** !

```
SELECT `mp_privatetopic`.`id`, `mp_privatetopic`.`title`, `mp_privatetopic`.`subtitle`, `mp_privatetopic`.`author_id`, `mp_privatetopic`.`last_message_id`, `mp_privatetopic`.`pubdate` FROM `mp_privatetopic` WHERE `mp_privatetopic`.`id` = <id membre>
```

C'est à corriger d'urgence. Ça passe dans la v1.5.

Et après ça on s'étonne que certaines personnes trouvaient le site lent...


--- END ISSUE ---

Below are some code segments, each from a relevant file. One or more of these files may contain bugs.
--- BEGIN FILES ---
Path: `zds/utils/templatetags/interventions.py`
Content:
```
1 # coding: utf-8
2 
3 from datetime import datetime, timedelta
4 import time
5 
6 from django import template
7 from django.db.models import Q, F
8 
9 from zds.article.models import Reaction, ArticleRead
10 from zds.forum.models import TopicFollowed, never_read as never_read_topic, Post, TopicRead
11 from zds.mp.models import PrivateTopic, PrivateTopicRead
12 from zds.tutorial.models import Note, TutorialRead
13 from zds.utils.models import Alert
14 
15 
16 register = template.Library()
17 
18 
19 @register.filter('is_read')
20 def is_read(topic):
21     if never_read_topic(topic):
22         return False
23     else:
24         return True
25 
26 
27 @register.filter('humane_delta')
28 def humane_delta(value):
29     # mapping between label day and key
30     const = {1: "Aujourd'hui", 2: "Hier", 3: "Cette semaine", 4: "Ce mois-ci", 5: "Cette année"}
31 
32     return const[value]
33 
34 
35 @register.filter('followed_topics')
36 def followed_topics(user):
37     topicsfollowed = TopicFollowed.objects.select_related("topic").filter(user=user)\
38         .order_by('-topic__last_message__pubdate')[:10]
39     # This period is a map for link a moment (Today, yesterday, this week, this month, etc.) with
40     # the number of days for which we can say we're still in the period
41     # for exemple, the tuple (2, 1) means for the period "2" corresponding to "Yesterday" according
42     # to humane_delta, means if your pubdate hasn't exceeded one day, we are always at "Yesterday"
43     # Number is use for index for sort map easily
44     period = ((1, 0), (2, 1), (3, 7), (4, 30), (5, 360))
45     topics = {}
46     for tf in topicsfollowed:
47         for p in period:
48             if tf.topic.last_message.pubdate.date() >= (datetime.now() - timedelta(days=int(p[1]),
49                                                                                    hours=0, minutes=0,
50                                                                                    seconds=0)).date():
51                 if p[0] in topics:
52                     topics[p[0]].append(tf.topic)
53                 else:
54                     topics[p[0]] = [tf.topic]
55                 break
56     return topics
57 
58 
59 def comp(d1, d2):
60     v1 = int(time.mktime(d1['pubdate'].timetuple()))
61     v2 = int(time.mktime(d2['pubdate'].timetuple()))
62     if v1 > v2:
63         return -1
64     elif v1 < v2:
65         return 1
66     else:
67         return 0
68 
69 
70 @register.filter('interventions_topics')
71 def interventions_topics(user):
72     topicsfollowed = TopicFollowed.objects.filter(user=user).values("topic").distinct().all()
73 
74     topics_never_read = TopicRead.objects\
75         .filter(user=user)\
76         .filter(topic__in=topicsfollowed)\
77         .select_related("topic")\
78         .exclude(post=F('topic__last_message'))
79 
80     articlesfollowed = Reaction.objects\
81         .filter(author=user, article__sha_public__isnull=False)\
82         .values('article')\
83         .distinct().all()
84 
85     articles_never_read = ArticleRead.objects\
86         .filter(user=user)\
87         .filter(article__in=articlesfollowed)\
88         .select_related("article")\
89         .exclude(reaction=F('article__last_reaction'))
90 
91     tutorialsfollowed = Note.objects\
92         .filter(author=user, tutorial__sha_public__isnull=False)\
93         .values('tutorial')\
94         .distinct().all()
95 
96     tutorials_never_read = TutorialRead.objects\
97         .filter(user=user)\
98         .filter(tutorial__in=tutorialsfollowed)\
99         .exclude(note=F('tutorial__last_note'))
100 
101     posts_unread = []
102 
103     for art in articles_never_read:
104         content = art.article.first_unread_reaction()
105         posts_unread.append({'pubdate': content.pubdate,
106                              'author': content.author,
107                              'title': art.article.title,
108                              'url': content.get_absolute_url()})
109 
110     for tuto in tutorials_never_read:
111         content = tuto.tutorial.first_unread_note()
112         posts_unread.append({'pubdate': content.pubdate,
113                              'author': content.author,
114                              'title': tuto.tutorial.title,
115                              'url': content.get_absolute_url()})
116 
117     for top in topics_never_read:
118         content = top.topic.first_unread_post()
119         if content is None:
120             content = top.topic.last_message
121         posts_unread.append({'pubdate': content.pubdate,
122                              'author': content.author,
123                              'title': top.topic.title,
124                              'url': content.get_absolute_url()})
125 
126     posts_unread.sort(cmp=comp)
127 
128     return posts_unread
129 
130 
131 @register.filter('interventions_privatetopics')
132 def interventions_privatetopics(user):
133 
134     topics_never_read = list(PrivateTopicRead.objects
135                              .filter(user=user)
136                              .filter(privatepost=F('privatetopic__last_message')).all())
137 
138     tnrs = []
139     for tnr in topics_never_read:
140         tnrs.append(tnr.privatetopic.pk)
141 
142     privatetopics_unread = PrivateTopic.objects\
143         .filter(Q(author=user) | Q(participants__in=[user]))\
144         .exclude(pk__in=tnrs)\
145         .select_related("privatetopic")\
146         .order_by("-pubdate")\
147         .distinct()
148 
149     return {'unread': privatetopics_unread}
150 
151 
152 @register.filter(name='alerts_list')
153 def alerts_list(user):
154     total = []
155     alerts = Alert.objects.select_related("author").all().order_by('-pubdate')[:10]
156     for alert in alerts:
157         if alert.scope == Alert.FORUM:
158             post = Post.objects.select_related("topic").get(pk=alert.comment.pk)
159             total.append({'title': post.topic.title,
160                           'url': post.get_absolute_url(),
161                           'pubdate': alert.pubdate,
162                           'author': alert.author,
163                           'text': alert.text})
164         if alert.scope == Alert.ARTICLE:
165             reaction = Reaction.objects.select_related("article").get(pk=alert.comment.pk)
166             total.append({'title': reaction.article.title,
167                           'url': reaction.get_absolute_url(),
168                           'pubdate': alert.pubdate,
169                           'author': alert.author,
170                           'text': alert.text})
171         if alert.scope == Alert.TUTORIAL:
172             note = Note.objects.select_related("tutorial").get(pk=alert.comment.pk)
173             total.append({'title': note.tutorial.title,
174                           'url': note.get_absolute_url(),
175                           'pubdate': alert.pubdate,
176                           'author': alert.author,
177                           'text': alert.text})
178 
179     return total
180 
181 
182 @register.filter(name='alerts_count')
183 def alerts_count(user):
184     if user.is_authenticated():
185         return Alert.objects.count()
186     else:
187         return 0
188
```
--- END FILES ---

Please first localize the bug based on the issue statement, and then generate a patch according to the `git diff` format fenced by three backticks.

Here is an example:

```diff
diff --git a/examples/server_async.py b/examples/server_async.py
--- a/examples/server_async.py
+++ b/examples/server_async.py
@@ -313,4 +313,4 @@
 
 
 if __name__ == "__main__":
-    asyncio.run(run_async_server("."), debug=True)
+    asyncio.run(run_async_server(), debug=True)
diff --git a/examples/server_sync.py b/examples/server_sync.py
--- a/examples/server_sync.py
+++ b/examples/server_sync.py
@@ -313,5 +313,5 @@
 
 
 if __name__ == "__main__":
-    server = run_sync_server(".")
+    server = run_sync_server()
     server.shutdown()

```

diff --git a/zds/utils/templatetags/interventions.py b/zds/utils/templatetags/interventions.py
--- a/zds/utils/templatetags/interventions.py
+++ b/zds/utils/templatetags/interventions.py
@@ -4,11 +4,11 @@
 import time
 
 from django import template
-from django.db.models import Q, F
+from django.db.models import F
 
 from zds.article.models import Reaction, ArticleRead
 from zds.forum.models import TopicFollowed, never_read as never_read_topic, Post, TopicRead
-from zds.mp.models import PrivateTopic, PrivateTopicRead
+from zds.mp.models import PrivateTopic
 from zds.tutorial.models import Note, TutorialRead
 from zds.utils.models import Alert
 
@@ -131,22 +131,21 @@
 @register.filter('interventions_privatetopics')
 def interventions_privatetopics(user):
 
-    topics_never_read = list(PrivateTopicRead.objects
-                             .filter(user=user)
-                             .filter(privatepost=F('privatetopic__last_message')).all())
-
-    tnrs = []
-    for tnr in topics_never_read:
-        tnrs.append(tnr.privatetopic.pk)
-
-    privatetopics_unread = PrivateTopic.objects\
-        .filter(Q(author=user) | Q(participants__in=[user]))\
-        .exclude(pk__in=tnrs)\
-        .select_related("privatetopic")\
-        .order_by("-pubdate")\
-        .distinct()
-
-    return {'unread': privatetopics_unread}
+    # Raw query because ORM doesn't seems to allow this kind of "left outer join" clauses.
+    # Parameters = list with 3x the same ID because SQLite backend doesn't allow map parameters.
+    privatetopics_unread = PrivateTopic.objects.raw(
+        '''
+        select distinct t.*
+        from mp_privatetopic t
+        inner join mp_privatetopic_participants p on p.privatetopic_id = t.id
+        left outer join mp_privatetopicread r on r.user_id = %s and r.privatepost_id = t.last_message_id
+        where (t.author_id = %s or p.user_id = %s)
+          and r.id is null
+        order by t.pubdate desc''',
+        [user.id, user.id, user.id])
+
+    # "total" re-do the query, but there is no other way to get the length as __len__ is not available on raw queries.
+    return {'unread': privatetopics_unread, 'total': len(list(privatetopics_unread))}
 
 
 @register.filter(name='alerts_list')

{"golden_diff": "diff --git a/zds/utils/templatetags/interventions.py b/zds/utils/templatetags/interventions.py\n--- a/zds/utils/templatetags/interventions.py\n+++ b/zds/utils/templatetags/interventions.py\n@@ -4,11 +4,11 @@\n import time\n \n from django import template\n-from django.db.models import Q, F\n+from django.db.models import F\n \n from zds.article.models import Reaction, ArticleRead\n from zds.forum.models import TopicFollowed, never_read as never_read_topic, Post, TopicRead\n-from zds.mp.models import PrivateTopic, PrivateTopicRead\n+from zds.mp.models import PrivateTopic\n from zds.tutorial.models import Note, TutorialRead\n from zds.utils.models import Alert\n \n@@ -131,22 +131,21 @@\n @register.filter('interventions_privatetopics')\n def interventions_privatetopics(user):\n \n-    topics_never_read = list(PrivateTopicRead.objects\n-                             .filter(user=user)\n-                             .filter(privatepost=F('privatetopic__last_message')).all())\n-\n-    tnrs = []\n-    for tnr in topics_never_read:\n-        tnrs.append(tnr.privatetopic.pk)\n-\n-    privatetopics_unread = PrivateTopic.objects\\\n-        .filter(Q(author=user) | Q(participants__in=[user]))\\\n-        .exclude(pk__in=tnrs)\\\n-        .select_related(\"privatetopic\")\\\n-        .order_by(\"-pubdate\")\\\n-        .distinct()\n-\n-    return {'unread': privatetopics_unread}\n+    # Raw query because ORM doesn't seems to allow this kind of \"left outer join\" clauses.\n+    # Parameters = list with 3x the same ID because SQLite backend doesn't allow map parameters.\n+    privatetopics_unread = PrivateTopic.objects.raw(\n+        '''\n+        select distinct t.*\n+        from mp_privatetopic t\n+        inner join mp_privatetopic_participants p on p.privatetopic_id = t.id\n+        left outer join mp_privatetopicread r on r.user_id = %s and r.privatepost_id = t.last_message_id\n+        where (t.author_id = %s or p.user_id = %s)\n+          and r.id is null\n+        order by t.pubdate desc''',\n+        [user.id, user.id, user.id])\n+\n+    # \"total\" re-do the query, but there is no other way to get the length as __len__ is not available on raw queries.\n+    return {'unread': privatetopics_unread, 'total': len(list(privatetopics_unread))}\n \n \n @register.filter(name='alerts_list')\n", "issue": "En mode connect\u00e9, on fait une requ\u00eate pour CHAQUE MP RECU !\nQuand on est connect\u00e9, on passe par ce bout de code sur **toutes** les pages :\n\nzds-site/templates/base.html ligne 258 : `{% with topics=user|interventions_privatetopics %}`\n\nIl fait cette requ\u00eate pour **chaque MP que vous avez re\u00e7u** !\n\n```\nSELECT `mp_privatetopic`.`id`, `mp_privatetopic`.`title`, `mp_privatetopic`.`subtitle`, `mp_privatetopic`.`author_id`, `mp_privatetopic`.`last_message_id`, `mp_privatetopic`.`pubdate` FROM `mp_privatetopic` WHERE `mp_privatetopic`.`id` = <id membre>\n```\n\nC'est \u00e0 corriger d'urgence. \u00c7a passe dans la v1.5.\n\nEt apr\u00e8s \u00e7a on s'\u00e9tonne que certaines personnes trouvaient le site lent...\n\n", "before_files": [{"content": "# coding: utf-8\n\nfrom datetime import datetime, timedelta\nimport time\n\nfrom django import template\nfrom django.db.models import Q, F\n\nfrom zds.article.models import Reaction, ArticleRead\nfrom zds.forum.models import TopicFollowed, never_read as never_read_topic, Post, TopicRead\nfrom zds.mp.models import PrivateTopic, PrivateTopicRead\nfrom zds.tutorial.models import Note, TutorialRead\nfrom zds.utils.models import Alert\n\n\nregister = template.Library()\n\n\n@register.filter('is_read')\ndef is_read(topic):\n    if never_read_topic(topic):\n        return False\n    else:\n        return True\n\n\n@register.filter('humane_delta')\ndef humane_delta(value):\n    # mapping between label day and key\n    const = {1: \"Aujourd'hui\", 2: \"Hier\", 3: \"Cette semaine\", 4: \"Ce mois-ci\", 5: \"Cette ann\u00e9e\"}\n\n    return const[value]\n\n\n@register.filter('followed_topics')\ndef followed_topics(user):\n    topicsfollowed = TopicFollowed.objects.select_related(\"topic\").filter(user=user)\\\n        .order_by('-topic__last_message__pubdate')[:10]\n    # This period is a map for link a moment (Today, yesterday, this week, this month, etc.) with\n    # the number of days for which we can say we're still in the period\n    # for exemple, the tuple (2, 1) means for the period \"2\" corresponding to \"Yesterday\" according\n    # to humane_delta, means if your pubdate hasn't exceeded one day, we are always at \"Yesterday\"\n    # Number is use for index for sort map easily\n    period = ((1, 0), (2, 1), (3, 7), (4, 30), (5, 360))\n    topics = {}\n    for tf in topicsfollowed:\n        for p in period:\n            if tf.topic.last_message.pubdate.date() >= (datetime.now() - timedelta(days=int(p[1]),\n                                                                                   hours=0, minutes=0,\n                                                                                   seconds=0)).date():\n                if p[0] in topics:\n                    topics[p[0]].append(tf.topic)\n                else:\n                    topics[p[0]] = [tf.topic]\n                break\n    return topics\n\n\ndef comp(d1, d2):\n    v1 = int(time.mktime(d1['pubdate'].timetuple()))\n    v2 = int(time.mktime(d2['pubdate'].timetuple()))\n    if v1 > v2:\n        return -1\n    elif v1 < v2:\n        return 1\n    else:\n        return 0\n\n\n@register.filter('interventions_topics')\ndef interventions_topics(user):\n    topicsfollowed = TopicFollowed.objects.filter(user=user).values(\"topic\").distinct().all()\n\n    topics_never_read = TopicRead.objects\\\n        .filter(user=user)\\\n        .filter(topic__in=topicsfollowed)\\\n        .select_related(\"topic\")\\\n        .exclude(post=F('topic__last_message'))\n\n    articlesfollowed = Reaction.objects\\\n        .filter(author=user, article__sha_public__isnull=False)\\\n        .values('article')\\\n        .distinct().all()\n\n    articles_never_read = ArticleRead.objects\\\n        .filter(user=user)\\\n        .filter(article__in=articlesfollowed)\\\n        .select_related(\"article\")\\\n        .exclude(reaction=F('article__last_reaction'))\n\n    tutorialsfollowed = Note.objects\\\n        .filter(author=user, tutorial__sha_public__isnull=False)\\\n        .values('tutorial')\\\n        .distinct().all()\n\n    tutorials_never_read = TutorialRead.objects\\\n        .filter(user=user)\\\n        .filter(tutorial__in=tutorialsfollowed)\\\n        .exclude(note=F('tutorial__last_note'))\n\n    posts_unread = []\n\n    for art in articles_never_read:\n        content = art.article.first_unread_reaction()\n        posts_unread.append({'pubdate': content.pubdate,\n                             'author': content.author,\n                             'title': art.article.title,\n                             'url': content.get_absolute_url()})\n\n    for tuto in tutorials_never_read:\n        content = tuto.tutorial.first_unread_note()\n        posts_unread.append({'pubdate': content.pubdate,\n                             'author': content.author,\n                             'title': tuto.tutorial.title,\n                             'url': content.get_absolute_url()})\n\n    for top in topics_never_read:\n        content = top.topic.first_unread_post()\n        if content is None:\n            content = top.topic.last_message\n        posts_unread.append({'pubdate': content.pubdate,\n                             'author': content.author,\n                             'title': top.topic.title,\n                             'url': content.get_absolute_url()})\n\n    posts_unread.sort(cmp=comp)\n\n    return posts_unread\n\n\n@register.filter('interventions_privatetopics')\ndef interventions_privatetopics(user):\n\n    topics_never_read = list(PrivateTopicRead.objects\n                             .filter(user=user)\n                             .filter(privatepost=F('privatetopic__last_message')).all())\n\n    tnrs = []\n    for tnr in topics_never_read:\n        tnrs.append(tnr.privatetopic.pk)\n\n    privatetopics_unread = PrivateTopic.objects\\\n        .filter(Q(author=user) | Q(participants__in=[user]))\\\n        .exclude(pk__in=tnrs)\\\n        .select_related(\"privatetopic\")\\\n        .order_by(\"-pubdate\")\\\n        .distinct()\n\n    return {'unread': privatetopics_unread}\n\n\n@register.filter(name='alerts_list')\ndef alerts_list(user):\n    total = []\n    alerts = Alert.objects.select_related(\"author\").all().order_by('-pubdate')[:10]\n    for alert in alerts:\n        if alert.scope == Alert.FORUM:\n            post = Post.objects.select_related(\"topic\").get(pk=alert.comment.pk)\n            total.append({'title': post.topic.title,\n                          'url': post.get_absolute_url(),\n                          'pubdate': alert.pubdate,\n                          'author': alert.author,\n                          'text': alert.text})\n        if alert.scope == Alert.ARTICLE:\n            reaction = Reaction.objects.select_related(\"article\").get(pk=alert.comment.pk)\n            total.append({'title': reaction.article.title,\n                          'url': reaction.get_absolute_url(),\n                          'pubdate': alert.pubdate,\n                          'author': alert.author,\n                          'text': alert.text})\n        if alert.scope == Alert.TUTORIAL:\n            note = Note.objects.select_related(\"tutorial\").get(pk=alert.comment.pk)\n            total.append({'title': note.tutorial.title,\n                          'url': note.get_absolute_url(),\n                          'pubdate': alert.pubdate,\n                          'author': alert.author,\n                          'text': alert.text})\n\n    return total\n\n\n@register.filter(name='alerts_count')\ndef alerts_count(user):\n    if user.is_authenticated():\n        return Alert.objects.count()\n    else:\n        return 0\n", "path": "zds/utils/templatetags/interventions.py"}], "after_files": [{"content": "# coding: utf-8\n\nfrom datetime import datetime, timedelta\nimport time\n\nfrom django import template\nfrom django.db.models import F\n\nfrom zds.article.models import Reaction, ArticleRead\nfrom zds.forum.models import TopicFollowed, never_read as never_read_topic, Post, TopicRead\nfrom zds.mp.models import PrivateTopic\nfrom zds.tutorial.models import Note, TutorialRead\nfrom zds.utils.models import Alert\n\n\nregister = template.Library()\n\n\n@register.filter('is_read')\ndef is_read(topic):\n    if never_read_topic(topic):\n        return False\n    else:\n        return True\n\n\n@register.filter('humane_delta')\ndef humane_delta(value):\n    # mapping between label day and key\n    const = {1: \"Aujourd'hui\", 2: \"Hier\", 3: \"Cette semaine\", 4: \"Ce mois-ci\", 5: \"Cette ann\u00e9e\"}\n\n    return const[value]\n\n\n@register.filter('followed_topics')\ndef followed_topics(user):\n    topicsfollowed = TopicFollowed.objects.select_related(\"topic\").filter(user=user)\\\n        .order_by('-topic__last_message__pubdate')[:10]\n    # This period is a map for link a moment (Today, yesterday, this week, this month, etc.) with\n    # the number of days for which we can say we're still in the period\n    # for exemple, the tuple (2, 1) means for the period \"2\" corresponding to \"Yesterday\" according\n    # to humane_delta, means if your pubdate hasn't exceeded one day, we are always at \"Yesterday\"\n    # Number is use for index for sort map easily\n    period = ((1, 0), (2, 1), (3, 7), (4, 30), (5, 360))\n    topics = {}\n    for tf in topicsfollowed:\n        for p in period:\n            if tf.topic.last_message.pubdate.date() >= (datetime.now() - timedelta(days=int(p[1]),\n                                                                                   hours=0, minutes=0,\n                                                                                   seconds=0)).date():\n                if p[0] in topics:\n                    topics[p[0]].append(tf.topic)\n                else:\n                    topics[p[0]] = [tf.topic]\n                break\n    return topics\n\n\ndef comp(d1, d2):\n    v1 = int(time.mktime(d1['pubdate'].timetuple()))\n    v2 = int(time.mktime(d2['pubdate'].timetuple()))\n    if v1 > v2:\n        return -1\n    elif v1 < v2:\n        return 1\n    else:\n        return 0\n\n\n@register.filter('interventions_topics')\ndef interventions_topics(user):\n    topicsfollowed = TopicFollowed.objects.filter(user=user).values(\"topic\").distinct().all()\n\n    topics_never_read = TopicRead.objects\\\n        .filter(user=user)\\\n        .filter(topic__in=topicsfollowed)\\\n        .select_related(\"topic\")\\\n        .exclude(post=F('topic__last_message'))\n\n    articlesfollowed = Reaction.objects\\\n        .filter(author=user, article__sha_public__isnull=False)\\\n        .values('article')\\\n        .distinct().all()\n\n    articles_never_read = ArticleRead.objects\\\n        .filter(user=user)\\\n        .filter(article__in=articlesfollowed)\\\n        .select_related(\"article\")\\\n        .exclude(reaction=F('article__last_reaction'))\n\n    tutorialsfollowed = Note.objects\\\n        .filter(author=user, tutorial__sha_public__isnull=False)\\\n        .values('tutorial')\\\n        .distinct().all()\n\n    tutorials_never_read = TutorialRead.objects\\\n        .filter(user=user)\\\n        .filter(tutorial__in=tutorialsfollowed)\\\n        .exclude(note=F('tutorial__last_note'))\n\n    posts_unread = []\n\n    for art in articles_never_read:\n        content = art.article.first_unread_reaction()\n        posts_unread.append({'pubdate': content.pubdate,\n                             'author': content.author,\n                             'title': art.article.title,\n                             'url': content.get_absolute_url()})\n\n    for tuto in tutorials_never_read:\n        content = tuto.tutorial.first_unread_note()\n        posts_unread.append({'pubdate': content.pubdate,\n                             'author': content.author,\n                             'title': tuto.tutorial.title,\n                             'url': content.get_absolute_url()})\n\n    for top in topics_never_read:\n        content = top.topic.first_unread_post()\n        if content is None:\n            content = top.topic.last_message\n        posts_unread.append({'pubdate': content.pubdate,\n                             'author': content.author,\n                             'title': top.topic.title,\n                             'url': content.get_absolute_url()})\n\n    posts_unread.sort(cmp=comp)\n\n    return posts_unread\n\n\n@register.filter('interventions_privatetopics')\ndef interventions_privatetopics(user):\n\n    # Raw query because ORM doesn't seems to allow this kind of \"left outer join\" clauses.\n    # Parameters = list with 3x the same ID because SQLite backend doesn't allow map parameters.\n    privatetopics_unread = PrivateTopic.objects.raw(\n        '''\n        select distinct t.*\n        from mp_privatetopic t\n        inner join mp_privatetopic_participants p on p.privatetopic_id = t.id\n        left outer join mp_privatetopicread r on r.user_id = %s and r.privatepost_id = t.last_message_id\n        where (t.author_id = %s or p.user_id = %s)\n          and r.id is null\n        order by t.pubdate desc''',\n        [user.id, user.id, user.id])\n\n    # \"total\" re-do the query, but there is no other way to get the length as __len__ is not available on raw queries.\n    return {'unread': privatetopics_unread, 'total': len(list(privatetopics_unread))}\n\n\n@register.filter(name='alerts_list')\ndef alerts_list(user):\n    total = []\n    alerts = Alert.objects.select_related(\"author\").all().order_by('-pubdate')[:10]\n    for alert in alerts:\n        if alert.scope == Alert.FORUM:\n            post = Post.objects.select_related(\"topic\").get(pk=alert.comment.pk)\n            total.append({'title': post.topic.title,\n                          'url': post.get_absolute_url(),\n                          'pubdate': alert.pubdate,\n                          'author': alert.author,\n                          'text': alert.text})\n        if alert.scope == Alert.ARTICLE:\n            reaction = Reaction.objects.select_related(\"article\").get(pk=alert.comment.pk)\n            total.append({'title': reaction.article.title,\n                          'url': reaction.get_absolute_url(),\n                          'pubdate': alert.pubdate,\n                          'author': alert.author,\n                          'text': alert.text})\n        if alert.scope == Alert.TUTORIAL:\n            note = Note.objects.select_related(\"tutorial\").get(pk=alert.comment.pk)\n            total.append({'title': note.tutorial.title,\n                          'url': note.get_absolute_url(),\n                          'pubdate': alert.pubdate,\n                          'author': alert.author,\n                          'text': alert.text})\n\n    return total\n\n\n@register.filter(name='alerts_count')\ndef alerts_count(user):\n    if user.is_authenticated():\n        return Alert.objects.count()\n    else:\n        return 0\n", "path": "zds/utils/templatetags/interventions.py"}]}