FluentPDO入门

You know the story: writing SQL queries is so boring. Especially when you don’t have time to do it. If you feel like me, today we are going to see something really cool: Fluent PDO. If the term “PDO” sounds new to you, don’t worry. It’s a really simple concept: in the PHP world PDO stands for Persistent Data Object and it helps you abstract some basic database-related operations (like inserts, updates, deletes etc.). It’s a layer between you and the database.

您知道这个故事：编写SQL查询是如此无聊。 尤其是当您没有时间这样做时。 如果您感觉像我，今天我们将看到一些非常酷的东西： Fluent PDO 。 如果您对“ PDO”一词听起来很陌生，请不要担心。 这是一个非常简单的概念：在PHP世界中，PDO代表Persistent Data Object，它可以帮助您抽象一些与数据库有关的基本操作(例如插入，更新，删除等)。 它是您和数据库之间的一层。

The result? No more SQL queries. Maybe this is not the first one you have seen: there are many similar projects out there and every single one has its key features. Fluent’s key feature is a great JOIN Query Builder.

结果？ 没有更多SQL查询。 也许这不是您第一次看到：有很多类似的项目，每个项目都有其关键功能。 Fluent的关键功能是出色的JOIN Query Builder。

我们的FluentPDO测试项目 (Our FluentPDO Test Project)

First of all, we will need a sample project to work with. Let’s think… what about a simple multi-user wishlist?

首先，我们需要一个示例项目。 让我们想想……一个简单的多用户愿望清单呢？

There are going to be many users, and each will have favorite products. For every user we will store the first name, last name and the signup date. For every item we will store the name, brand, price and the related user id.

将会有很多用户，每个用户都有自己喜欢的产品。 对于每个用户，我们将存储名字，姓氏和注册日期。 对于每个项目，我们将存储名称，品牌，价格和相关的用户ID。

I am going to use a simple MySQL database. Here’s the structure for our data:

我将使用一个简单MySQL数据库。 这是我们数据的结构：

… and here’s the SQL dump (with some dummy data).

…这是SQL转储(带有一些虚拟数据)。

CREATE TABLE IF NOT EXISTS items (   
        id int(11) NOT NULL AUTO_INCREMENT,   
        name varchar(100) NOT NULL,   
        brand varchar(100) NOT NULL,   
        price decimal(10,2) NOT NULL,   
        user_id int(10) unsigned NOT NULL,   
        PRIMARY KEY (id),   
        KEY user_id (user_id) ) ENGINE=InnoDB  
        DEFAULT CHARSET=utf8 AUTO_INCREMENT=5 ;

    INSERT INTO items (id, name, brand, price, user_id) 
    VALUES 
    (1, 'Last Awesome Phone', 'Awesome Brand', '550.00', 1), 
    (2, 'Last Awesome TV', 'Awesome Brand', '1200.00', 1), 
    (3, 'Fantastic E-Car', 'E-Cars Inc.', '80000.00', 2), 
    (4, 'Fantastic E-Bike', 'E-Bikes Co. Ltd.', '16000.00', 2);

    CREATE TABLE IF NOT EXISTS users (
        id int(10) unsigned NOT NULL AUTO_INCREMENT,   
        first_name varchar(100) NOT NULL,   
        last_name varchar(100) NOT NULL,   
        signup_date datetime NOT NULL,   
        PRIMARY KEY (id) ) ENGINE=InnoDB  
        DEFAULT CHARSET=utf8 AUTO_INCREMENT=3;

    INSERT INTO users (id, first_name, last_name, signup_date) 
    VALUES 
    (1, 'Francesco', 'Malatesta', '2014-06-29 13:00:00'), 
    (2, 'John', 'Foo Bar', '2014-06-20 11:16:39');

    ALTER TABLE items   ADD CONSTRAINT items_ibfk_1 FOREIGN KEY (user_id) REFERENCES users (id);

Note: As you can easily imagine, this is not going to be a “complete” project. We are just trying FluentPDO, so we are not going to cover things like login, signup or application structure.

注意：您可以轻松想象，这将不是一个“完整”的项目。 我们只是在尝试FluentPDO，因此我们将不涉及登录，注册或应用程序结构之类的内容。

安装 (Installation)

You install Fluent with Composer, including the library as a dependency:

您将Fluent与Composer一起安装，包括作为依赖项的库：

"require": {
        ...
        "lichtner/fluentpdo": "dev-master"  
    }

Once done, you need to instantiate like this:

完成后，您需要像这样实例化：

$pdo = new PDO("mysql:dbname=wishlist", "root", "password");
    $fpdo = new FluentPDO($pdo);

You will have to specify your connection details in the PDO constructor method. Type your database name after the dbname= piece in the first parameter, then write your username and password as second and third arguments.

您将必须在PDO构造函数方法中指定连接详细信息。 在第一个参数的dbname=后面键入您的数据库名称，然后将您的用户名和密码分别作为第二个和第三个参数。

Then, you will pass the PDO object as a parameter for the FluentPDO object constructor.

然后，将PDO对象作为FluentPDO对象构造函数的参数传递。

That’s all, FluentPDO does not need anything else to work. No extra configuration.

就是这样，FluentPDO不需要其他任何工作。 没有额外的配置。

基本选择操作 (Basic Select Operations)

We already have some dummy data. Let’s start with the “Hello World” of SQL queries. A simple select with a where and the user primary key id as a parameter to retrieve basic information.

我们已经有一些虚拟数据。 让我们从SQL查询的“ Hello World”开始。 使用where和用户主键id作为参数的简单选择，以检索基本信息。

$user_id = 1;

    $query = $fpdo->from('users')->where('id', $user_id);

    foreach($query as $row){
        echo 'Hello, ' . $row['first_name'] . ' ' . $row['last_name'] . '!';    
    }

Nothing hard to understand, here. FluentPDO has a good and readable syntax, so it’s really easy to understand what we are doing.

这里一点都不难理解。 FluentPDO具有良好且易读的语法，因此很容易理解我们在做什么。

The from() method is used to set the right table. The where() method is used to filter our results with the same name clause. By default, in the where() method you just have to specify the field name and the value. The “=” is implied. Of course, you can also use different comparison operators. In that case you will have to write them right after the field name.

from()方法用于设置正确的表。 where()方法用于过滤具有相同名称子句的结果。 默认情况下，在where()方法中，您只需要指定字段名称和值即可。 暗含“ =”。 当然，您也可以使用不同的比较运算符。 在这种情况下，您将必须在字段名称之后立即编写它们。

$fpdo->from('items')->where('price >', 1000);

Getting the results is very easy: they are stored in the $query object we just used. You can iterate it with a foreach cycle as the example shows.

获得结果非常容易：它们存储在我们刚刚使用的$query对象中。 如示例所示，您可以使用foreach循环对其进行迭代。

In that specific case (searching an item by its primary id) we can also use a shortcut in the from() method:

在这种特定情况下(通过其主ID搜索项目)，我们还可以在from()方法中使用快捷方式：

$query = fpdo->from('users', $user_id);

    // will be the same thing as...

    $query = $fpdo->from('users')->where('id', $user_id);

Let’s see something more complicated than this.

让我们来看看比这更复杂的东西。

选择特定字段 (Select specific fields)

If you want, you can select specific fields using the select() method right after the from(). All you have to do is tell FluentPDO what fields you want to pick up with an array.

如果需要，可以在from()之后使用select()方法选择特定字段。 您所要做的就是告诉FluentPDO您要使用数组选择哪些字段。

Here’s an example:

这是一个例子：

$query = $fpdo->from('users')->select(array('first_name', 'last_name'))->where('id', $user_id);

限制和偏移 (Limit and Offset)

It is very easy to set limit and offset parameters to retrieve only a certain number of rows from the database. You can use the limit() and offset() methods like this.

设置限制和偏移量参数以仅从数据库中检索特定数量的行非常容易。 您可以像这样使用limit()和offset()方法。

// selecting the first ten results...
    $query = $fpdo->from('users')->where('id', $user_id)->limit(10)->offset(0);

The only parameter for both methods is an integer specifying the desired value (number of items for limit(), number of items to skip for offset()).

两种方法的唯一参数是指定所需值的整数( limit()的项目数， offset()跳过项数)。

具有，分组和排序 (Having, Group By and Order By)

There are also available methods for “HAVING”, “GROUP BY” and “ORDER BY” instructions.

还有“ HAVING”，“ GROUP BY”和“ ORDER BY”指令的可用方法。

Let’s see them with some examples.

让我们看一些例子。

订购依据 (Order By)

The orderBy() method is used to order results with specific criteria. Let’s make an example: here’s how to order results by price, from cheapest to most expensive.

orderBy()方法用于对具有特定条件的结果进行排序。 让我们举个例子：这是如何按价格(从最便宜到最昂贵)对结果进行排序的方法。

$query = $fpdo->from('items')->orderBy('price');

If you want to invert the order (getting results from the most expensive to the cheaper) you just have to add “DESC” after the column you chose.

如果要反转顺序(从最昂贵的到便宜的获取结果)，只需在选择的列之后添加“ DESC”。

$query = $fpdo->from('items')->orderBy('price DESC');

有 (Having)

The having() method has a very simple syntax. In the next example we are filtering every item with a price lower than $2000.

having()方法的语法非常简单。 在下一个示例中，我们将过滤价格低于$ 2000的每个商品。

$query = $fpdo->from('items')->having('price < 2000');

Quite simple.

非常简单。

You can use every comparison operator you need.

您可以使用所需的每个比较运算符。

通过...分组 (Group By)

With the groupBy() method you can group results using a specific field as a criteria. Here we are showing an items count for every brand.

使用groupBy()方法，您可以使用特定字段作为条件对结果进行分组。 在这里，我们显示了每个品牌的商品计数。

$query = $fpdo->from('items')->select('brand, COUNT(*) AS c')->groupBy('brand');

Note: you can specify an alias for a field just as you do in classic SQL.

注意：您可以像在经典SQL中一样为字段指定别名。

提取方法 (Fetching Methods)

取 (Fetch)

Using a foreach is not the only way to fetch results. What if we want to retrieve only the first result from the set?

使用foreach并不是获取结果的唯一方法。 如果我们只想从集合中检索第一个结果怎么办？

Just use the fetch() method:

只需使用fetch()方法：

$query = $fpdo->from('users');
    $row = $query->fetch();

    var_dump($row);
    // will output:
    // array(4) { ["id"]=> string(1) "1" ["first_name"]=> string(9) "Francesco" ["last_name"]=> string(9) "Malatesta" ["signup_date"]=> string(19) "2014-06-29 13:00:00" }

You can also fetch a single column, specifying its name as a parameter.

您还可以获取单个列，并将其名称指定为参数。

$query = $fpdo->from('users');
    $firstName = $query->fetch('first_name');

    var_dump($firstName);
    // will output:
    // string(9) "Francesco"

配对 (FetchPairs)

With fetchPairs() you can retrieve results as an associative array. Using a syntax like:

使用fetchPairs()您可以将结果作为关联数组检索。 使用如下语法：

fetchPairs($column1, $column2);

you will get an output like

您将得到类似的输出

// [column1_value] => "column2_value"

Here’s an example, using user unique id and the first name.

这是一个使用用户唯一ID和名字的示例。

$query = $fpdo->from('users');
    $row = $query->fetchPairs('id', 'first_name');

    var_dump($row);
    // will output:
    // array(2) { [1]=> string(9) "Francesco" [2]=> string(4) "John" }

全部提取 (FetchAll)

Last, but not least, we have the fetchAll() method.

最后但并非最不重要的一点是，我们有fetchAll()方法。

Here’s the syntax:

语法如下：

fetchAll($index = '', $selectOnly = '')

With fetchAll() we have complete control on what we are taking from the result. The first parameter, $index, is the field used as index, the $selectOnly is useful to specify what fields you want to pick up.

使用fetchAll()我们可以完全控制从结果中获取的内容。 第一个参数$index是用作索引的字段， $selectOnly可用于指定要选择的字段。

Here you have an example:

这里有一个例子：

$query = $fpdo->from('users');
    $row = $query->fetchAll('id', 'first_name, last_name');

    var_dump($row);
    // will output:
    // array(2) { [1]=> array(3) { ["id"]=> string(1) "1" ["first_name"]=> string(9) "Francesco" ["last_name"]=> string(9) "Malatesta" } [2]=> array(3) { ["id"]=> string(1) "2" ["first_name"]=> string(4) "John" ["last_name"]=> string(7) "Foo Bar" } }

Note: the column used as index (id, in this example) is also included in the final array.

注意：用作索引的列(在此示例中为id)也包含在最终数组中。

Ok, that’s enough for select operations. Let’s take a look at the other CRUD operations.

好的，对于选择操作就足够了。 让我们看一下其他CRUD操作。

插入，更新和删除 (Insert, Update and Delete)

FluentPDO is not just about selecting things. It also has classes for manipulating data in an easy way.

FluentPDO不仅仅是选择事物。 它还具有用于轻松处理数据的类。

Let’s start with the Insert operation.

让我们从插入操作开始。

插 (Insert)

$values = array('first_name' => 'Joe', 'last_name' => 'Doe', 'signup_date' => '2014-06-30 11:00:00');       
    $query = $fpdo->insertInto('users')->values(values);    
    $insert = $query->execute();

    var_dump($insert);
    // will output:
    // string(1) "3"

The method insertInto() is used to specify what table you want to use for the operation. Then, you will have to use the values() method to assign the desired values (in this case they are stored in the $values associative array).

方法insertInto()用于指定要用于该操作的表。 然后，您将必须使用values()方法来分配所需的值(在这种情况下，它们存储在$values关联数组中)。

The last step will be the execute() method, which will return the new record’s primary key.

最后一步是execute()方法，它将返回新记录的主键。

You can also use this shortcut, if you want:

如果需要，也可以使用此快捷方式：

$query = $fpdo->insertInto('users', $values);

更新资料 (Update)

The update method is really similar. Let’s see an example.

更新方法确实很相似。 让我们来看一个例子。

$set = array('last_name' => 'Foo Foo Bar');
    $query = $fpdo->update('users')->set($set)->where('id', 2);

    // you can also use this shortcut:
    $query = $fpdo->update('users', $set, 1);
    $query->execute();

Using the set() method you can specify new values for your update operation.

使用set()方法，可以为更新操作指定新值。

With the where() method we are filtering the affected rows. There is also a shortcut, as before.

使用where()方法，我们可以过滤受影响的行。 和以前一样，还有一个快捷方式。

删除 (Delete)

The delete operation is even simpler. Here’s a quick example.

删除操作甚至更简单。 这是一个简单的例子。

$query = $fpdo->deleteFrom('users')->where('id', 3);

    // ... or you can use this:
    $query = $fpdo->deleteFrom('users', 3);
    $query->execute();

If you want to delete a record knowing its primary key, you can do it with the deleteFrom() shortcut above.

如果要删除知道其主键的记录，则可以使用上面的deleteFrom()快捷方式来完成。

Note: as you can see from the examples here, you must use the execute() method to run the delete query. If you don’t, you will not change anything on the database. The same thing works also for inserts and updates. Keep it in mind.

注意：从此处的示例中可以看到，必须使用execute()方法运行删除查询。 否则，您将不会更改数据库上的任何内容。 插入和更新同样适用。 记在心上。

高级功能 (Advanced Features)

As I told you before, every project of that kind has its unique features. No exceptions for FluentPDO: we are going to analyse two of these features: the Join Query Builder and the Debugger.

正如我之前告诉您的那样，每个此类项目都有其独特的功能。 FluentPDO也不例外：我们将分析以下两个功能：联接查询生成器和调试器。

联接查询生成器 (The Join Query Builder)

Probably the most important unique feature of FluentPDO. The builder is really useful if you want to simplify your work and write less code. Let’s see how to use it.

可能是FluentPDO最重要的独特功能。 如果您想简化工作并减少代码编写，则构建器非常有用。 让我们看看如何使用它。

We are going to start with a “classic” join query made with FluentPDO.

我们将从使用FluentPDO进行的“经典”联接查询开始。

Something like that:

像这样：

$query = $fpdo->from('items')->leftJoin('user ON user.id = items.user_id')->select('user.first_name');

Ok: we are using the classic syntax in the special method leftJoin(). Not bad.

好的：我们在特殊方法leftJoin()中使用经典语法。 不错。

However, we can do something better. If you use conventions in your table structure, you can use this code:

但是，我们可以做得更好。 如果在表结构中使用约定，则可以使用以下代码：

$query = $fpdo->from('article')->leftJoin('user')->select('user.name');

Great, huh? Well, quick is really cool… but what about smart?

很好，是吗？ 好吧，快速真的很棒……但是聪明呢？

Take a look here:

在这里看看：

$query = $fpdo->from('items')->select('users.first_name');

It gets better.

它变得更好了。

Actually, FluentPDO understands what you want to do and automatically builds the query using the data you provided in the select() method, with a table.field_name format string.

实际上，FluentPDO理解您想要做什么，并使用您在select()方法中提供的数据select()带有table.field_name格式字符串select()自动构建查询。

Here you can read the final built query for the last example:

在这里，您可以阅读最后一个示例的最终构建查询：

SELECT items.*, users.first_name 
    FROM items 
    LEFT JOIN users ON users.id = items.user_id

That definitely looks good.

那绝对看起来不错。

If you want, of course, you can create aliases for fields:

当然，如果需要，您可以为字段创建别名：

$query = $fpdo->from('items')->select('users.first_name AS user_first_name');

调试器 (The Debugger)

FluentPDO comes out with a built-in debugger system that you can use to test your queries and inspect them.

FluentPDO带有内置的调试器系统，您可以使用它来测试查询和检查查询。

It works with a simple closure system. If you want to use debug, just place this code after your connection code.

它与简单的封闭系统配合使用。 如果要使用调试，只需将此代码放在连接代码之后。

$fpdo->debug = function($BaseQuery) {
        echo "query: " . $BaseQuery->getQuery(false) . "<br/>";
        echo "parameters: " . implode(', ', $BaseQuery->getParameters()) . "<br/>";
        echo "rowCount: " . $BaseQuery->getResult()->rowCount() . "<br/>";  
    };

You can customize the closure as you want, just remember the $BaseQuery object as a parameter.

您可以根据需要自定义闭包，只需记住$BaseQuery对象作为参数即可。

The $BaseQuery object is an instance of the BaseQuery class.

$BaseQuery对象是BaseQuery类的实例。

结论 (Conclusion)

FluentPDO is a small and simple project. It is absolutely not suitable for every project, and could be improved – especially seeing as it’s been dormant for six months now – but it could be a good choice for a little/medium applications, just in case you don’t want to involve a big framework in the game. Thanks to some features like the Join Query Builder, it’s a good compromise.

FluentPDO是一个小型简单的项目。 它绝对不适合每个项目，并且可以进行改进-尤其是因为它已经Hibernate了六个月-但对于中小型应用程序来说，它可能是一个不错的选择，以防万一您不想参与其中。游戏中的大框架。 由于有诸如Join Query Builder之类的功能，这是一个很好的折衷方案。

翻译自: https://www.sitepoint.com/getting-started-fluentpdo/