Uploaded image for project: 'eZ Publish / Platform'
  1. eZ Publish / Platform
  2. EZP-18161

sql error in eztagcloud template operator

    XMLWordPrintable

    Details

      Description

      extension/ezwebin/autoloads/eztagcloud.php inline around 126, there's the sql query:

       $rs = $db->arrayQuery( "SELECT ezkeyword.keyword, count(ezkeyword.keyword) AS keyword_count
                                              FROM ezkeyword,
                                                  ezkeyword_attribute_link
                                                  $sqlPermissionChecking[from]
                                              LEFT JOIN ezcontentobject_attribute
                                                  ON ezkeyword_attribute_link.objectattribute_id = ezcontentobject_attribute.id
                                              LEFT JOIN ezcontentobject
                                                  ON ezcontentobject_attribute.contentobject_id = ezcontentobject.id
                                              LEFT JOIN ezcontentobject_tree
                                                  ON ezcontentobject_attribute.contentobject_id = ezcontentobject_tree.contentobject_id
                                              WHERE ezkeyword.id = ezkeyword_attribute_link.keyword_id
                                                  AND ezcontentobject.status = " . eZContentObject::STATUS_PUBLISHED . "
                                                  AND ezcontentobject_attribute.version = ezcontentobject.current_version
                                                  AND ezcontentobject_tree.main_node_id = ezcontentobject_tree.node_id
                                                  $pathString
                                                  $parentNodeIDSQL
                                                  $classIdentifierSQL
                                                  $showInvisibleNodesCond
                                                  $sqlPermissionChecking[where]
                                                  $languageFilter
                                              GROUP BY ezkeyword.id, ezkeyword.keyword
                                              $orderBySql", $dbParams );
      

      But if $sqlPermissionChecking[from] is not empty, then the sql gives an error:

      Unknown column 'ezkeyword_attribute_link.objectattribute_id' in 'on clause'.
      

      It comes from the fact the first left join refers to the very first table in the FROM, a quick workaround is to put into brackets all the tables in the FROM:

       $rs = $db->arrayQuery( "SELECT ezkeyword.keyword, count(ezkeyword.keyword) AS keyword_count
                                              FROM (ezkeyword,
                                                  ezkeyword_attribute_link
                                                  $sqlPermissionChecking[from])
                                              LEFT JOIN ezcontentobject_attribute
                                                  ON ezkeyword_attribute_link.objectattribute_id = ezcontentobject_attribute.id
                                              LEFT JOIN ezcontentobject
                                                  ON ezcontentobject_attribute.contentobject_id = ezcontentobject.id
                                              LEFT JOIN ezcontentobject_tree
                                                  ON ezcontentobject_attribute.contentobject_id = ezcontentobject_tree.contentobject_id
                                              WHERE ezkeyword.id = ezkeyword_attribute_link.keyword_id
                                                  AND ezcontentobject.status = " . eZContentObject::STATUS_PUBLISHED . "
                                                  AND ezcontentobject_attribute.version = ezcontentobject.current_version
                                                  AND ezcontentobject_tree.main_node_id = ezcontentobject_tree.node_id
                                                  $pathString
                                                  $parentNodeIDSQL
                                                  $classIdentifierSQL
                                                  $showInvisibleNodesCond
                                                  $sqlPermissionChecking[where]
                                                  $languageFilter
                                              GROUP BY ezkeyword.id, ezkeyword.keyword
                                              $orderBySql", $dbParams );
      

      But this solution is not perfect in terms of optimization, one better solution (but please check this query by one that understands perfectly well this query) would be to perform all the left join just after the ezkeyword_attribute_link, it gives ($sqlPermissionChecking[from] is just put after all the left join) :

      $rs = $db->arrayQuery( "SELECT ezkeyword.keyword, count(ezkeyword.keyword) AS keyword_count
                                              FROM ezkeyword,
                                                  ezkeyword_attribute_link
                                              LEFT JOIN ezcontentobject_attribute
                                                  ON ezkeyword_attribute_link.objectattribute_id = ezcontentobject_attribute.id
                                              LEFT JOIN ezcontentobject
                                                  ON ezcontentobject_attribute.contentobject_id = ezcontentobject.id
                                              LEFT JOIN ezcontentobject_tree
                                                  ON ezcontentobject_attribute.contentobject_id = ezcontentobject_tree.contentobject_id
                                              $sqlPermissionChecking[from]
                                              WHERE ezkeyword.id = ezkeyword_attribute_link.keyword_id
                                                  AND ezcontentobject.status = " . eZContentObject::STATUS_PUBLISHED . "
                                                  AND ezcontentobject_attribute.version = ezcontentobject.current_version
                                                  AND ezcontentobject_tree.main_node_id = ezcontentobject_tree.node_id
                                                  $pathString
                                                  $parentNodeIDSQL
                                                  $classIdentifierSQL
                                                  $showInvisibleNodesCond
                                                  $sqlPermissionChecking[where]
                                                  $languageFilter
                                              GROUP BY ezkeyword.id, ezkeyword.keyword
                                              $orderBySql", $dbParams );
      

      Those 2 solutions work perfectly for me, l, but second is best, first is just a workaround...

        Attachments

          Activity

            People

            Assignee:
            unknown unknown
            Reporter:
            enzo Jerome Despatis
            Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

              Dates

              Created:
              Updated: