将用户筛选的查询参数存储在数据库表中的最佳方法是什么？

I have an ASP.NET MVC website. In my backend I have a table called People with the following columns:

我有一个ASP.NET MVC网站。在我的后端,我有一个名为People的表,其中包含以下列:

ID
Name
Age
Location
... (a number of other cols)

......(其他一些小组)

I have a generic web page that uses model binding to query this data. Here is my controller action:

我有一个通用的网页,使用模型绑定来查询这些数据。这是我的控制器动作:

public ActionResult GetData(FilterParams filterParams)
{
      return View(_dataAccess.Retrieve(filterParams.Name, filterParams.Age, filterParams.location, . . .)
}

which maps onto something like this:

映射到这样的东西:

 http://www.mysite.com/MyController/GetData?Name=Bill .. .

The dataAccess layer simply checks each parameter to see if its populated to add to the db where clause. This works great.

dataAccess层只是检查每个参数以查看它是否已填充以添加到db where子句。这很好用。

I now want to be able to store a user's filtered queries and I am trying to figure out the best way to store a specific filter. As some of the filters only have one param in the queryString while others have 10+ fields in the filter I can't figure out the most elegant way to storing this query "filter info" into my database.

我现在希望能够存储用户的过滤查询,我试图找出存储特定过滤器的最佳方法。由于某些过滤器在queryString中只有一个参数,而其他过滤器在过滤器中有10个以上的字段,因此我无法找到将此查询“过滤信息”存储到我的数据库中的最优雅方式。

Options I can think of are:

我能想到的选择是:

Have a complete replicate of the table (with some extra cols) but call it PeopleFilterQueries and populate in each record a FilterName and put the value of the filter in each of field (Name, etc)

有一个完整的表复制(有一些额外的cols),但称之为PeopleFilterQueries并在每个记录中填充一个FilterName并将过滤器的值放在每个字段中(名称等)
Store a table with just FilterName and a string where I store the actual querystring Name=Bill&Location=NewYork. This way I won't have to keep adding new columns if the filters change or grow.

存储一个只有FilterName的表和一个字符串,我存储实际的查询字符串Name = Bill&Location = NewYork。这样,如果过滤器更改或增长,我将不必继续添加新列。

What is the best practice for this situation?

这种情况的最佳做法是什么?

7 个解决方案

#1

If the purpose is to save a list of recently used filters, I would serialise the complete FilterParams object into an XML field/column after the model binding has occurred. By saving it into a XML field you're also giving yourself the flexibility to use XQuery and DML should the need arise at a later date for more performance focused querying of the information.

如果目的是保存最近使用的过滤器列表,我会在模型绑定发生后将完整的FilterParams对象序列化为XML字段/列。通过将其保存到XML字段中,您还可以灵活地使用XQuery和DML,以便日后能够以更高性能为重点查询信息。

    public ActionResult GetData(FilterParams filterParams)
    {
          // Peform action to get the information from your data access layer here
          var someData = _dataAccess.Retrieve(filterParams.Name, filterParams.Age, filterParams.location, . . .);

          // Save the search that was used to retrieve later here
          _dataAccess.SaveFilter(filterParams);
          return View(someData);
    }

And then in your DataAccess Class you'll want to have two Methods, one for saving and one for retrieving the filters:

然后在您的DataAccess类中,您将需要两个方法,一个用于保存,另一个用于检索过滤器:

public void SaveFilter(FilterParams filterParams){
    var ser = new System.Xml.Serialization.XmlSerializer(typeof(FilterParams));
    using (var stream = new StringWriter())
           {
              // serialise to the stream
              ser.Serialize(stream, filterParams);
           }
  //Add new database entry here, with a serialised string created from the FilterParams obj
  someDBClass.SaveFilterToDB(stream.ToString());
}

Then when you want to retrieve a saved filter, perhaps by Id:

然后,当您想要检索已保存的过滤器时,可能由Id:

public FilterParams GetFilter(int filterId){

      //Get the XML blob from your database as a string
      string filter = someDBClass.GetFilterAsString(filterId);

      var ser = new System.Xml.Serialization.XmlSerializer(typeof(FilterParams));

      using (var sr = new StringReader(filterParams))
      {
          return (FilterParams)ser.Deserialize(sr);
      }
}

Remember that your FilterParams class must have a default (i.e. parameterless) constructor, and you can use the [XmlIgnore] attribute to prevent properties from being serialised into the database should you wish.

请记住,您的FilterParams类必须具有默认(即无参数)构造函数,并且您可以使用[XmlIgnore]属性来阻止属性根据需要序列化到数据库中。

public class FilterParams{
   public string Name {get;set;}
   public string Age {get;set;}

   [XmlIgnore]
   public string PropertyYouDontWantToSerialise {get;set;}
}

Note: The SaveFilter returns Void and there is no error handling for brevity.

注意:SaveFilter返回Void,为简洁起见,没有错误处理。

#2

Rather than storing the querystring, I would serialize the FilterParams object as JSON/XML and store the result in your database.

我不是存储查询字符串,而是将FilterParams对象序列化为JSON / XML并将结果存储在数据库中。

Here's a JSON Serializer I regularly use:

这是我经常使用的JSON Serializer:

using System.IO;
using System.Runtime.Serialization.Json;
using System.Text;

namespace Fabrik.Abstractions.Serialization
{
    public class JsonSerializer : ISerializer<string>
    {
        public string Serialize<TObject>(TObject @object) {
            var dc = new DataContractJsonSerializer(typeof(TObject));
            using (var ms = new MemoryStream())
            {
                dc.WriteObject(ms, @object);
                return Encoding.UTF8.GetString(ms.ToArray());
            }
        }

        public TObject Deserialize<TObject>(string serialized) {
            var dc = new DataContractJsonSerializer(typeof(TObject));
            using (var ms = new MemoryStream(Encoding.UTF8.GetBytes(serialized)))
            {
                return (TObject)dc.ReadObject(ms);
            }
        }
    }
}

You can then deserialize the object and pass it your data access code as per your example above.

然后,您可以反序列化对象,并按照上面的示例将其传递给您的数据访问代码。

#3

You didn't mention about exact purpose of storing the filter.

您没有提到存储过滤器的确切目的。

If you insist to save filter into a database table, I would have following structure of the table.

如果你坚持将过滤器保存到数据库表中,我将拥有该表的以下结构。

FilterId
Field
FieldValue

An example table might be

示例表可能是

FilterId Field    FieldValue
1        Name     Tom
1        Age      24
1        Location IL       
3        Name     Mike
...

#4

The answer is much more simple than you are making it:

答案比你做的简单得多:

Essentially you should store the raw query in its own table and relate it to your People table. Don't bother storing individual filter options.

基本上,您应该将原始查询存储在自己的表中,并将其与People表关联。不要打扰存储单个过滤器选项。

Decide on a value to store (2 options)

确定要存储的值(2个选项)

Store the URL Query String

存储URL查询字符串

This id be beneficial if you like open API-style apps, and want something you can pass nicely back and forth from the client to the server and re-use without transformation.

如果您喜欢开放的API风格的应用程序,并希望能够从客户端到服务器之间来回传递并在不进行转换的情况下重复使用,则此ID非常有用。
Serialize the Filter object as a string

将Filter对象序列化为字符串

This is a really nice approach if your purpose for storing these filters remains entirely server side, and you would like to keep the data closer to a class object.

如果您存储这些过滤器的目的仍然完全在服务器端,并且您希望将数据保持更接近类对象,那么这是一种非常好的方法。

Relate your People table to your Query Filters Table:

将您的人员表与您的查询过滤器表相关联:

The best strategy here depends on what your intention and performance needs are. Some suggestions below:

这里最好的策略取决于您的意图和性能需求。以下一些建议:

Simple filtering (ex. 2-3 filters, 3-4 options each)

简单过滤(例如2-3个过滤器,每个3-4个选项)

Use Many-To-Many because the number of combinations suggests that the same filter combos will be used lots of times by lots of people.

使用多对多,因为组合的数量表明相同的过滤器组合将被很多人使用很多次。
Complex filtering

Use One-To-Many as there are so many possible individual queries, it less likely they are to be reused often enough to make the extra-normalization and performance hit worth your while.

使用一对多,因为有太多可能的个人查询,它们不太可能经常被重复使用,以使额外的规范化和性能值得你花时间。

There are certainly other options but they would depend on more detailed nuances of your application. The suggestions above would work nicely if you are say, trying to keep track of "recent queries" for a user, or "user favorite" filtering options...

当然还有其他选择,但它们取决于应用程序的更详细细微差别。如果您说,尝试跟踪用户的“最近查询”或“用户最喜欢的”过滤选项,上面的建议将很好地工作...

Personal opinion Without knowing much more about your app, I would say (1) store the query string, and (2) use OTM related tables... if and when your app shows a need for further performance profiling or issues with refactoring filter params, then come back... but chances are, it wont.

个人意见在不了解您的应用程序的情况下,我会说(1)存储查询字符串,以及(2)使用OTM相关表格...如果您的应用程序显示需要进一步的性能分析或重构过滤器参数问题,然后回来......但机会是,它不会。

GL.

#5

In my opinion the best way to save the "Filter" is to have some kind of json text string with each of the "columns names"

在我看来,保存“过滤器”的最佳方法是使用每个“列名”的某种json文本字符串

So you will have something in the db like

所以你会在db中有一些东西

Table Filters

FilterId = 5 ; FilterParams = {'age' : '>18' , ...

FilterId = 5; FilterParams = {'age':'> 18',...

Json will provide a lot of capabilities, like the use of age as an array to have more than one filter to the same "column", etc.

Json将提供许多功能,例如使用age作为数组,对同一“列”具有多个过滤器等。

Also json is some kind of standard, so you can use this "filters" with other db some day or to just "display" the filter or edit it in a web form. If you save the Query you will be attached to it.

此外,json是某种标准,因此您可以在某天使用此“过滤器”与其他数据库,或者只是“显示”过滤器或在Web表单中对其进行编辑。如果您保存查询,您将被附加到它。

Well, hope it helps!

好吧,希望它有所帮助!

#6

Assuming that a nosql/object database such as Berkeley DB is out of the question, I would definitely go with option 1. Sooner or later you'll find the following requirements or others coming up:

假设像Berkeley DB这样的nosql / object数据库是不可能的,我肯定会使用选项1.迟早你会发现以下要求或其他要求:

Allow people to save their filters, label, tag, search and share them via bookmarks, tweets or whatever.

允许用户通过书签,推文或其他方式保存过滤器,标签,标签,搜索和分享。

Change what a parameter means or what it does, which will require you to version your filters for backward compatibility.

更改参数的含义或其作用,这将要求您对过滤器进行版本化以实现向后兼容性。

Provide auto-complete functions over filters, possibly using a user's filter history to inform the auto-complete.

通过过滤器提供自动完成功能,可能使用用户的过滤器历史记录来通知自动完成。

The above will be somewhat harder to satisfy if you do any kind of binary/string serialization where you'll need to parse the result and then process them.

如果您进行任何类型的二进制/字符串序列化,您需要解析结果然后处理它们,上面的内容将更难以满足。

If you can use a NoSql DB, then you'll get all the benefits of a sql store plus be able to model the 'arbitrary number of key/value pairs' very well.

如果你可以使用NoSql DB,那么你将获得sql商店的所有好处,并能够非常好地建模“任意数量的键/值对”。

#7

Have thought about using Profiles. This is a build in mechanism to store user specific info. From your description of your problem its seems a fit.

考虑过使用Profiles。这是一种用于存储用户特定信息的内置机制。根据您对问题的描述,它似乎很合适。

Profiles In ASP.NET 2.0

ASP.NET 2.0中的配置文件

I have to admit that M$ implementation is a bit dated but there is essentially nothing wrong with the approach. If you wanted to roll your own, there's quite a bit of good thinking in their API.

我不得不承认M $的实现有点过时,但这种方法基本上没有错。如果你想自己动手,那么他们的API就会有很多好的想法。

#1

    public ActionResult GetData(FilterParams filterParams)
    {
          // Peform action to get the information from your data access layer here
          var someData = _dataAccess.Retrieve(filterParams.Name, filterParams.Age, filterParams.location, . . .);

          // Save the search that was used to retrieve later here
          _dataAccess.SaveFilter(filterParams);
          return View(someData);
    }