a20e4c95a5fde014130e2d04d61ce814cbe5413b galt Mon May 14 15:25:47 2018 -0700 CIRM facetedSearch diff --git src/hg/lib/tablesTables.c src/hg/lib/tablesTables.c index 92dcada..5375c02 100644 --- src/hg/lib/tablesTables.c +++ src/hg/lib/tablesTables.c @@ -1,77 +1,117 @@ /* tablesTables - this module deals with two types of tables SQL tables in a database, * and fieldedTable objects in memory. It has routines to do sortable, filterable web * displays on tables. */ #include "common.h" #include "hash.h" #include "obscure.h" #include "linefile.h" #include "jksql.h" #include "jsHelper.h" #include "sqlSanity.h" #include "fieldedTable.h" #include "cheapcgi.h" +#include "htmshell.h" #include "web.h" #include "cart.h" +#include "facetField.h" #include "tablesTables.h" struct fieldedTable *fieldedTableFromDbQuery(struct sqlConnection *conn, char *query) /* Return fieldedTable from a database query */ { struct sqlResult *sr = sqlGetResult(conn, query); char **fields; int fieldCount = sqlResultFieldArray(sr, &fields); struct fieldedTable *table = fieldedTableNew(query, fields, fieldCount); char **row; int i = 0; while ((row = sqlNextRow(sr)) != NULL) fieldedTableAdd(table, row, fieldCount, ++i); sqlFreeResult(&sr); return table; } +struct fieldedTable *fieldedTableAndCountsFromDbQuery(struct sqlConnection *conn, char *query, int limit, int offset, + char *selectedFields, struct facetField ***pFfArray, int *pResultCount) +/* Return fieldedTable from a database query and also fetch use and select counts */ +{ +struct sqlResult *sr = sqlGetResult(conn, query); +char **fields; +int fieldCount = sqlResultFieldArray(sr, &fields); +struct facetField **ffArray; +AllocArray(ffArray, fieldCount); +struct fieldedTable *table = fieldedTableNew(query, fields, fieldCount); + +struct facetField *ffList = facetFieldsFromSqlTableInit(fields, fieldCount, selectedFields, ffArray); + +char **row; +int i = 0; +int id = 0; +char *nullVal = "n/a"; // TODO what do we want here? +/* Scan through result saving it in list. */ +while ((row = sqlNextRow(sr)) != NULL) + { + if (perRowFacetFields(fieldCount, row, nullVal, ffArray)) + { + if ((i >= offset) && (i < offset+limit)) + fieldedTableAdd(table, row, fieldCount, ++id); + ++i; + } + } +facetFieldsFromSqlTableFinish(ffList, facetValCmpSelectCountDesc); +sqlFreeResult(&sr); +*pFfArray = ffArray; +*pResultCount = i; +return table; +} + static void showTableFilterInstructionsEtc(struct fieldedTable *table, - char *itemPlural, struct fieldedTableSegment *largerContext, void (*addFunc)(void)) + char *itemPlural, struct fieldedTableSegment *largerContext, void (*addFunc)(void), + char *visibleFacetList) /* Print instructional text, and basic summary info on who passes filter, and a submit * button just in case user needs it */ { /* Print info on matching */ int matchCount = slCount(table->rowList); if (largerContext != NULL) // Need to page? matchCount = largerContext->tableSize; cgiMakeButton("submit", "search"); printf("  "); cgiMakeOnClickButton("clearButton", "$(':input').not(':button, :submit, :reset, :hidden, :checkbox, :radio').val('');\n" "$('[name=cdwBrowseFiles_page]').val('1');\n" "$('#submit').click();\n" , "clear search"); printf("
"); printf("%d %s found. ", matchCount, itemPlural); if (addFunc) addFunc(); +if (!visibleFacetList) + { printf("
\n"); printf("You can further filter search results field by field below. "); printf("Wildcard * and ? characters are allowed in text fields. "); printf(">min or <max are allowed in numerical fields.
\n"); } +} static void printSuggestScript(char *id, struct slName *suggestList) /* Print out a little javascript to wrap auto-suggester around control with given ID */ { struct dyString *dy = dyStringNew(256); dyStringPrintf(dy,"$(document).ready(function() {\n"); dyStringPrintf(dy," $('#%s').autocomplete({\n", id); dyStringPrintf(dy," delay: 100,\n"); dyStringPrintf(dy," minLength: 0,\n"); dyStringPrintf(dy," source: ["); char *separator = ""; struct slName *suggest; for (suggest = suggestList; suggest != NULL; suggest = suggest->next) { dyStringPrintf(dy,"%s\"%s\"", separator, suggest->name); @@ -354,70 +394,220 @@ "$('[name=%s_page]').val('%d');\n" "$('#submit').click();\n" , varPrefix, totalPages); } } } } void webFilteredFieldedTable(struct cart *cart, struct fieldedTable *table, char *returnUrl, char *varPrefix, int maxLenField, struct hash *tagOutputWrappers, void *wrapperContext, boolean withFilters, char *itemPlural, int pageSize, struct fieldedTableSegment *largerContext, struct hash *suggestHash, + struct facetField **ffArray, char *visibleFacetList, void (*addFunc)(void)) /* Show a fielded table that can be sorted by clicking on column labels and optionally * that includes a row of filter controls above the labels . * The maxLenField is maximum character length of field before truncation with ... * Pass in 0 for no max */ { if (strchr(returnUrl, '?') == NULL) errAbort("Expecting returnUrl to include ? in showFieldedTable\nIt's %s", returnUrl); -if (withFilters) - showTableFilterInstructionsEtc(table, itemPlural, largerContext, addFunc); +if (withFilters || visibleFacetList) + showTableFilterInstructionsEtc(table, itemPlural, largerContext, addFunc, visibleFacetList); + +printf("
"); // parent container +int facetMargin = 280; +// right column +printf("
\n", visibleFacetList ? facetMargin : 0); /* Set up our table within table look. */ webPrintLinkTableStart(); /* Draw optional filters cells ahead of column labels*/ if (withFilters) showTableFilterControlRow(table, cart, varPrefix, maxLenField, suggestHash); showTableSortingLabelRow(table, cart, varPrefix, returnUrl); showTableDataRows(table, pageSize, maxLenField, tagOutputWrappers, wrapperContext); /* Get rid of table within table look */ webPrintLinkTableEnd(); if (largerContext != NULL) showTablePaging(table, cart, varPrefix, largerContext, pageSize); +printf("
"); + +if (visibleFacetList) // facet desired? + { + // left column + printf("
\n", facetMargin); + + // reset all facet value selections + char *color = "#FFFFCC"; + char *op = "resetAll"; + htmlPrintf("%s
\n", + cartSessionVarName(), cartSessionId(cart), + op, "", "", + color, + "fieldCount; ++f) + { + struct facetField *field = ffArray[f]; + if (slNameInListUseCase(nameList, field->fieldName)) // is this field a visible facet? + { + htmlPrintf("%s
\n", + field->fieldName); // (%d), slCount(field->valList)); + struct facetVal *val; + + if (!field->allSelected) // add reset facet link + { + char *color = "#FFFFCC"; + char *op = "reset"; + htmlPrintf("%s
\n", + cartSessionVarName(), cartSessionId(cart), + op, field->fieldName, "", + color, + "valList; val; val=val->next) + { + boolean specificallySelected = (val->selected && !field->allSelected); + if ((val->selectCount > 0 && (field->showAllValues || valuesShown < FacetFieldLimit)) + || specificallySelected) + { + ++valuesShown; + char *color = "#FFFFCC"; + if (val->selected) + color = "#CCFFFF"; + char *op = "add"; + if (specificallySelected) + op = "remove"; + printf("
\n", + valIndent, (facetMargin-valIndent)); + htmlPrintf( + "" + "", + color, + specificallySelected ? "true" : "false", + specificallySelected ? "checked" : ""); + htmlPrintf("", + cartSessionVarName(), cartSessionId(cart), + op, field->fieldName, val->val, + color); + htmlPrintf("%s(%d)\n", val->val, val->selectCount); + printf("
\n"); + } + } + + // show See More link when facet has lots of values + if (!(field->showAllValues || valuesShown < FacetFieldLimit)) + { + char *color = "#FFFFCC"; + char *op = "showAllValues"; + htmlPrintf("%s
\n", + cartSessionVarName(), cartSessionId(cart), + op, field->fieldName, "", + color, + "See More" + ); + } + + // show See Less link when facet has lots of values + if (field->showAllValues && valuesShown >= FacetFieldLimit) + { + char *color = "#FFFFCC"; + char *op = "showSomeValues"; + htmlPrintf("%s
\n", + cartSessionVarName(), cartSessionId(cart), + op, field->fieldName, "", + color, + "See Fewer" + ); + } + + + } + } + printf("
\n"); + jsInlineF( + "$(function () {\n" + " $('.cdwFSCheckBox').click(function() {\n" + " this.parentElement.nextSibling.click();\n" + " });\n" + "});\n"); + } + +printf("
\n"); + } void webSortableFieldedTable(struct cart *cart, struct fieldedTable *table, char *returnUrl, char *varPrefix, int maxLenField, struct hash *tagOutputWrappers, void *wrapperContext) /* Display all of table including a sortable label row. The tagOutputWrappers * is an optional way to enrich output of specific columns of the table. It is keyed * by column name and has for values functions of type webTableOutputWrapperType. */ { webFilteredFieldedTable(cart, table, returnUrl, varPrefix, maxLenField, tagOutputWrappers, wrapperContext, FALSE, NULL, - slCount(table->rowList), NULL, NULL, NULL); + slCount(table->rowList), NULL, NULL, NULL, NULL, NULL); } void webTableBuildQuery(struct cart *cart, char *from, char *initialWhere, char *varPrefix, char *fields, boolean withFilters, struct dyString **retQuery, struct dyString **retWhere) /* Construct select, from and where clauses in query, keeping an additional copy of where * Returns the SQL query and the SQL where expression as two dyStrings (need to be freed) */ { struct dyString *query = dyStringNew(0); struct dyString *where = dyStringNew(0); struct slName *field, *fieldList = commaSepToSlNames(fields); boolean gotWhere = FALSE; sqlDyStringPrintf(query, "select %-s from %-s", sqlCkIl(fields), sqlCkIl(from)); if (!isEmpty(initialWhere)) @@ -493,60 +683,78 @@ if (orderFields[0] == '-') sqlDyStringPrintf(query, " order by %s desc", orderFields+1); else sqlDyStringPrintf(query, " order by %s", orderFields); } // return query and where expression *retQuery = query; *retWhere = where; } void webFilteredSqlTable(struct cart *cart, struct sqlConnection *conn, char *fields, char *from, char *initialWhere, char *returnUrl, char *varPrefix, int maxFieldWidth, struct hash *tagOutWrappers, void *wrapperContext, - boolean withFilters, char *itemPlural, int pageSize, struct hash *suggestHash, void (*addFunc)(void) ) + boolean withFilters, char *itemPlural, int pageSize, struct hash *suggestHash, char *visibleFacetList, void (*addFunc)(void) ) /* Given a query to the database in conn that is basically a select query broken into * separate clauses, construct and display an HTML table around results. This HTML table has * column names that will sort the table, and optionally (if withFilters is set) * it will also allow field-by-field wildcard queries on a set of controls it draws above * the labels. * Much of the functionality rests on the call to webFilteredFieldedTable. This function * does the work needed to bring in sections of potentially huge results sets into * the fieldedTable. */ { struct dyString *query; struct dyString *where; webTableBuildQuery(cart, from, initialWhere, varPrefix, fields, withFilters, &query, &where); -/* Figure out size of query result */ -struct dyString *countQuery = sqlDyStringCreate("select count(*) from %-s", sqlCkIl(from)); -sqlDyStringPrintf(countQuery, "%-s", where->string); // trust -int resultsSize = sqlQuickNum(conn, countQuery->string); -dyStringFree(&countQuery); +char *selectedFacetValues=cartUsualString(cart, "cdwSelectedFieldValues", ""); + +struct facetField **ffArray = NULL; +struct fieldedTable *table = NULL; char pageVar[64]; safef(pageVar, sizeof(pageVar), "%s_page", varPrefix); int page = 0; -struct fieldedTableSegment context = { .tableSize=resultsSize}; -if (resultsSize > pageSize) - { +struct fieldedTableSegment context; page = cartUsualInt(cart, pageVar, 0) - 1; if (page < 0) page = 0; - int lastPage = (resultsSize-1)/pageSize; +context.tableOffset = page * pageSize; + +if (visibleFacetList) + { + table = fieldedTableAndCountsFromDbQuery(conn, query->string, pageSize, context.tableOffset, selectedFacetValues, &ffArray, &context.tableSize); + } +else + { + /* Figure out size of query result */ + struct dyString *countQuery = sqlDyStringCreate("select count(*) from %-s", sqlCkIl(from)); + sqlDyStringPrintf(countQuery, "%-s", where->string); // trust + context.tableSize = sqlQuickNum(conn, countQuery->string); + dyStringFree(&countQuery); + } + +if (context.tableSize > pageSize) + { + int lastPage = (context.tableSize-1)/pageSize; if (page > lastPage) page = lastPage; context.tableOffset = page * pageSize; + } + +if (!visibleFacetList) + { sqlDyStringPrintf(query, " limit %d offset %d", pageSize, context.tableOffset); + table = fieldedTableFromDbQuery(conn, query->string); } -struct fieldedTable *table = fieldedTableFromDbQuery(conn, query->string); webFilteredFieldedTable(cart, table, returnUrl, varPrefix, maxFieldWidth, - tagOutWrappers, wrapperContext, withFilters, itemPlural, pageSize, &context, suggestHash, addFunc); + tagOutWrappers, wrapperContext, withFilters, itemPlural, pageSize, &context, suggestHash, ffArray, visibleFacetList, addFunc); fieldedTableFree(&table); dyStringFree(&query); dyStringFree(&where); }