Question

search syntax to mssql

Forms

Updated October 14, 2020

asked on October 8, 2020

Hi all,

This is my search syntax from Laserfiche Windows Client.

{LF:Name="*", Type="D"} & {LF:LOOKIN="VARKIT\Utilisateurs\BACCINO Olivier", SUBFOLDERS=0}

The query returns me some files.

Is it possible to "translate" this syntax to MSSQL syntax and retrieve the metadata in my forms ?

Thanks in advance.

Regards

0 0

Answer

SELECTED ANSWER

replied on October 13, 2020

To get the Metadata with Field Names, you need to join the dbo.propval and the dbo.propdef tables

-- Do not edit the DECLARE statements
DECLARE @SQLCommand varchar(max)
DECLARE @Repository varchar(100)
DECLARE @LFSource varchar(255)

-- Edit the @LFSource below
-- Set path starting with repository name
SET @LFSource = 'VARKIT\Utilisateurs\BACCINO Olivier'

-- Do not edit anything below
-- 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], [EntryList].[tocid] AS [LFEntryID], [name] AS [Name], [pd].[prop_name] AS [FieldName], [pd].[prop_type] AS [FieldType], [pv].[str_val], [pv].[num_val], [pv].[date_val] FROM
  (Select * FROM
  ( SELECT h.[LFPath], h.[tocid] As [Ftocid]
   FROM h
   JOIN [dbo].[toc] ON  h.[tocid] = [dbo].[toc].[tocid]
   WHERE [LFPath] =  ''' + @LFSource + ''' ) AS FList
   INNER JOIN 
   [dbo].[toc] AS DList
    ON FList.[Ftocid] = DList.[parentid]) AS EntryList
   INNER JOIN
   [dbo].[propval] AS [pv]
   ON [EntryList].[tocid] = [pv].[tocid]
   INNER JOIN
   [dbo].[propdef] AS [pd]
   ON [pd].[prop_id] = [pv].[prop_id]
-- Documents only
WHERE EntryList.[etype] = -2;'

EXEC(@SQLCommand);

1 0

Replies

replied on October 9, 2020

There's no simple utility to drop in some LF search syntax and have it spit out valid T-SQL statements that pull the same things. I've implemented some solutions to retrieve information directly from the DBs in the past but it involves a decent understanding of T-SQL (joins, pivot tables, diving deep into the tables to find out how they relate, etc).

A better approach might be running that search, then doing a select all on your forms, and selecting File > Download > Download Report and selecting all the fields for your metadata. Would that work for your needs?

0 0

replied on October 9, 2020 • Show version history

EDIT -- I did not at first realize you had asked for the metadata to be returned... my query will not return any metadata.

I don't know that this is the most efficient query, but here is a query that does what you ask for.

-- Do not edit the DECLARE statements
DECLARE @SQLCommand varchar(max)
DECLARE @Repository varchar(100)
DECLARE @LFSource varchar(255)

-- Edit the @LFSource below
-- Set path starting with repository name
SET @LFSource = 'VARKIT\Utilisateurs\BACCINO Olivier'

-- Do not edit anything below
-- 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], [tocid] AS [LFEntryID], [name] AS [Name] FROM
  (Select * FROM
  ( SELECT h.[LFPath], h.[tocid] As [Ftocid]
   FROM h
   JOIN [dbo].[toc] ON  h.[tocid] = [dbo].[toc].[tocid]
   WHERE [LFPath] = ''' + @LFSource + ''') AS FList
   INNER JOIN 
   [dbo].[toc] AS DList
    ON FList.[Ftocid] = DList.[parentid]) AS EntryList
-- Documents only
WHERE EntryList.[etype] = -2;'

EXEC(@SQLCommand);

0 0

replied on October 12, 2020

Hi all, nice to see you and thanks for your help.

@Bert, I tried your request and got an error.

Msg 208, Level 16, State 1, Line 1
Invalid object name 'dbo.toc'.

(1 row(s) affected)

What is the database "toc"?

0 0

View 3 previous replies

replied on October 12, 2020

when you run the query, are you running it on the LF Repository DB?

0 0

replied on October 13, 2020

No I'm running it from another DB

0 0

replied on October 13, 2020

dbo.toc is a table in the repository DB that has data on every entry in the repository.

1 0

replied on October 13, 2020

Hi Bert.

Thanks for the tip.

I found dbo.toc but didn't see the metadata in the table. Can you give me the metadata base's name please?

0 0

SELECTED ANSWER

replied on October 13, 2020

To get the Metadata with Field Names, you need to join the dbo.propval and the dbo.propdef tables

-- Do not edit the DECLARE statements
DECLARE @SQLCommand varchar(max)
DECLARE @Repository varchar(100)
DECLARE @LFSource varchar(255)

-- Edit the @LFSource below
-- Set path starting with repository name
SET @LFSource = 'VARKIT\Utilisateurs\BACCINO Olivier'

-- Do not edit anything below
-- 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], [EntryList].[tocid] AS [LFEntryID], [name] AS [Name], [pd].[prop_name] AS [FieldName], [pd].[prop_type] AS [FieldType], [pv].[str_val], [pv].[num_val], [pv].[date_val] FROM
  (Select * FROM
  ( SELECT h.[LFPath], h.[tocid] As [Ftocid]
   FROM h
   JOIN [dbo].[toc] ON  h.[tocid] = [dbo].[toc].[tocid]
   WHERE [LFPath] =  ''' + @LFSource + ''' ) AS FList
   INNER JOIN 
   [dbo].[toc] AS DList
    ON FList.[Ftocid] = DList.[parentid]) AS EntryList
   INNER JOIN
   [dbo].[propval] AS [pv]
   ON [EntryList].[tocid] = [pv].[tocid]
   INNER JOIN
   [dbo].[propdef] AS [pd]
   ON [pd].[prop_id] = [pv].[prop_id]
-- Documents only
WHERE EntryList.[etype] = -2;'

EXEC(@SQLCommand);

1 0

replied on October 14, 2020

Nice Bert!

It's working!

You are a BOSS ^^

0 0

You are not allowed to follow up in this post.

Question

Question

search syntax to mssql

Answer

Replies

Sign in to reply to this post.