All questions and answers by user, rendered as HTML

0

Please login or register to vote for this query.

(click on this box to dismiss)

Joomla

Q&A for Joomla! administrators, users, developers and designers

WITH Users_CTE AS
(
  SELECT u.*,
    DisplayNameEncoded = REPLACE(REPLACE(REPLACE(u.DisplayName, '&', '&amp;'), '<', '&lt;'), '>', '&gt;'),
    UserRendered =
      '<div class="user-details">' + CHAR(10) +
        '<a href="https://##SiteDomain?stackoverflow.com##/users/' + CAST(u.Id AS NVARCHAR) + '">' +
          REPLACE(REPLACE(REPLACE(u.DisplayName, '&', '&amp;'), '<', '&lt;'), '>', '&gt;') +
        '</a>' + CHAR(10) +
      '</div>' + CHAR(10)
  FROM Users u
),

Comments_CTE AS
(
  SELECT c.*,
    CommentRendered =
      '<div class="comment">' + CHAR(10) +
        '<div class="comment-score">' + CAST(c.Score AS NVARCHAR) + '</div>' + CHAR(10) +
        '<div class="comment-text">' + CHAR(10) +
          '<span class="comment-copy">' + CHAR(10) +
            CAST(REPLACE(REPLACE(REPLACE(c.Text, '&', '&amp;'), '<', '&lt;'), '>', '&gt;') AS NVARCHAR(MAX)) + CHAR(10) +
          '</span>' + CHAR(10) +
        '</div>' + CHAR(10) +
      '</div>' + CHAR(10)
  FROM Comments c
),

Posts_CTE AS
(
  SELECT p.*,
    TitleEncoded = REPLACE(REPLACE(REPLACE(p.Title, '&', '&amp;'), '<', '&lt;'), '>', '&gt;'),
    Url = 'https://##SiteDomain?stackoverflow.com##/q/' + CAST(p.Id AS NVARCHAR),
    PostRendered =
      '<div class="score">' + CAST(p.Score AS NVARCHAR) + '</div>' + CHAR(10) +
      CASE WHEN p.ViewCount IS NOT NULL 
        THEN '<div class="view-count">' + CAST(p.ViewCount AS NVARCHAR) + '</div>'
        ELSE ''
      END + CHAR(10) +
      '<div class="post">' + CHAR(10) +
        p.Body +
      '</div>' + CHAR(10) +
      u.UserRendered +
      '<div class="comments-list">' + CHAR(10) +
        --(
        --  SELECT STRING_AGG(c.CommentRendered, '') 
        --    WITHIN GROUP (ORDER BY c.CreationDate ASC)
        --  FROM Comments_CTE c
        --  WHERE c.PostId = p.Id
        --) +        
      '</div>' + CHAR(10)      
  FROM Posts p
  JOIN Users_CTE u ON u.Id = p.OwnerUserId
),

Answers_CTE AS
(
  SELECT p.*,
    AnswerRendered = 
      '<div class="answer">' + CHAR(10) +
        p.PostRendered +
      '</div>' + CHAR(10)
  FROM Posts_CTE p
  WHERE p.PostTypeId = 2 -- Answer
),

Questions_CTE AS
(
  SELECT p.*,
    Rendered =
      '<div class="question-and-answers">' + CHAR(10) +
        '<h1 class="question-header">' + CHAR(10) +
          '<a class="question-hyperlink" href="' + p.Url + '">' + CHAR(10) +
            p.TitleEncoded + CHAR(10) +
          '</a>' + CHAR(10) +
        '</h1>' + CHAR(10) +
        '<div class="question">' + CHAR(10) +
          p.PostRendered +
        '</div>' + CHAR(10) +
        (
          SELECT STRING_AGG(a.AnswerRendered, '') 
            WITHIN GROUP (ORDER BY a.Score DESC)
          FROM Answers_CTE a 
          WHERE a.ParentId = p.Id
        ) +
      '</div>' + CHAR(10)
  FROM Posts_CTE p
  WHERE p.PostTypeId = 1 -- Question
)

SELECT 
  '<html>' + CHAR(10) + 
  '<head>' + CHAR(10) + 
    '<style>' + CHAR(10) + 
      '.question-and-answers { border: 1px solid black; padding: 4px; }' + CHAR(10) +
      '.question { border: 1px solid purple; padding: 4px;}' + CHAR(10) + 
      '.answer { border: 1px solid green; padding: 4px; }' + CHAR(10) + 
    '</style>' + CHAR(10) +
  '</head>' + CHAR(10) + 
  '<body>' + CHAR(10) + 
    STRING_AGG(q.Rendered, '') 
      WITHIN GROUP (ORDER BY q.CreationDate ASC) +
  '</body>' + CHAR(10) + 
  '</html>'  
FROM Questions_CTE q
WHERE q.OwnerUserId = ##UserId:int?1149773##

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results