Migrate documents from relational database

[Laurentiu_VLASA]

Hello Nuxeo Gurus,

I have a question related to document structure configuration: I have a use case where I have to import documents from a relational database to Nuxeo. In my documents I have fields that are foreign keys from related tables (ex: DOCS.CUSTOMER_ID pointing to CUSTOMERS table). The documents table from the database will be abandoned and the documents will only be stored in Nuxeo, but all the other related tables will be accesible from the DB. I want to be able to search documents only in Nuxeo (using Nuxeo API) by customer names for ex. (exact match, starting with, containing etc.) so I have to create the correct document structure in Nuxeo. For this,I imagined 2 ways :

1. denormalize the structure of Nuxeo docs and add a customer name string field - I don't like this for obvoius reasons (customers can change)
2. create a vocabulary in Nuxeo based on a database table (via jdbc data source) and configure a customer field in Nuxeo documents based on this vocabulary. The problem with this approach is that I haven't foand a way to search documents in Nuxeo by customer name. For ex. I'm looking for a way to write an NXQL query with a "join" beetwen Nuxeo documents and the "customers" directory. Is this possible?

Which way would you choose? Or do you have another suggestion? Thank you.

[Gregory_Carlin]

Hello,

You should go with Nuxeo SQL Directories, using our CRUD API, as described in https://doc.nuxeo.com/nxdoc/data-lists-and-directories/#sql-directories

Regards

[Laurentiu_VLASA]

Hello Gregory,

Thank you for your answer. Nuxeo SQL Directories looks like a good choice for me too. The link you provided contains details about the configuration. The application we are currently building allows the users to manage their documents in complex ways so one of the requirements is to create a very complicated research functionality. Our idea was to build complex NXQL queries that will give the list of documents that satisfy the research criteria. How can I construct the NXQL query that retrieves all the documents where customer name contains a search string - for example (knowing that the customer field in the document contains the id of the customer and the name of the customer is only in the Nuxeo directory)? Is there a "join" like functionality between documents and directories? Do you know another way to obtain this? Can you give me a working exemple? Maybe I'm missing something as I am just starting to work with Nuxeo. Thank you again for your time and patience 🙂