Búsqueda de publicaciones de un usuario en todos los sitios

2

Please login or register to vote for this query.

(click on this box to dismiss)

Busca todas las publicaciones de un usuario que contengan un texto específico. (Publicación like [Like] OR Publicación like [OrLike]) AND Publicación like [AndLike2] AND Publicación like [AndLike3]

Stack Overflow en español

Preguntas y respuestas para programadores y profesionales de la informática

-- AccountId: AccountId "Id de perfil de red del usuario (obtenida en Perfil > Perfil de red)"
-- Like: Like "Término de búsqueda. Usar % para coincidir con cualquier caracter."
-- OrLike: Or Like "Término de búsqueda alternativo. Usar -1 para ignorar. Usar % para coincidir con cualquier caracter."
-- AndLike2: And Like "Otro término de búsqueda obligatorio. Usar -1 para ignorar. Usar % para coincidir con cualquier caracter."
-- AndLike3: And Like "Otro término de búsqueda obligatorio. Usar -1 para ignorar. Usar % para coincidir con cualquier caracter."

DECLARE @accountid AS nvarchar(12) = cast(##AccountId:int## as nvarchar)
DECLARE @searchtext AS nvarchar(max) = ##Like:string?%palabra%##
DECLARE @searchtext2 AS nvarchar(max) = ##OrLike:string?-1##
DECLARE @searchtextand2 AS nvarchar(max) = ##AndLike2:string?-1##
DECLARE @searchtextand3 AS nvarchar(max) = ##AndLike3:string?-1##

-- Forked from: http://data.stackexchange.com/es/query/355774/cross-site-query-template
-- Create cursor for list of sites
DECLARE sites CURSOR FOR
  SELECT name
  FROM sys.databases
  WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb', 'Data.StackExchange')
-- And declare some variables
DECLARE @sitedbname AS nvarchar(max)
DECLARE @sitehostname AS nvarchar(max)
DECLARE @ispersitemeta AS bit

DECLARE @query AS nvarchar(max)
CREATE TABLE #out (
  Sitio nvarchar(max) NOT NULL,
  -- ↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓
  -- COLUMN NAMES WANT TO ADD SHOULD GO HERE   
  Puntaje int,
  [Título] nvarchar(max),
  Fecha date
  -- ↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑
)
-- These variables are for the SPOOKY HOSTNAME GENERATION CODE
DECLARE @spooky_string AS nvarchar(max)
DECLARE @spooky_delimiter AS char(1) = '.'
DECLARE @spooky_xml AS xml
DECLARE @spooky_result AS nvarchar(max)

-- Step through cursor
OPEN sites
FETCH NEXT FROM sites INTO @sitedbname
WHILE @@FETCH_STATUS = 0
BEGIN
  -----------------------------------------------------------------------------
  -- BEGIN SPOOKY HOSTNAME GENERATION CODE ------------------------------------
  -- adapted from <http://data.stackexchange.com/stackoverflow/query/256747/> -
  -----------------------------------------------------------------------------
  SET @spooky_string = @sitedbname
  SET @spooky_xml = CAST(('<X>' + REPLACE(@spooky_string, @spooky_delimiter, '</X><X>') + '</X>') AS xml)
  SET @spooky_result = ''

  SELECT
    C.value('.', 'nvarchar(max)') AS [Piece],
    C.value('for $i in . return count(../*[. << $i]) + 1', 'int') AS [Index]
  INTO #spooky_pieces
  FROM @spooky_xml.nodes('X') AS X(C)

  SELECT @spooky_result = COALESCE(@spooky_result + '.', '') + [Piece]
  FROM #spooky_pieces
  ORDER BY [Index] DESC

  DROP TABLE #spooky_pieces

  SET @sitehostname = 'http://' + RIGHT(@spooky_result, LEN(@spooky_result)-1) + '.com'
  SET @ispersitemeta = (CASE WHEN @sitedbname LIKE '%Meta%' AND @sitedbname != 'StackExchange.Meta' THEN 1 ELSE 0 END)

  ----------------------------------------
  -- HERE COME THE SPOOKY SPECIAL CASES --
  ----------------------------------------
  -- Meta MathOverflow doesn't have a redirect; see <http://meta.stackexchange.com/q/215071/224428>
  IF @sitedbname = 'StackExchange.Mathoverflow.Meta' SET @sitehostname = 'http://Meta.MathOverflow.net'
  -- For some reason probably involving the AVP/Audio/Video/Sound hullabaloo, there is 
  -- still a StackExchange.Audio DB that's getting updated. http://audio.stackexchange.com/
  -- no longer exists, so we use Video.SE for the hostname instead.
  IF @sitedbname = 'StackExchange.Audio' SET @sitehostname = 'http://Video.StackExchange.com'
  -----------------------------------------------------------------------------
  -- END SPOOKY HOSTNAME GENERATION CODE --------------------------------------
  -----------------------------------------------------------------------------

  -- ↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓
  -- CODE YOU WANT TO ADD SHOULD GO HERE
  -- for example,
  SET @query = '
    USE [' + @sitedbname + ']

    INSERT INTO #out
    SELECT
           ''' + @sitehostname + '|' + @sitedbname + ''' [Sitio],
 
            p.Score Puntaje,
            ''' + @sitehostname + '/q/'' + cast(p.Id as nvarchar) + ''|'' 
              + case when p.PostTypeId = 1 then
                     p.Title 
                else (select Title from Posts where Id = p.ParentId)
                 end [Título],
            p.CreationDate Fecha
       from Posts p
       left join Users u
         on p.OwnerUserId = u.Id
      where u.AccountId = ' + @accountid + '
        and p.PostTypeId <= 2
        and (
              (
                p.Body like ''' + @searchtext + ''' or p.Title like ''' + @searchtext + '''
                or  (''' + @searchtext2 + ''' <> ''-1'' and (p.Body like ''' + @searchtext2 + ''' or p.Title like ''' + @searchtext2 + '''))
              )
              and (''' + @searchtextand2 + ''' = ''-1'' or p.Body like ''' + @searchtextand2 + ''' or p.Title like ''' + @searchtextand2 + ''')
              and (''' + @searchtextand3 + ''' = ''-1'' or p.Body like ''' + @searchtextand3 + ''' or p.Title like ''' + @searchtextand3 + ''')
            ) 
    '
  EXEC sp_executesql @query
  -- ↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑

  FETCH NEXT FROM sites INTO @sitedbname
END
CLOSE sites
DEALLOCATE sites

-- Reap results (also optional)
SELECT
  *
FROM
  #out
  
  
ORDER BY Fecha desc

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results