• SQL Queries...

  • We tried to make OpenKM as intuitive as possible, but an advice is always welcome.
We tried to make OpenKM as intuitive as possible, but an advice is always welcome.
Forum rules: Please, before asking something see the documentation wiki or use the search feature of the forum. And remember we don't have a crystal ball or mental readers, so if you post about an issue tell us which OpenKM are you using and also the browser and operating system version. For more info read How to Report Bugs Effectively.
 #44780  by dferguson
 
I would like to use a SQL query to find documents that meet a few criteria.

I can use this query to successfully find all documents in a specific folder.
Code: Select all
select * from OKM_NODE_BASE where NBS_PARENT = '7a583384-8610-41d5-a535-9b8158a821e6';
I would like to additionally filter out documents that have a specific metadata. As an example I would like to additionally filter the above documents to those with metadata of okg:docstate and a value of 001.

Code: Select all
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE property-groups PUBLIC "-//OpenKM//DTD Property Groups 2.0//EN"
                                 "http://www.openkm.com/dtd/property-groups-2.0.dtd">
<property-groups>
  <property-group label="Doc State" name="okg:docstate">
    <select label="Doc State" name="okp:docstate.select" type="simple">
      <option label="Current" value="001" />
      <option label="WIP" value="002" />
      <option label="Legacy" value="003" />
     </select>
  </property-group>
    <property-group label="SubSystem" name="okg:subsystem">
    <select label="SubSystem" name="okp:subsystem.select" type="simple">
      <option label="RIG" value="001" />
      <option label="CPM" value="002" />
      <option label="SW" value="003" />
        <option label="DSC" value="004" />
        <option label="System" value="005" />
        <option label="User" value="006" />
         <option label="CHW" value="007" />
        <option label="Consumables" value="008" />
     </select>
  </property-group>
    <property-group label="Design Phase" name="okg:designphase">
    <select label="Design Phase" name="okp:designphase.select" type="simple">
      <option label="Charter" value="001" />
      <option label="Scoping" value="002" />
      <option label="Verification and Validation" value="003" />
        <option label="Design Transfer" value="004" />
        <option label="Design Output" value="005" />
     </select>
  </property-group>
    <property-group label="Status" name="okg:status">
    <select label="Status" name="okp:status.select" type="simple">
      <option label="Development" value="001" />
        <option label="Review" value="002" />
      <option label="Training" value="003" />
      <option label="Disposition" value="004" />
        <option label="Complete" value="005" />
     </select>
  </property-group>
 </property-groups>
 #44782  by dferguson
 
I had found that document, but struggled to reuse the examples for my specific case. I took some time to figure out that the SQL examples were incorrect for my case. Using lowercase to identify the tables names (okm_node_property) didn't work. I had to capitalize to OKM_NODE_PROPERTY. In the end I ended up with this...
Code: Select all
select NBS_NAME, NPG_GROUP from OKM_NODE_DOCUMENT nd inner join OKM_NODE_BASE nb on nd.NBS_UUID = nb.NBS_UUID inner join OKM_NODE_PROPERTY np on np.NPG_NODE = nd.NBS_UUID where NBS_PARENT = '7a583384-8610-41d5-a535-9b8158a821e6' and NPG_GROUP = 'okg:docstate' and NPG_VALUE = '["001"]';
 #44783  by dferguson
 
Here is output of the NBS_NAME and NPG_GROUP...
Screenshot from 2017-10-19 09-18-53.png
Screenshot from 2017-10-19 09-18-53.png (69.64 KiB) Viewed 2598 times
How can I represent okg:docstate with the appropriate label in the iReport?
 #44785  by dferguson
 
Okay, I figured it out.

I used the text field expression built in iReport
Screenshot from 2017-10-20 11-04-59.png
Screenshot from 2017-10-20 11-04-59.png (38.77 KiB) Viewed 2594 times
 #44788  by jllort
 
Only for your consideration, take in mind direct SQL will output a reports results outside any kind of security filter what might be an issue for you ( it depends on what is your scenario ). Also is possible getting something like it from search API ( java scripting to get results ). I suggest take a look at https://docs.openkm.com/kcenter/view/ok ... -pack.html ( SQLReportPattern.jrxml ) and Search API description https://docs.openkm.com/kcenter/view/ok ... earch.html

About Us

OpenKM is part of the management software. A management software is a program that facilitates the accomplishment of administrative tasks. OpenKM is a document management system that allows you to manage business content and workflow in a more efficient way. Document managers guarantee data protection by establishing information security for business content.