Faceted filtering vs side wide search

It really depends on the context of your case:

  • Is it for a “power user” tool?
  • Is it for single-visit context?
  • Is it a technical website/store (like industry-specific store or something)?

Depending on the answers, you might want to keep your global search as-is, or adjust its behavior.

Narrowing down is normal

When you are looking for something on a website, you often start by visiting around to better understand what the website proposes.
It’s part of the human brain to see global before narrowing down information to reach a more precise goal.

Searching is not different: you search global with some keywords, then you want to adjust regarding the categories and filters the interface/store proposes.

That is for the usual behavior, it might not be the right solution for power-users or really technical domain related website/store.

Advanced filters for power-users

You global search might propose advanced keyword or syntax like Google does.
For instance, when you want result for a specific website, Google allows you to limit your search to a specific domain with this syntax:

site:stackexchange.com faceted filter site wide

You can also use modifier to change the meaning of your research, like:

-site:stackexchange.com "faceted filter" site wide

This one means “not in this site”, and I look for the exact term “faceted filter” into the results.

You could totally imagine keywords for your categories. Let’s take a shoes store example:

brand:martens size:44 color:black winter shoes

Could lead me to pre-filtered search for “winter shoes” of a brand containing “Martens” with a 44 size and black color. Your choice then to allow a bit of fuzziness around the results by providing with 44.5 and 43.5, black and dark blue shoes, for instance.

It doesn’t mean that the global research must only be the only way you can filter things, but it could be a good start to bring a powerful tool for your power-users, and keep the sidebar with filters for beginners, after a first more global search.

Of course, that’s only my opinion and point of view. Best is to analyse your context and ask to users 😊

sql server – Query Cost when filtering with varchar vs nvarchar

Recently the application I’m working on encountered performance issue on a particular table. When I check the queries executing on this table, I noticed a warning about CONVERT_IMPLICIT on the predicate of the query. Apparently the data type of the column is varchar, but the query is passing in nvarchar, hence the warning.

Out of curiosity, I try to see how much difference would passing the correct data type improves query performance by checking the execution plan. I got the following result:

I don't get it

To my surprise, when I’m using the matching data type varchar, the query cost is double compare to using nvarchar, even though it seems like the second query is not using the most efficient execution plan.

So is the relative query cost to be trusted, or is there something I missed?

Filtering List View by Current User Email

You are doing the right thing. Using User ID (email) instead of the weird people field type is what I was doing as well. Many do the same.

Of course you can filter any list by an email in Power Apps. Filtering my email is not different from filtering by any other string-based field. Sample steps:

  1. Add Office 365 Users connector.
  2. Get current user’s email like so
Office365Users.MyProfile().Mail

enter image description here

  1. Now you can filter your SharePoint lists by email:
Filter('My SharePoint list', 'Email'= Office365Users.MyProfile().Mail)

materialized view – What is the correct way to do a computed column with complex logic with performant sorting and filtering at scale in MS SQL Server?

I have the Quantity indexed on both tables because I obviously wish
to sort and filter on this column, but of course the index doesn’t
help if I want to sort and filter on vwThingAugmented.

Based on your view and the comment on filtering I am going to assume queries like this:

SELECT * 
FROM  dbo.vwThingAugmented
WHERE Quantity = 5;

Where there is a filter operator late in the execution plan:

enter image description here

One of the things you could try, depending on the size of the tables and the type of filters you do would be to create a view with a UNION an INNER JOIN and a NOT EXISTS instead of a LEFT JOIN

CREATE VIEW dbo.vwThingAugmented_2
AS SELECT t.ID
, Quantity = t.Quantity + a.Quantity
, AdditionGroupID
FROM dbo.Thing t
INNER JOIN dbo.AdditionGroup a ON  t.AdditionGroupID = a.ID
UNION ALL
SELECT t.ID
, Quantity = t.Quantity
, AdditionGroupID
FROM dbo.Thing t
WHERE NOT EXISTS (SELECT * FROM dbo.AdditionGroup a WHERE  t.AdditionGroupID = a.ID);

You do get two accesses to dbo.Thing and dbo.AdditionGroup in this case, YMMV depending on the type of query you run against the view. You could try running the same query again:

SELECT * 
FROM dbo.vwThingAugmented_2
WHERE Quantity = 5;

This removes the filter operator but you do have double index access with a concatenate.
enter image description here

And you still have residual predicates on both parts of the query,

Because the nonclustered index is not covering:

DROP INDEX IX_Thing_Quantity ON dbo.Thing;
CREATE INDEX IX_Thing_Quantity ON dbo.Thing (Quantity, ID) 
INCLUDE (AdditionGroupID);

Where now the bottom part is improved to use the nonclustered index (without a key lookup) instead of the clustered index :

enter image description here

Lastly, as mentioned by you, you could add an indexed view to the first part of the view. Remember to use NOEXPAND for best performance, even in Enterprise edition.

In IIS Request Filtering, is the case of the string important?

When in the Edit Filtering Rule dialog in IIS, is the case of the string, e.g. cast( important?

Edit Filtering Rule

We’re seeing SQL injection attack attempts that have the string in various combinations of upper- and lower-case, like cAsT(. Do we need to be specific? I would expect that the module doesn’t expect one to think of all the possible combinations but I can’t find it clearly stated as the case in any of the online re

modern experience – Dynamic filtering with Gallery list view in SharePoint Online

I have found a way to implement a substitute for a tab control in SharePoint Online. In SharePoint On Premise I used Hillbilly Tabs, but I am not allowed to use SPFx at our tenant so I had to look for other solutions.

I use two lists, one list contains the items that used to be visible on the tabs and then I use that list to perform dynamic filtering of the other list.
In the view settings for the first list I unchecked "Allow individual item checkboxes", because in a tab control you only want to select one tab at a time.

This setup works pretty well, but the user experience is of course a bit odd. So I was happy to find the "Gallery" list view. I now have the chance to line up the “tabs” horizontally.
But when I switched to Gallery view, the item checkboxes showed up again (even though "Allow individual item checkboxes" was still unchecked in the view settings).

And when I click an item in the list, to filter the other list, no filtering is taking place. Instead I am redirected to another place.

This is how the auto-generated code for the gallery list looks like. Is there anything I can change in the JSON to keep the good behavior (with filtering functioning and no checkboxes) of the basic list I had?

{
  "height": 45,
  "width": 254,
  "hideSelection": false,
  "fillHorizontally": true,
  "formatter": {
    "elmType": "div",
    "attributes": {
      "class": "sp-card-container"
    },
    "children": (
      {
        "elmType": "button",
        "attributes": {
          "class": "sp-card-defaultClickButton"
        },
        "customRowAction": {
          "action": "defaultClick"
        }
      },
      {
        "elmType": "div",
        "attributes": {
          "class": "ms-bgColor-white sp-css-borderColor-neutralLight sp-card-borderHighlight sp-card-subContainer"
        },
        "children": (
          {
            "elmType": "div",
            "attributes": {
              "class": "sp-card-lastTextColumnContainer"
            },
            "children": (
              {
                "elmType": "p",
                "attributes": {
                  "title": "($Title)",
                  "class": "ms-fontColor-neutralPrimary sp-card-content sp-card-highlightedContent"
                },
                "txtContent": "=if (($Title) == '', '–', ($Title))"
              }
            )
          }
        )
      }
    )
  }
}

microsoft excel – Power Query – Append a generated table from a different wsheet to another generated table by filtering through a conditional

I created a power query, where my source data is from division A (wsheet 1) of the company accounts wbook, applied my PQ steps, and generated a table. I will then split that generated table based on 3 departments into 3 different wsheets (using basic excel table filter by dept, cut and paste into other wsheets). However, I want to replicate the same query for another wsheet division B (almost same columns with a few new columns added) from the same source workbook, and generate a report table. Once the report table is generated, I would like to add that table where it is filtered by department 1 (both divisions share one common department) and add that data to the generated report for department 1 of Division A. The report workbook will essentially have 3 PQ generated tables – a wsheet report for Division A Dept2, a wsheet report for Division A Dept 3, and a wsheet report for Division A and B WHERE it is only DEPT1. How would I achieve this so the wsheet report always moves the DEPT1 Division B accounts to the wsheet report of Division A and B WHERE it is only DEPT1 every time I refresh the report wbook, when new entries are added each day from the source wbook?

python – Type hints complaining when dynamically filtering

I’m trying to introduce type hints into an existing codebase, but I’m running into an issue when I attempt to type my query.

from sqlalchemy.orm.query import Query

class DbContext:
    def __init__(self, db_host, db_port, db_name, db_user, db_password):

        engine = create_engine(...)

        session = sessionmaker(bind=engine)
        self.Session: Session = session(bind=engine)

...

def fetch(context: DbContext
   sub_query: Query = context.Session.query(...)

Before I added type hints, filtering dynamically was simply a matter of:

if filters.name is not None:
   sub_query = sub_query.name(
                Person.name.ilike(f"%{filters.name}%"))

However, now with hinting I’m getting this error:

Expression of type “None” cannot be assigned to declared type “Query”

Sure enough, filter appears to return None:

(method) filter: (*criterion: Unknown) -> None

I navigated to the source and at it appears the method does indeed not return anything.

def filter(self, *criterion):
        for criterion in list(criterion):
            criterion = expression._expression_literal_as_text(criterion)

            criterion = self._adapt_clause(criterion, True, True)

            if self._criterion is not None:
                self._criterion = self._criterion & criterion
            else:
                self._criterion = criterion

There’s obviously a disconnect somewhere, as assigning None to sub_query should result in an error which the hint is warning against, but I need to perform the assignment for the filtering to actually work:

# Does NOT work, filtering is not applied
if filters.name is not None:
  sub_query.filter(
               Person.name.ilike(f"%{filters.name}%"))

# Works but Pylance complains
if filters.name is not None:
  sub_query = sub_query.filter(
               Person.name.ilike(f"%{filters.name}%"))

This is my first foray into Python, would love some guidance as to what is going on here!

dropdown – Progressively Filtering Options

A series of dropdowns isn’t necessarily bad. The example you gave with cars is very well-suited to this type of navigation due it its hierarchical structure, and it’s seen on many websites. www.edmunds.com and www.tirerack.com both use this technique.

In both cases they use AJAX to dynamically fill the next dropdown list after the Make is selected, and in the case of TireRack, they expand yet again after the Model is selected to display trim levels. This makes the UI very responsive. Because it’s so common, users accustomed to researching cars will also be familiar with this dropdown navigation method.

However, there is an alternative. Edmunds.com also has a list under “Browse by make” that’s simply full of links. Clicking a link then takes you to a page with a list of cars by that make.

enter image description here

This has a couple advantages, especially for a research site like edmunds.

  1. It’s very quick to glance at that list, find the make you’re looking for, and click it.
  2. This has huge benefits for SEO. I attended an SEO conference hosted by the guys who optimized edmunds.com. One of the things they talked about was the great improvements they made with the link structuring. By dividing the site into landing pages for Ford > Mustang (and so on) they were able to create better long-tail SEO for searches like “Ford Mustang”, “Ford Mustang GT” or “Ford Mustang reviews.” This technique is called siloing.

The disadvantage of course is that you have to wait for the next page to load before choosing the model. So you should of course weigh your options, based on your end goals.