DNN Forums

Ask questions about your website to get help learning DNN and help resolve issues.
Prev
Next

Help with SQL Query

Forums > SQL and SQL Server
Sort:
You are not authorized to post a reply.





amurraygbhu

Growing Member



Posts:28
Growing Member




8/9/2022 7:39 AM

    I am using DNN Platform and I need help with an SQL query I am trying to run using the DNN SQL Console.

    I need to change a small block of code that is used in several locations throughout the website. So I am trying to write an SQL query that searches the htmltext table for modules that still contain the block of code in the content field as I have changes it in a few locations already. Here is the query

    Select moduleid from htmltext where content like 'codeblock'

    The problem is that the table htmltext keeps the historical versions of the module. So if I already changed it in module 600 and module 600 has 5 total versions (versions 1-5) it will show module 600 4 times (version 1-4). None of these are the current version (version 5) of the module though.

    So I need to change the query to only search the latest version of the modules in the htmltext table.

    Anyone know how to change the query to accomplish this?






    James Clarkson

    Growing Member



    Posts:72
    Growing Member




    8/9/2022 7:45 AM
      try GROUP BY to group by the module id and then HAVING version=MAX(version)
      or something along those lines - I would check this for you if I had more time just now - hopefully this gets you on the right lines





      Sebastian Leupold

      Veteran Member



      Posts:539
      Veteran Member




      8/9/2022 7:47 AM

        Select T.moduleID
        FROM HTMLText T
        JOIN (SELECT ModuleID, max(versionID) as latestVersion FROM HTMLText GROUP BY ModuleID) AS S ON T.ModuleID = S.ModuleID AND T.Version = S.Version
        WHERE T.content like 'codeblock'






        Michael Tobisch

        Veteran Member



        Posts:1124
        Veteran Member




        8/9/2022 9:27 AM

          In addition to Sebastian (and correcting one or two typos in his script): If you use the SQL Console in DNN, you might need the "DNN-SQL-Syntax", which is then:

          SELECT T.ModuleID
          FROM {databaseOwner}{objectQualifier}HtmlText T
          INNER JOIN (SELECT ModuleID, MAX([Version]) as LatestVersion FROM {databaseOwner}{objectQualifier}HtmlText GROUP BY ModuleID) AS S ON T.ModuleID = S.ModuleID AND T.Version = S.LatestVersion
          WHERE T.Content LIKE '%codeblock%'

          Please keep in mind that the content is stored HTML-encoded - so replace < by &lt;, > by &gt;, " by &quot; etc.

          Happy DNNing!
          Michael

           

          Michael Tobisch
          DNN★MVP

          dnnWerk Austria
          DNN Connect





          amurraygbhu

          Growing Member



          Posts:28
          Growing Member




          8/9/2022 9:41 AM

            Great thanks for the help. Worked great.






            amurraygbhu

            Growing Member



            Posts:28
            Growing Member




            5/5/2023 7:33 AM
              I was able to get the query I needed with the help provided above. Works great now.
              You are not authorized to post a reply.

              These Forums are dedicated to the discussion of DNN Platform.

              For the benefit of the community and to protect the integrity of the ecosystem, please observe the following posting guidelines:

              1. If you have (suspected) security issues, please DO NOT post them in the forums but instead follow the official DNN security policy. 
              2. No Advertising. This includes the promotion of commercial and non-commercial products or services which are not directly related to DNN.
              3. No vendor trolling / poaching. If someone posts about a vendor issue, allow the vendor or other customers to respond. Any post that looks like trolling / poaching will be removed.
              4. Discussion or promotion of DNN Platform product releases under a different brand name are strictly prohibited.
              5. No Flaming or Trolling.
              6. No Profanity, Racism, or Prejudice.
              7. Site Moderators have the final word on approving / removing a thread or post or comment.
              8. English language posting only, please.

              Would you like to help us?

              Awesome! Simply post in the forums using the link below and we'll get you started.

              Get Involved