[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

  • It seems like there might be one more option available, however I cannot get it to work for some reason:

    int selEnv = PWWrapper.aaApi_SelectEnvByProjectId(157085);
    int envId = PWWrapper.aaApi_GetEnvId(0);
    
    result = PWSearch.SearchForDocumentsUltimate(
        157085,  // ProjectID 
        true,   // Search sub folders
        null,   // Search string
        false,  // Match whole phrase
        false,  // Any words
        true,  // Search attributes
        null,   // Document name
        null,   // File name
        null,   // Document desc 
        true,  // Originals only
        new SortedList<string, string>(), // Attribute list
        0,      // Latitude min
        0,      // Longitude min
        0,      // Latitude max
        0,      // Longitude max
        false,   // Spatial second pass
        new List<int>() { envId },    // List environments
        new List<int>(),    // List states
        new List<int>(),    // List workflows 
        new List<int>(),    // List storages 
        new List<int>(),    // List applications
        new List<string>(), // List statuses
        new List<int>(),    // List item types 
        new List<int>(),    // List creators
        new List<int>(),    // List updaters
        new List<int>(),    // List checked out users
        0,      // Final status
        null,   // File updated after
        null,   // File updated before
        null,   // Doc updated after
        null,   // Doc updated before
        null,   // Doc created after
        null,   // Doc created before
        null,   // Doc checked out after 
        null,   // Doc checked out before
        populatePath,       // Populate path
        new List<string>() { "RV_REV_1" }, // List columns
        "Test",   // Query name
        0,      // Query parent project id
        0);     // Parent query id
    int count = result.Rows.Count; // Returns 0 results

    Not sure why this isn't working regardless of what I put as the inputs. I have tested other PW search methods and they all function as expected, but don't seem to provide the options to use both a saved query AND specify return columns at the same time. For example this works fine:

    result = PWSearch.SearchForDocumentsWithStatesAndDatesAndReturnColumns(157085, true, null, false, false, true, null, null, null, true, envId, new SortedList<string, string>(), new List<int>(), null, null, null, null, true, new List<string>() { "RV_REV_1" });

    But I cannot use a saved query with it, and it does not return the file update date. If only I could get the "SearchForDocumentsUltimate" working, it seems that should satisfy all criteria!

  • 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