{"action":"created","issue":{"url":"https://api.github.com/repos/zestedesavoir/zds-site/issues/3270","labels_url":"https://api.github.com/repos/zestedesavoir/zds-site/issues/3270/labels{/name}","comments_url":"https://api.github.com/repos/zestedesavoir/zds-site/issues/3270/comments","events_url":"https://api.github.com/repos/zestedesavoir/zds-site/issues/3270/events","html_url":"https://github.com/zestedesavoir/zds-site/issues/3270","id":124677319,"number":3270,"title":"Problèmes de requêtes SQL « cachées » dans les templates","user":{"login":"SpaceFox","id":5786268,"avatar_url":"https://avatars.githubusercontent.com/u/5786268?v=3","gravatar_id":"","url":"https://api.github.com/users/SpaceFox","html_url":"https://github.com/SpaceFox","followers_url":"https://api.github.com/users/SpaceFox/followers","following_url":"https://api.github.com/users/SpaceFox/following{/other_user}","gists_url":"https://api.github.com/users/SpaceFox/gists{/gist_id}","starred_url":"https://api.github.com/users/SpaceFox/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/SpaceFox/subscriptions","organizations_url":"https://api.github.com/users/SpaceFox/orgs","repos_url":"https://api.github.com/users/SpaceFox/repos","events_url":"https://api.github.com/users/SpaceFox/events{/privacy}","received_events_url":"https://api.github.com/users/SpaceFox/received_events","type":"User","site_admin":false},"labels":[{"url":"https://api.github.com/repos/zestedesavoir/zds-site/labels/Back","name":"Back","color":"207de5"},{"url":"https://api.github.com/repos/zestedesavoir/zds-site/labels/Evolution","name":"Evolution","color":"c7def8"}],"state":"open","locked":false,"assignee":null,"milestone":null,"comments":12,"created_at":"2016-01-03T23:18:21Z","updated_at":"2016-01-04T09:49:06Z","closed_at":null,"body":"On a un certain nombre de méthodes sur nos modèles qui provoquent des requêtes SQL quand on les utilise, sans que quoi que ce soit indique au créateur de template que ceci existe.\r\n\r\nSi ça permet en général d'avoir un code à peu près clair, c'est catastrophique pour les performances puisqu'on se retrouve à faire des requêtes dans des boucles sur les pages sans même le savoir. Même si les requêtes elle-mêmes sont légères, le temps de les préparer et de les traiter ne devient plus négligeable.\r\n\r\nOn devait trouver des solutions pour éviter ça.\r\n\r\nExemples :\r\n\r\n**templates/misc/message.part.html** --> **zds/utils/templatetags/profile.py in liked(60)**\r\n`{% if user|liked:message.pk %}voted{% endif %}\"`\r\nProvoque\r\n```sql\r\nSELECT (1) AS `a` FROM `utils_commentlike` WHERE (`utils_commentlike`.`user_id` = 72 AND `utils_commentlike`.`comments_id` = 8611) LIMIT 1\r\n```\r\n\r\n**templates/misc/message.part.html** --> **zds/utils/templatetags/profile.py in disliked(65)**\r\n`{% if user|disliked:message.pk %}voted{% endif %}\"`\r\nProvoque\r\n```sql\r\nSELECT (1) AS `a` FROM `utils_commentdislike` WHERE (`utils_commentdislike`.`user_id` = 72 AND `utils_commentdislike`.`comments_id` = 8611) LIMIT 1\r\n```\r\n\r\n**templates/misc/message.part.html** --> **zds-site/zds/forum/models.py in antispam(416)**\r\n`{% if cite_link and not topic.is_locked and not topic.antispam %}`\r\nProvoque\r\n```sql\r\nSELECT `utils_comment`.`id`, `utils_comment`.`author_id`, `utils_comment`.`editor_id`, `utils_comment`.`ip_address`, `utils_comment`.`position`, `utils_comment`.`text`, `utils_comment`.`text_html`, `utils_comment`.`like`, `utils_comment`.`dislike`, `utils_comment`.`pubdate`, `utils_comment`.`update`, `utils_comment`.`is_visible`, `utils_comment`.`text_hidden`, `forum_post`.`comment_ptr_id`, `forum_post`.`topic_id`, `forum_post`.`is_useful` FROM `forum_post` INNER JOIN `utils_comment` ON ( `forum_post`.`comment_ptr_id` = `utils_comment`.`id` ) WHERE (`forum_post`.`topic_id` = 697 AND `utils_comment`.`author_id` = 72) ORDER BY `utils_comment`.`position` DESC LIMIT 1\r\n```\r\nEt ce sans que rien n'indique que `topic.antispam` fait une requête, contrairement aux précédents.\r\n\r\n… et il y en a plein d'autres. Le pire étant sans doute :\r\n\r\n**templates/forum/base.html** --> **zds-site/zds/forum/models.py in last_read_post(296)**\r\n`{{ topic.last_read_post.get_absolute_url }}`\r\nCette méthode lance en réalité cette requête (ici reformattée) :\r\n```sql\r\nSELECT \r\n    `forum_topicread`.`id`,\r\n    `forum_topicread`.`topic_id`,\r\n    `forum_topicread`.`post_id`,\r\n    `forum_topicread`.`user_id`,\r\n    `forum_topic`.`id`,\r\n    `forum_topic`.`title`,\r\n    `forum_topic`.`subtitle`,\r\n    `forum_topic`.`forum_id`,\r\n    `forum_topic`.`author_id`,\r\n    `forum_topic`.`last_message_id`,\r\n    `forum_topic`.`pubdate`,\r\n    `forum_topic`.`is_solved`,\r\n    `forum_topic`.`is_locked`,\r\n    `forum_topic`.`is_sticky`,\r\n    `forum_topic`.`key`,\r\n    `forum_forum`.`id`,\r\n    `forum_forum`.`title`,\r\n    `forum_forum`.`subtitle`,\r\n    `forum_forum`.`image`,\r\n    `forum_forum`.`category_id`,\r\n    `forum_forum`.`position_in_category`,\r\n    `forum_forum`.`slug`,\r\n    `forum_category`.`id`,\r\n    `forum_category`.`title`,\r\n    `forum_category`.`position`,\r\n    `forum_category`.`slug`,\r\n    T6.`id`,\r\n    T6.`password`,\r\n    T6.`last_login`,\r\n    T6.`is_superuser`,\r\n    T6.`username`,\r\n    T6.`first_name`,\r\n    T6.`last_name`,\r\n    T6.`email`,\r\n    T6.`is_staff`,\r\n    T6.`is_active`,\r\n    T6.`date_joined`,\r\n    `utils_comment`.`id`,\r\n    `utils_comment`.`author_id`,\r\n    `utils_comment`.`editor_id`,\r\n    `utils_comment`.`ip_address`,\r\n    `utils_comment`.`position`,\r\n    `utils_comment`.`text`,\r\n    `utils_comment`.`text_html`,\r\n    `utils_comment`.`like`,\r\n    `utils_comment`.`dislike`,\r\n    `utils_comment`.`pubdate`,\r\n    `utils_comment`.`update`,\r\n    `utils_comment`.`is_visible`,\r\n    `utils_comment`.`text_hidden`,\r\n    `forum_post`.`comment_ptr_id`,\r\n    `forum_post`.`topic_id`,\r\n    `forum_post`.`is_useful`,\r\n    T9.`id`,\r\n    T9.`password`,\r\n    T9.`last_login`,\r\n    T9.`is_superuser`,\r\n    T9.`username`,\r\n    T9.`first_name`,\r\n    T9.`last_name`,\r\n    T9.`email`,\r\n    T9.`is_staff`,\r\n    T9.`is_active`,\r\n    T9.`date_joined`,\r\n    T10.`id`,\r\n    T10.`title`,\r\n    T10.`subtitle`,\r\n    T10.`forum_id`,\r\n    T10.`author_id`,\r\n    T10.`last_message_id`,\r\n    T10.`pubdate`,\r\n    T10.`is_solved`,\r\n    T10.`is_locked`,\r\n    T10.`is_sticky`,\r\n    T10.`key`,\r\n    T11.`id`,\r\n    T11.`title`,\r\n    T11.`subtitle`,\r\n    T11.`image`,\r\n    T11.`category_id`,\r\n    T11.`position_in_category`,\r\n    T11.`slug`,\r\n    T12.`id`,\r\n    T12.`title`,\r\n    T12.`position`,\r\n    T12.`slug`,\r\n    T13.`id`,\r\n    T13.`password`,\r\n    T13.`last_login`,\r\n    T13.`is_superuser`,\r\n    T13.`username`,\r\n    T13.`first_name`,\r\n    T13.`last_name`,\r\n    T13.`email`,\r\n    T13.`is_staff`,\r\n    T13.`is_active`,\r\n    T13.`date_joined`,\r\n    `auth_user`.`id`,\r\n    `auth_user`.`password`,\r\n    `auth_user`.`last_login`,\r\n    `auth_user`.`is_superuser`,\r\n    `auth_user`.`username`,\r\n    `auth_user`.`first_name`,\r\n    `auth_user`.`last_name`,\r\n    `auth_user`.`email`,\r\n    `auth_user`.`is_staff`,\r\n    `auth_user`.`is_active`,\r\n    `auth_user`.`date_joined`\r\nFROM\r\n    `forum_topicread`\r\n        INNER JOIN\r\n    `auth_user` ON (`forum_topicread`.`user_id` = `auth_user`.`id`)\r\n        INNER JOIN\r\n    `forum_topic` ON (`forum_topicread`.`topic_id` = `forum_topic`.`id`)\r\n        INNER JOIN\r\n    `forum_forum` ON (`forum_topic`.`forum_id` = `forum_forum`.`id`)\r\n        INNER JOIN\r\n    `forum_category` ON (`forum_forum`.`category_id` = `forum_category`.`id`)\r\n        INNER JOIN\r\n    `auth_user` T6 ON (`forum_topic`.`author_id` = T6.`id`)\r\n        INNER JOIN\r\n    `forum_post` ON (`forum_topicread`.`post_id` = `forum_post`.`comment_ptr_id`)\r\n        INNER JOIN\r\n    `utils_comment` ON (`forum_post`.`comment_ptr_id` = `utils_comment`.`id`)\r\n        INNER JOIN\r\n    `auth_user` T9 ON (`utils_comment`.`author_id` = T9.`id`)\r\n        INNER JOIN\r\n    `forum_topic` T10 ON (`forum_post`.`topic_id` = T10.`id`)\r\n        INNER JOIN\r\n    `forum_forum` T11 ON (T10.`forum_id` = T11.`id`)\r\n        INNER JOIN\r\n    `forum_category` T12 ON (T11.`category_id` = T12.`id`)\r\n        INNER JOIN\r\n    `auth_user` T13 ON (T10.`author_id` = T13.`id`)\r\nWHERE\r\n    (`forum_topicread`.`user_id` = 72\r\n        AND `forum_topicread`.`topic_id` = 2972)\r\nORDER BY `utils_comment`.`position` DESC\r\nLIMIT 1\r\n```\r\nQui en plus oblige à un « full table scan » de la part de MySQL…\r\n\r\n--------------------\r\n\r\nJe ne sais pas quelles sont les bonnes pratiques Django, mais je suis assez surpris de voir le template taper directement sur le modèle SQL. Ne devrait-on pas fournir au template une représentation déjà intégralement construite ?"},"comment":{"url":"https://api.github.com/repos/zestedesavoir/zds-site/issues/comments/168626470","html_url":"https://github.com/zestedesavoir/zds-site/issues/3270#issuecomment-168626470","issue_url":"https://api.github.com/repos/zestedesavoir/zds-site/issues/3270","id":168626470,"user":{"login":"aesteve","id":1456317,"avatar_url":"https://avatars.githubusercontent.com/u/1456317?v=3","gravatar_id":"","url":"https://api.github.com/users/aesteve","html_url":"https://github.com/aesteve","followers_url":"https://api.github.com/users/aesteve/followers","following_url":"https://api.github.com/users/aesteve/following{/other_user}","gists_url":"https://api.github.com/users/aesteve/gists{/gist_id}","starred_url":"https://api.github.com/users/aesteve/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/aesteve/subscriptions","organizations_url":"https://api.github.com/users/aesteve/orgs","repos_url":"https://api.github.com/users/aesteve/repos","events_url":"https://api.github.com/users/aesteve/events{/privacy}","received_events_url":"https://api.github.com/users/aesteve/received_events","type":"User","site_admin":false},"created_at":"2016-01-04T09:49:06Z","updated_at":"2016-01-04T09:49:06Z","body":"Y'a pas l'équivalent dans Django du `ModelAndView` de SpringMVC ? Ou de la `contextMap` qu'on retrouve dans d'autres frameworks ?\r\n\r\nC'est-à-dire un objet (une simple map/dictionnaire) qui stocke les données dont a besoin le template. Le plus souvent (bis) ces map sont possibles à mettre en cache pour un contexte donné (le contexte peut ou ne peut pas dépendre de l'utilisateur). La complexité est de générer ledit objet côté serveur, mais une fois généré on peut le stocker en cache et surtout on a la garantie que le template ne peut pas \"par inadvertance\" appeler des méthodes coûteuses.\r\n\r\n(ça peut aussi être des objets \"creux\" si besoin i.e. une classe `Person` avec `firstname` `lastname` comme attributs).\r\n\r\nSi jamais ça peut aider / mettre sur une piste."}}