PINQ-查询数据集-简介

超、凢脫俗 2022-12-03 01:58 168阅读 0赞

You may have heard of [LINQ][] (Language-Integrated Query), a “set of features introduced in Visual Studio 2008 that extends powerful query capabilities to the language syntax of C\# and Visual Basic”.

您可能听说过[LINQ][] (语言集成查询)，这是“ Visual Studio 2008中引入的一组功能，将强大的查询功能扩展到C＃和Visual Basic的语言语法”。

It provides necessary interfaces and syntax to perform various queries on a given dataset so that operations like filtering, sorting, grouping, aggregating, etc can be performed.

它提供了必要的接口和语法，可以对给定的数据集执行各种查询，以便可以执行诸如过滤，排序，分组，聚合等操作。

PINQ (PHP Integrated Query) is “based off the .NET’s Linq, and unifies querying across arrays/iterators and external data sources, in a single readable and concise fluent API”. (Excerpted from [PINQ’s official site][PINQ_s official site])

PINQ(PHP集成查询)“基于.NET的Linq，并通过单个可读且简洁的API统一了跨阵列/迭代器和外部数据源的查询”。 (摘自[PINQ的官方网站][PINQ_s official site] )

### 为什么要使用另一种查询语言？ **(**Why another query language?**)** ###

PHPers are very much comfortable with executing queries with either raw SQL statements or via ORM. We select the data from a database, process it and display it in a chosen format (say, a table form). If we need another set of data, we issue another statement, process the returned dataset and display it.

PHPer非常适合使用原始SQL语句或通过ORM执行查询。 我们从数据库中选择数据，进行处理并以选定的格式(例如表格形式)显示。 如果需要另一组数据，则发出另一条语句，处理返回的数据集并显示它。

In normal cirucumstances, this process is both sufficient and efficient.

在正常情况下，此过程既充分又有效。

But there are cases where this process simply won’t work. Take, for example, a typical e-Commerce website. The user enters one search keyword (say, “router”) and the site displays every matching item. The initial search may only search items with their description, category or tags containing the keyword. Next, the user will start to fine tune the search results by selecting a brand, a price range, etc.

但是在某些情况下，此过程根本行不通。 以一个典型的电子商务网站为例。 用户输入一个搜索关键字(例如“路由器”)，该站点将显示每个匹配的项目。 初始搜索只能搜索具有其描述，类别或包含关键字的标签的项目。 接下来，用户将开始通过选择品牌，价格范围等来微调搜索结果。

This fine tuning process is called “faceted” search. Some database engines (like SOLR) have this capability built in (as described in this series: [Using Solarium for SOLR Search][]) but obviously MySQL does not come with this functionality.

这种微调过程称为“分面”搜索。 一些数据库引擎(如SOLR)具有内置的此功能(如本系列中所述： [使用Solarium进行SOLR搜索][Using Solarium for SOLR Search] )，但显然MySQL并未提供此功能。

That does not mean, however, that MySQL can’t provide such features. After all, it is all about constructing a new SQL statement and fetching the data again. This has some disadvantages, however:

但是，这并不意味着MySQL无法提供此类功能。 毕竟，这都是关于构造新SQL语句并再次获取数据的。 但是，这有一些缺点：

1.  The criteria of the SQL statement, i.e., the “where” and/or “group by” part, can get very complicated after SQL construction.
    
    SQL语句的条件，即“ where”和/或“ group by”部分，在构建SQL之后会变得非常复杂。
2.  As the SQL statement will be very dynamic, it can’t be optimized and will make indexing more difficult.
    
    由于SQL语句将是非常动态的，因此无法进行优化，这将使索引编制更加困难。
3.  It will create a huge overhead when communicating the SQL statement back to the db server.
    
    将SQL语句传递回db服务器时，将产生巨大的开销。

In these cases, [PINQ][PINQ_s official site] may come in handy. It is a PHP version of the LINQ library which provides filtering, sorting, grouping, aggregating, and indexing on a given dataset.

在这些情况下， [PINQ][PINQ_s official site]可能会派上用场。 它是LINQ库PHP版本，可对给定的数据集进行过滤，排序，分组，聚合和索引。

### 制备 **(**Preparation**)** ###

In this series of two parts, we will demonstrate how to use PINQ to mimic a “faceted” search. We will use the sample book collection application’s data (see [Data Fixtures in Symfony2][] on how to dump the sample data) but with some slight modifications.

在这个由两部分组成的系列文章中，我们将演示如何使用PINQ模仿“分面”搜索。 我们将使用样本书收集应用程序的数据(有关如何转储样本数据的信息，请参见[Symfony2中的数据夹具][Data Fixtures in Symfony2] )，但需进行一些细微修改。

Also, we will use [Silex][], a very light-weight PHP framework and [Bootstrap][] CSS to simplify the app setup. Please follow the instructions in their respective websites on how to set up a Silex web app and integrate Bootstrap CSS.

另外，我们将使用[Silex][] (一个非常轻巧PHP框架)和[Bootstrap][] CSS来简化应用设置。 请遵循各自网站上的说明，以了解如何设置Silex网络应用程序以及如何集成Bootstrap CSS。

The sample data we used in this demo is slightly modified and I have uploaded it to [the repo for this demo][]. The source code can also be found there.

我们在本演示中使用的样本数据已稍作修改，我已将其上载到[该演示的回购中][the repo for this demo] 。 也可以在此处找到源代码。

### PINQ安装 **(**PINQ Installation**)** ###

The recommended PINQ installation is to modify the `composer.json` file that comes with Silex and add one more line in its `require` section:

推荐的PINQ安装是修改Silex随附的`composer.json`文件，并在其`require`节中再添加一行：

{
        "require": {
            "silex/silex": "~1.1",
            "twig/twig": ">=1.8,<2.0-dev",
            "doctrine/dbal": "2.2.*",
            "symfony/twig-bridge": "~2.3",
            "timetoogo/pinq": "~2.0"
        }
    }

Please note that I have also added a few more dependencies: Twig (and Twig-Bridge) to display the results, and Doctrine as I am to grab data from my database for further processing.

请注意，我还添加了其他一些依赖项：Twig(和Twig-Bridge)显示结果，而Doctrine则是从数据库中获取数据以进行进一步处理。

After this, we can run a `composer.phar update` command to install all necessary packages.

之后，我们可以运行`composer.phar update`命令来安装所有必需的软件包。

### 演示1：基本用法 **(**Demo 1: the basic usage**)** ###

We will first show a few lines of code to demonstrate the basic usage of PINQ. I will grab the books as they are, do a simple filter (for price between 90 and 99) and then display the aggregation information for different authors.

我们将首先显示几行代码，以演示PINQ的基本用法。 我将按原样抓书，做一个简单的过滤器(价格在90到99之间)，然后显示不同作者的汇总信息。

The display will be like this:

显示将如下所示：

![alt][]

Let’s see how to make this demo page.

让我们看看如何制作这个演示页面。

**NOTE:** I am not going to cover the basics on Silex app setup in this article. Silex’s default `index.php` does not include support for Twig/Twig-bridge and Doctrine-DBAL. You will need to enable these two modules in your app’s `index.php` file. Please refer to Silex’s official site to find out how.

**注意：本文**将不介绍Silex应用设置的基础知识。 Silex的默认`index.php`不包括对Twig / Twig-bridge和Doctrine-DBAL的支持。 您将需要在应用程序的`index.php`文件中启用这两个模块。 请访问Silex的官方网站以了解操作方法。

We first create a `Demo` class in `pinqDemo.php` as the data provider for our app:

我们首先在`pinqDemo.php`创建一个`Demo`类作为应用程序的数据提供者：

<?php
    
    namespace pinqDemo
    {
    
        class Demo
        {
    
            private $books = '';
    
            public function __construct($app)
            {
                $sql = 'select * from book_book order by id';
                $this->books = $app['db']->fetchAll($sql);
            }
    
            public function test1($app)
            {
                return $this->books;
            }
    
        }
    
    }

This file is very simple. In the class declaration, we have a constructor that retrieves the data from the db server and a function that returns the dataset back to the calling function.

这个文件很简单。 在类声明中，我们有一个从db服务器检索数据的构造函数，以及一个将数据集返回给调用函数的函数。

This class can of course be enhanced, for example, by introducing some typical design patterns.

当然，可以通过引入一些典型的设计模式来增强此类。

Next we will move to the `index.php` file and see some of the basic PINQ usages applied on the data that we retrieved.

接下来，我们将移至`index.php`文件，并查看应用于检索到的数据的一些基本PINQ用法。

// Excerpt of index.php
    use Pinq\ITraversable,
        Pinq\Traversable;
    
    ... ...
    
    $app->get('/demo1', function () use ($app)
    {
        global $demo;
        $books = $demo->test1($app);
        $data = Traversable::from($books);
    
        //Apply first filter
        $filter1 = $data
                ->where(function($row)
                {
                    return $row['price'] > 90 && $row['price'] < 99;
                })
                ->orderByDescending(function($row)
        {
            return $row['id'];
        });
    
        $filter2 = $filter1
                ->groupBy(function($row)
                {
                    return $row['author'];
                })
                ->select(
                function(ITraversable $filter1)
        {
            return ['author' => $filter1->first()['author'], 'count' => $filter1->count()];
        }
        );
    
        return $app['twig']->render('demo1.html.twig', array('orig' => $data, 'filter1' => $filter1, 'filter2'=>$filter2));
    }
    );

We mapped the URI `/demo1` to the first sample. In the handling function for this route, we basically do 4 things:

我们将URI `/demo1`映射到第一个示例。 在此路线的处理功能中，我们基本上要做四件事：

1.  Get the data retrieved from our `pinqDemo\Demo` class
    
    从我们的`pinqDemo\Demo`类获取数据
2.  Apply the first filter. In our case, we apply a price range to our original data
    
    应用第一个过滤器。 在我们的案例中，我们将价格范围应用于原始数据
3.  Apply another operation (grouping) to the data generated in Step 2.
    
    对步骤2中生成的数据进行另一个操作(分组)。
4.  Display data generated in Step 2 and 3.
    
    显示在步骤2和3中生成的数据。

#### 深入了解PINQ相关声明 **(**A deeper look at PINQ related statements**)** ####

Above all things, if we are going to use PINQ, we need to provide a dataset.

首先，如果要使用PINQ，则需要提供一个数据集。

In PINQ’s terminology, the dataset to be manipulated is a “Traversable”. As expected, we can construct a “Traversable” from our dataset returned from our SQL queries:

用PINQ的术语来说，要处理的数据集是“可遍历的”。 如预期的那样，我们可以从SQL查询返回的数据集中构造“ Traversable”：

$data = Traversable::from($books);

To apply an operation on the newly created “Traversable” object – I will use “dataset” onwards – PINQ provides a rich set of functions:

要对新创建的“ Traversable”对象执行操作-我将继续使用“数据集”-PINQ提供了一组丰富的功能：

*  Filtering, `where` clause;
    
    过滤， `where`子句；
 *  Ordering, such as `orderByAscending` and `orderByDescending` clause;
    
    排序，例如`orderByAscending`和`orderByDescending`子句；
 *  Grouping, `groupBy` clause;
    
    分组， `groupBy`子句；
 *  Aggregating, such as `count`, `average` clause;
    
    汇总，例如`count` ， `average`子句；
 *  Selecting, to select and/or construct fields in the resulted dataset, such as `select` clause;
    
    选择，选择和/或构造结果数据集中的字段，例如`select`子句；
 *  And others like joining, etc.
    
    还有其他一些人，例如加入等等。

The complete API reference can be found in [PINQ’s official documentation site][PINQ_s official documentation site].

完整的API参考可在[PINQ的官方文档网站中找到][PINQ_s official documentation site] 。

In our `demo1`, the first operation we applied is filtering and sorting:

在我们的`demo1` ，我们应用的第一个操作是过滤和排序：

$filter1 = $data
                ->where(function($row)
                {
                    return $row['price'] > 90 && $row['price'] < 99;
                })
                ->orderByDescending(function($row)
        {
            return $row['id'];
        });

PINQ uses advanced PHP features like closures and anonymous functions to perform these tasks.

PINQ使用高级PHP功能(如闭包和匿名函数)来执行这些任务。

The syntax is quite similiar to the one we can see when we are using an ORM: the commands can be chained, each continuing on to the next command in the chain.

语法与使用ORM时可以看到的语法非常相似：这些命令可以链接起来，每个命令都继续执行链中的下一个命令。

The main differences between this and an ORM are:

这个和ORM之间的主要区别是：

1.  We are not operating on a db connection or “Entity Manager” but directly on a dataset
    
    我们不是在数据库连接或“实体管理器”上运行，而是直接在数据集上运行
2.  Filtering criteria (and other functions) is not expressed in SQL-like syntax but in PHP
    
    过滤条件(和其他函数)不是用类似SQL的语法表示的，而是用PHP表示的

After we applied the filter, which is a `where` clause, we also did a sorting of the filtered data by `id` in descending order.

在应用了`where`子句的过滤器之后，我们还按照`id`降序对过滤后的数据进行了排序。

If you are using the same sample data as I am, the resulting dataset (`$filter1`) will contain 9 records and they are displayed in the 2nd section of the final rendered page as shown above.

如果您使用与我相同的示例数据，则所得数据集( `$filter1` )将包含9条记录，它们将显示在最终呈现页面的第二部分中，如上所示。

One of PINQ’s selling points is that the dataset generated can be re-used. This gives us the flexibility to apply multiple operations on the same dataset but provides different sub datasets and/or aggregation information, without destroying the original dataset.

PINQ的卖点之一是所生成的数据集可以重复使用。 这使我们可以灵活地在同一数据集上应用多个操作，但可以提供不同的子数据集和/或聚合信息，而不会破坏原始数据集。

In our sample code above, immediately after we have applied the first filter, we do an aggregation job to find out how many distinct authors there are and how many books they have written:

在上面的示例代码中，在应用第一个过滤器之后，我们立即进行汇总工作以找出有多少不同的作者以及他们写了多少本书：

$filter2 = $filter1
                ->groupBy(function($row)
                {
                    return $row['author'];
                })
                ->select(
        function(ITraversable $filter1)
        {
            return ['author' => $filter1->first()['author'], 'count' => $filter1->count()];
        }
        );

The `groupBy` function is familiar but the `select` function needs a bit more elaboration.

`groupBy`函数很熟悉，但是`select`函数需要更多的阐述。

For a typical `group by` aggregation, we expect to return at least two values from the execution: one is the key used for grouping (`author`) and the aggregation information (`count`) related to that key.

对于典型的`group by`聚合`group by` ，我们期望从执行中至少返回两个值：一个是用于分组的密钥( `author` )和与该密钥相关的聚合信息( `count` )。

Thus, the above statement inside the `function(ITraversable $filter1)` selects the `author` value from the first record in each group, and also the count for that group. This is exactly what we need.

因此， `function(ITraversable $filter1)`的上述语句从每个组的第一条记录中选择`author`值，以及该组的计数。 这正是我们所需要的。

The result is displayed in the bottom section of the page as shown above: in the total 9 books filtered, we have 2 books each for Author 0/1/23 and 1 book for Author 4.

结果显示在页面底部，如上所示：在筛选出的9本书中，作者0/1/23的每本有2本书，作者4的每本有1本书。

If we want to show the total price in this grouping, we can further extend the `select` function statement:

如果要在此分组中显示总价，可以进一步扩展`select`函数语句：

->select(
        function(ITraversable $filter1)
        {
            return
    		 ['author' => $filter1->first()['author'],
     		  'count' => $filter1->count(),
    		   'sum'=>$filter1->sum(
    				function($row)
                    {
                        return $row['price'];
                    }
               )
               ];
        }
        )

Anonymous functions get embedded and return the correct results.

匿名函数被嵌入并返回正确的结果。

We can then chain another `orderByAscending` to the `select` statement to order the dataset by `sum`:

然后，我们可以将另一个`orderByAscending`到`select`语句，以按`sum`对数据集进行排序：

->orderByAscending(function($row){return $row['sum'];});

This is very convenient and consistent.

这是非常方便且一致的。

Some may argue that in the above `groupBy` and `select` statements, the syntax used may not seem intuitive enough. Why not, say, use the below more straightforward syntax?

有人可能会争辩说，在上述`groupBy`和`select`语句中，所使用的语法似乎不够直观。 为什么不使用下面更简单的语法？

groupBy(function(return $row['author'];), $filter1->count());

Well, the author replied that it is impossible to do a grouping like this. And in PINQ’s current version (2.1), my approach is the best. However, the author hinted in his reply to me that in the next major release of PINQ (3.0), a much improved syntax will be introduced for such a common task, i.e., grouping by a key and also getting the respective aggregation information. Let’s keep an eye on it.

好吧，作者回答说不可能进行这样的分组。 在PINQ的当前版本(2.1)中，我的方法是最好的。 但是，作者在对我的答复中暗示，在PINQ(3.0)的下一个主要版本中，将为这种常见任务引入一种大大改进的语法，即按关键字分组并获得相应的聚合信息。 让我们密切关注它。

### 结论与前进 **(**Conclusion and moving forward**)** ###

In this article, we briefly introduced the basic functionality of PINQ and how to use it in a web app.

在本文中，我们简要介绍了PINQ的基本功能以及如何在Web应用程序中使用它。

PINQ is under heavy development and thus its documentation is not fully there yet. Almost all functions lack a solid and practical demonstration. I hope this article can be of help in this sense, but I also hope the author of PINQ can put some effort in the documentation as well.

PINQ正在大量开发中，因此其文档尚不完善。 几乎所有功能都缺乏扎实实用的演示。 我希望本文可以在这种意义上有所帮助，但是我也希望PINQ的作者也可以在文档中做出一些努力。

In the followup, I will try to illustrate how to use it to mimic a kind of “faceted” search capability.

在后续文章中，我将尝试说明如何使用它来模仿一种“多面”搜索功能。

Feel free to comment and give us your thoughts!

随时发表评论并给我们您的想法！

> 翻译自: [https://www.sitepoint.com/pinq-querify-datasets-introduction/][https_www.sitepoint.com_pinq-querify-datasets-introduction]

[LINQ]: http://msdn.microsoft.com/en-us/library/bb397926.aspx
[PINQ_s official site]: http://timetoogo.github.io/Pinq/
[Using Solarium for SOLR Search]: https://www.sitepoint.com/series/using-solarium-for-solr-search/
[Data Fixtures in Symfony2]: https://www.sitepoint.com/data-fixtures-symfony2/
[Silex]: http://silex.sensiolabs.org/
[Bootstrap]: http://getbootstrap.com/
[the repo for this demo]: https://github.com/sitepoint-examples/pinq
[alt]: /images/20221123/10b47927873f4755bb557c564673fd77.png
[PINQ_s official documentation site]: http://timetoogo.github.io/Pinq/docs/
[https_www.sitepoint.com_pinq-querify-datasets-introduction]: https://www.sitepoint.com/pinq-querify-datasets-introduction/