[C# PW API] Execute saved search and return environment attributes for results

Hi,

I am trying to execute a saved search via PW API, and would like to return all attributes from a specific environment along with the results. So far I have the following code:

public static int GetSavedSearchQueryId(string searchName, int parentQueryId, int projectId, bool personal)
{
    int user = personal ? PWWrapper.aaApi_GetCurrentUserId() : 0;
    IntPtr databuffer = PWWrapper.aaApi_SQueryDataBufferSelectSubItems2(parentQueryId, user, projectId);
    int count = PWWrapper.aaApi_DmsDataBufferGetCount(databuffer);
    
    for (int row = 0; row < count; ++row)
    { 
        string queryName = PWWrapper.aaApi_DmsDataBufferGetStringProperty(databuffer, 6, row);
        if(queryName == searchName)
        {
            int queryId = PWWrapper.aaApi_DmsDataBufferGetNumericProperty(databuffer, 1, row);
            return queryId;
        }
    }

    return 0;
}

static void GetPWDocumentsBySavedSearch(string searchName, int projectId, bool personal, bool populatePath)
{
    int queryId = Utils.GetSavedSearchQueryId(searchName, 0, projectId, personal); // This returns the "Query ID" of the saved search
    
    DataTable result;
    if (queryId > 0)
    {
        //Saved search found
        result = PWSearch.SearchForDocumentsByQueryId(queryId, populatePath); //This will return a DataTable with the list of documents found
    }
}

This all works fine, however I am struggling to figure out how I can best add custom attributes into the search results. It seems there are a couple of options available:

  1. Iterate over each row in the result DataTable to manually retrieve attributes (slow, but easy to do!) - EDIT: Not required if you just use SQL queries on the db side to search and join attributes table
  2. Use a different search mechanism such as aaApi_FindDocuments (not sure how to do this, but seems to be the more desirable approach given the search query is executed in a single request)

Not sure if there are any other options that I've missed. Option 2 seems to be a lot more complex in comparison to option 1, given that it requires structs that don't make much sense to me after reading the documentation. It appears as though I need to do something like the following:

// 1. Select Criteria buffer
IntPtr criteriabuffer = PWWrapper.aaApi_SQueryCriDataBufferSelect(queryId);

// 2. Generate list of DocumentRequestColumns (the hard part!)
PWWrapper.DocumentRequestColumns requestColumns = new PWWrapper.DocumentRequestColumns() 
{//No idea what I'm doing here, the documentation is confusing me :D
    properties = { ??? },
    padding = ???,
    columns = { ??? }
};

// 3. Execute search
bool cancel = false;
IntPtr results = IntPtr.Zero;
PWWrapper.aaApi_FindDocumentsToBuffer(criteriabuffer, requestColumns, ref cancel, ref results);

But the reality is I don't know how to generate that struct properly... If anyone has any pointers (pardon the pun) please let me know!
Thanks,

Edward

Parents
  • I have decided just to use the search function PWSearch.SearchForDocumentsWithStatesAndUpdateDatesAndReturnColumns() for now:

                //Get attribute columns
                int envId = PWWrapper.GetEnvironmentId("DOC-01");
                List<string> envColNames = PWWrapper.GetAttributeColumnNamesFromEnvironment(envId).Keys.Cast<string>().ToList();
    
                //Get states
                List<int> states = new List<int>();
                states.Add(PWWrapper.GetStateId("Shared"));
                states.Add(PWWrapper.GetStateId("Published"));
    
                //South (STH) attributes to search
                SortedList<string, string> sthAttSearchValues = new SortedList<string, string>();
                //attSearchValues.Add("DC_TYPE", "MD2");
                //attSearchValues.Add("DC_TYPE", "MD3");
                sthAttSearchValues.Add("DC_CONTRACT", "STH");
                DataTable sthDT = PWSearch.SearchForDocumentsWithStatesAndUpdateDatesAndReturnColumns(91106, true, "", false, false, false, "", "", "", true, envId, sthAttSearchValues, states, null, null, true, envColNames);


    Unfortunately I cannot use a saved query with this method, nor does it return the doc FileUpdateDate, however it is close enough to what I need that it will be acceptable for this specific example.

    It would be great if in future PW releases we saw some additional methods / improvements made to these search functions to overcome the aforementioned limitations; or perhaps some online documentation detailing how to perform these complex searches ourselves using aaApi_FindDocuments(), with examples for building out the query criteria and return columns structs within managed code.

  • Just an update - for those wanting to perform searches outside the scope of what the PWSearch.dll lib provides: You can actually just create SQL Queries to achieve the same, and even store it as a custom view in the db if need be. I have found this approach to be much more flexible and equally performant, however it comes at the cost of some additional complexity when building the select statements if you have many search parameters and need to join multiple tables (e.g. dms_doc, dms_proj, custom_environment).

    Answer Verified By: Edward Ashbolt 

Reply
  • Just an update - for those wanting to perform searches outside the scope of what the PWSearch.dll lib provides: You can actually just create SQL Queries to achieve the same, and even store it as a custom view in the db if need be. I have found this approach to be much more flexible and equally performant, however it comes at the cost of some additional complexity when building the select statements if you have many search parameters and need to join multiple tables (e.g. dms_doc, dms_proj, custom_environment).

    Answer Verified By: Edward Ashbolt 

Children
No Data