Question

Path filter in SQL

Administration How To

Updated February 19, 2019

asked on February 18, 2019 • Show version history

So I know that the path of documents is not stored in SQL, only the parent folder but wondering if anyone with more SQL knowledge than I can assist.

We have a conversion that we need to modify and update. Using Workflow, this is almost impossible do to how slow it is. The search repository activity simply never finishes or crashes. The search in the client takes 4 minutes to complete, not sure why Workflow never finishes (though the workflow search always takes 10 times as long as the client).

What i'd like to do in SQL is write a query to get the field values and docids I want instead. The documents I want have a specific field and are in a certain folder.

Essentially it would be something like the following:

SELECT tocid,str_val

WHERE prop_id = 155 AND path like '%ROOT FOLDER\TEST FOLDER%'

The documents could be in any number of subfolders under TEST FOLDER

However, you only have the parent ID (will need a join on toc table) so you somehow have to recursively go through them to build the path. Or somehow go recursively up to the TEST FOLDER document ID. If we don't have the path we have millions of extra documents.

Anyone know how in SQL you could query something like this?

Thanks much,

Chris

0 0

Answer

SELECTED ANSWER

replied on February 19, 2019 • Show version history

You can use

 ;WITH h AS(
    -- top level rows, no parent detected
    SELECT CAST([name] AS varchar(max)) AS [LFPath], *
    FROM [dbo].[toc] t1
    WHERE NOT EXISTS( SELECT 1 FROM [dbo].[toc] t2 WHERE t1.[parentid] = t2.[tocid])
    --
    UNION All
    SELECT CAST([LFPath] + '\' + t.[name] AS varchar(max))AS [LFPath], t.*
    FROM h
    JOIN [dbo].[toc] t ON h.[tocid] = t.[parentid]
)
SELECT * FROM
 ( SELECT h.[LFPath], [dbo].[toc].*
   FROM h
   JOIN [dbo].[toc] ON  h.[tocid] = [dbo].[toc].[tocid]
   WHERE [LFPath] LIKE '%ROOT FOLDER\TEST FOLDER\%') AS DList
-- Documents only
WHERE DList.[etype] = -2;

0 0

Replies

replied on February 19, 2019 • Show version history

So the original post wanted the Field values and Entry IDs for documents within a path starting with...

My original code just got a listing of the documents within the starting path...

Also if you want to run this as a custom query through a workflow we can create the Query like this:

DECLARE @SQLCommand varchar(max)
DECLARE @Repository varchar(100)
DECLARE @FieldName varchar(100)
DECLARE @LFSource varchar(255)
-- Set path starting with repository name
SET @LFSource = ? -- 'Demo\Bert'
-- Set Field Name to search for
SET @FieldName = ? -- 'Rev.'
-- Do not edit, Get Repository from LFSource Path
SET @Repository = SUBSTRING(@LFSource, 0, CHARINDEX('\', @LFSource))
SET @SQLCommand = ';WITH h AS(
    -- top level rows, no parent detected
    SELECT [LFPath] =
	CASE [name]
	WHEN ''ROOT FOLDER'' THEN CAST(''' + @Repository + ''' AS varchar(max))
	ELSE CAST([name] AS varchar(max))
	END, * 
    FROM [dbo].[toc] t1
    WHERE NOT EXISTS( SELECT 1 FROM [dbo].[toc] t2 WHERE t1.[parentid] = t2.[tocid])
    --
    UNION All
    SELECT CAST([LFPath] + ''\'' + t.[name] AS varchar(max))AS [LFPath], t.*
    FROM h
    JOIN [dbo].[toc] t ON h.[tocid] = t.[parentid]
)
SELECT [LFPath], [str_val], [date_val], [num_val], [bin_val], [tocid] FROM
 ( SELECT h.[LFPath], pv.[str_val], pv.[date_val], pv.[num_val], pv.[bin_val], [dbo].[toc].*
   FROM h
   JOIN [dbo].[toc] ON  h.[tocid] = [dbo].[toc].[tocid]
   JOIN [dbo].[propval] pv ON h.[tocid] = pv.[tocid]
   JOIN [OPG3Demo].[dbo].[propdef] pd ON pv.[prop_id] = pd.[prop_id]
   WHERE pd.[prop_name] = ''' + @FieldName + ''' AND [LFPath] LIKE ''%' + @LFSource + '%'') AS EntryList
-- Documents only
WHERE EntryList.[etype] = -2;'

EXEC(@SQLCommand)

Note: multivalue fields will return 1 row per field value, so if the field has 3 values on a given document, your results will have 3 rows for the same Entry ID.

0 0

replied on February 19, 2019

Thanks Bert, made some minor tweaks but this seems to return the appropriate amount.

Running and Search Repository acitivity and then an insert data into a temp table with workflow through a General Database Error after about 1.5 hours. Looks like we have to remain in SQL.

0 0

You are not allowed to follow up in this post.

Question

Question

Path filter in SQL

Answer

Replies

Sign in to reply to this post.