Question

How to mimic a repository search using a SQL query?

How To

Updated March 15, 2019

asked on March 15, 2019

Hello!

As a database admin who is also often asked to perform development work, I am participating in a project whose objective is to identify and report on bottlenecks in a workflow. In its entirety, the workflow is spread out amongst several application platforms, but it originates in Laserfiche.

Being new to all aspects of Laserfiche, I'm looking for some direction as to the best way to proceed with my initial task, which centers on generating the same results from a query against the Laserfiche SQL Server tables as is returned from a UI search against the repository.

Our workflow team lead exports the results of the repository search to an external file which is used to dole out processing assignments to the rest of the workflow team members. The purpose of the SQL query is to store the search results and facilitate keeping track as each document passes through a series of status-change events which take place on the other platforms.

The repository search seems very straightforward. It includes only two fields in its criteria -- Department and Status -- and specifies a single value in each field. The repository search consistently returns about 3,500 entries in its results. Having poked around here in Laserfiche Answers for information on the LF database schema, my best attempt to mimic that search in a SQL query consistently returns almost 6,000 rows.

I am fully aware that I lack a great deal of background knowledge on both Laserfiche and its database schema, so I thought I would include my initial query attempt here and hope that someone can point me in the right direction. Even replies that offer only links to other sources of information, either here or externally, with no accompanying commentary, will be greatly appreciated.

WITH 
Documents AS (
SELECT 
   tocid,
   name
FROM Laserfiche_DB.dbo.toc
)
,
PropertiesByID AS (
SELECT 
   pv.tocid,
   [Name] = pd.prop_name,
   [Value] = pv.short_str_val
FROM Laserfiche_DB..propdef AS pd
INNER JOIN Laserfiche_DB..propval AS pv
   ON pv.prop_id = pd.prop_id
   AND pv.prop_id IN (
   13, 14, 15, 16, 18, 21, 24, 26, 27, 29, 30, 31, 32, 33, 35, 38, 42, 43, 44, 45,
   46, 47, 48, 49, 50, 58, 59, 60, 61, 66, 70, 71, 72, 103, 141, 142, 150, 151, 152, 154,
   155, 156, 157, 158, 159, 168, 171, 173, 194, 243, 244, 264, 270, 271, 273, 275, 276, 280, 281, 287,
   292, 293, 299, 324, 325, 330, 341, 342, 343, 345, 349, 354, 355, 356, 357, 358, 359, 360, 361, 368,
   369, 370, 371, 373, 374, 375, 376, 377, 378, 381, 382, 383, 384, 385, 387, 388, 391, 392, 395, 396, 397)
INNER JOIN Documents AS d
    ON d.tocid = pv.tocid

UNION

SELECT 
   pv.tocid,
   [Name] = pd.prop_name,
   [Value] = CAST(pv.num_val AS varchar)
FROM Laserfiche_DB..propdef AS pd
INNER JOIN Laserfiche_DB..propval AS pv
   ON pv.prop_id = pd.prop_id
   AND pv.prop_id IN (39, 34, 366)
INNER JOIN Documents AS d
    ON d.tocid = pv.tocid
   
UNION

SELECT 
   pv.tocid,
   [Name] = pd.prop_name,
   [Value] = CONVERT(char(23), date_val, 121)
FROM Laserfiche_DB..propdef AS pd
INNER JOIN Laserfiche_DB..propval AS pv
   ON pv.prop_id = pd.prop_id
   AND pv.prop_id IN (20, 68, 69, 265, 340, 362, 363, 364, 379, 380)
INNER JOIN Documents AS d
    ON d.tocid = pv.tocid
)
,
Properties AS (
SELECT * FROM PropertiesByID
PIVOT (MAX([Value])
   [AccountNumber],
   [Alternative],
   [Department],
   [FirstName],
   [FormNumber],
   [LastName],
   [OrigID],
   [ReceiveDate],
   [ScanBy],
   [Status]
   )
  )
AS pvt
)
SELECT * FROM Documents AS d
INNER JOIN Properties AS p
   ON p.tocid = d.tocid
WHERE Department = 'Distributions'
AND Status = 'Pending'

0 0

Replies

replied on March 15, 2019

It's hard to give a precise answer, but:

The results returned by LFS will not contain entries that are in the recycle bin or which the user doesn't have rights to see.
Doesn't your UNION result in each entry appearing more than once?

Is there a reason you don't just use the SDK to query the repository directly?

0 0

replied on March 15, 2019

Just to provide a bit of my own personal experience. Our former VAR created some queries that pulled data from the live repository database, and it eventually caused performance issues.

I would highly recommend not building anything that actively hits the repository database directly if you can find any viable alternatives.

0 0

replied on March 15, 2019 • Show version history

To remove any entries that are in the Recycle bin, you can add the del_tocid to your Documents SELECT statement

Documents AS (
SELECT 
   tocid,
   name,
   del_tocid -- Added column
FROM dbo.toc
)

Then in your final WHERE statement, add the del_tocid condition

WHERE del_tocid = 0 -- Filter out any deleted entries
AND Department = 'Distributions'
AND Status = 'Pending';

NOTE:

Your direct SQL query does not account for security (user access rights) nor is it guaranteed to work after any upgrades as the SQL Schema can and does change.

Suggested Change to your PropertiesByID:

Instead of listing out the FieldIDs, you can do subqueries

For Text and List Fields:

AND pv.prop_id IN (Select prop_id From dbo.propval Where prop_type = 'L' OR prop_type = 'S')

For Date Fields

AND pv.prop_id IN (Select prop_id From dbo.propval Where prop_type = 'D')

For DateTime Fields

AND pv.prop_id IN (Select prop_id From dbo.propval Where prop_type = 'A')

For Number Fields

AND pv.prop_id IN (Select prop_id From dbo.propval Where prop_type = 'N')

0 0

replied on March 15, 2019

Thanks, all, for the timely replies. @████████, we may well end up using the SDK; I am almost entirely new to Laserfiche so I anticipate some degree of fits and starts while I am attempting to come up to speed. @████████, I appreciate the caveat on potential performance issues. @████████, thanks much for the how-to on filtering out recycle bin contents and cleaning up the PropertiesByID CTE.

I'll try to post here regularly as we move forward.

0 0

You are not allowed to follow up in this post.

Question

Question

How to mimic a repository search using a SQL query?

Replies

Sign in to reply to this post.