symfony 查询数据_Symfony2中的数据装置

symfony 查询数据

Back when I first started to learn Symfony (1.x) with its Jobeet project, I thought the ability to load a bunch of test data into the database very useful.

回到我最初通过Jobeet项目学习Symfony(1.x)时，我认为将大量测试数据加载到数据库中的功能非常有用。

In this article, we will revisit this feature, which has been completely re-modeled and thus has a lot to teach us.

在本文中，我们将重新介绍此功能，该功能已完全重新建模，因此有很多要教给我们的知识。

制备 (Preparation)

In this article, we will have two 3rd party libraries to further enhance the power of Symfony.

在本文中，我们将有两个第三方库来进一步增强Symfony的功能。

The first is the DoctrineFixturesBundle, used to load test data with Doctrine ORM, which is the default ORM in Symfony.

第一个是DoctrineFixturesBundle ，用于使用Doctrine ORM加载测试数据， Doctrine ORM是Symfony中的默认ORM。

Please follow the instructions in Symfony official documentation to set up and configure the DoctrineFixturesBundle. If you are familiar with Composer, the process should be easy and straightforward, no hassles.

请按照Symfony 官方文档中的说明设置和配置DoctrineFixturesBundle。 如果您熟悉Composer，则该过程应该简单明了，没有麻烦。

Next, we will install PHPUnit, the default test framework used by Symfony. Its official site offers the download of the latest phpunit.phar file. Simply save that file into the Symfony root directory and we are done. Symfony has come with a default and workable PHPUnit configuration file (app/phpunit.xml.dist). In normal circumstances, we shall keep this file unchanged and PHPUnit will work fine. We’ll use PHPUnit in the followup to this article, so just make sure you have it.

接下来，我们将安装SymUnity使用的默认测试框架PHPUnit。 它的官方站点提供了最新phpunit.phar文件的下载。 只需将该文件保存到Symfony根目录中就可以了。 Symfony随附了一个默认且可行PHPUnit配置文件( app/phpunit.xml.dist )。 在正常情况下，我们将保持此文件不变，PHPUnit可以正常工作。 在本文的后续文章中，我们将使用PHPUnit，因此只需确保已安装即可。

Of course, to make the above two libraries work in our app, we will have to set up the Symfony project/app first. This topic is covered in my previous article “Building a Web App with Symfony 2: Bootstrapping”. Please take a look if you are not yet familiar with a Symfony 2 setup. As a matter of fact, this article is quite related to the Book Collection app we created before (with some modifications to the database schema, though).

当然，要使上述两个库在我们的应用程序中起作用，我们将必须首先设置Symfony项目/应用程序。 我之前的文章“使用Symfony 2：引导构建Web应用程序”中涵盖了该主题。 如果您还不熟悉Symfony 2设置，请看看。 实际上，本文与我们之前创建的Book Collection应用程序非常相关(尽管对数据库架构进行了一些修改)。

编写我们的第一个数据夹具文件 (Writing our first data fixture file)

As we are building a book collection app, we need a few tables to describe the relationships among book-related information:

在构建图书收集应用时，我们需要一些表格来描述与图书相关的信息之间的关系：

• book_book: contains the information about a book, including the book id, title, author, ISBN, publisher (FK to another book_publisher table), purchase place (FK to another book_place table), purchase date, etc.

  book_book ：包含有关书籍的信息，包括书籍ID，书名，作者，ISBN，出版商(FK到另一个book_publisher表)，购买地点(FK到另一个book_place表)，购买日期等。

• book_place, book_publisher: contains the information about a purchase place and a publisher, respectively.

  book_place ， book_publisher ：分别包含有关购买地点和发行者的信息。

• book_taglist: contains the tag for a book and it forms a many-to-many relationship to book_book table.

  book_taglist ：包含一本书的标签，它与book_book表形成多对多关系。

• book_headline, book_review: contains my reading thoughts on a particular book. Headline is the master table and forms a one-to-many relationship with book_review, a one-to-one relationship to book_book.

  book_headline ， book_review ：包含我对特定书籍的阅读想法。 标题是主表和形式与一对多的关系book_review ，一对一关系到book_book 。

• Other tables to capture additional information.
  其他表以捕获其他信息。

In this app, I am setting up the database (rsywx_test) with the following structure. The schema is drawn with the help of this online tool.

在此应用中，我使用以下结构设置数据库( rsywx_test )。 该模式是借助此在线工具绘制的。

NOTE: I have not listed all the tables/fields in this schema drawing but just those primary keys and foreign keys to illustrate the relationships among the tables. The complete SQL dump can be found in the Github repository created for this article.

注意 ：我没有列出此架构图中的所有表/字段，而仅列出了那些主键和外键来说明表之间的关系。 完整SQL转储可以在为本文创建的Github存储库中找到。

To start with, we will create the data fixture for book_place.

首先，我们将为book_place创建数据夹具。

<?php

namespace tr\rsywxBundle\DataFixtures\ORM;

use \Doctrine\Common\DataFixtures\AbstractFixture;
use \Doctrine\Common\DataFixtures\OrderedFixtureInterface;
use \Doctrine\Common\Persistence\ObjectManager;
use \tr\rsywxBundle\Entity\BookPlace as BookPlace;

class LoadPlaceData extends AbstractFixture implements OrderedFixtureInterface
{
    /**
     * 
     * {@inheritDoc}
     */
    public function load(ObjectManager $manager)
    {
        //Create a common place
        $place1=new BookPlace();
        $place1->setName('Common');
        $this->addReference('commonPlace', $place1);

        //Create a special place
        $place2=new BookPlace();
        $place2->setName('Special');
        $this->addReference('specialPlace', $place2);

        $manager->persist($place1);
        $manager->persist($place2);

        $manager->flush();
    }

    /**
     * 
     * {@inheritDoc}
     */
    public function getOrder()
    {
       return 2; 
    }
}

A few things to highlight here:

这里要强调的几件事：

1. There are 4 use statements. The first 3 are quite standard and required for almost all data fixture files. The last use is to include the namespace for table book_place. This will have to match the table we are to load data into.

   有4个use语句。 前三个是非常标准的，几乎是所有数据夹具文件所必需的。 最后的use是包括表book_place的名称空间。 这必须与我们要将数据加载到的表匹配。

2. A data fixture file will contain a customized class (extends from AbstractFixture and implements OrderedFixtureInterface) and has at least two functions in it: load (to load the sample data) and getOrder (to specify the loading order and preserve reference integrity among PK/FK).

   数据夹具文件将包含一个自定义类(从AbstractFixture扩展并实现OrderedFixtureInterface )，并且其中至少具有两个功能： load (加载示例数据)和getOrder (指定加载顺序并保持PK / FK之间的引用完整性) )。

3. In the load function, we create a few instances of book_place and assign the values for name. The id field is not to be assigned as it is an auto-increment field.

   在load函数中，我们创建了book_place的几个实例，并为name分配了值。 id字段是自动递增的字段，因此请勿分配。

4. Furthermore, after creating an object of book_place, we add a reference to this object by $this->addReference(). This is a MUST as we will need this object to be referenced again in our book_book table data loading. You may recall that in book_book table, its field place is a FK to book_place table’s id field.

   此外，在创建book_place对象book_place ，我们通过$this->addReference()添加对此对象的引用。 这是必须的，因为我们将需要在book_book表数据加载中再次引用该对象。 您可能还记得在book_book表中，它的字段place是book_place表的id字段的FK。

5. Finally, we persist the object and make the physical insertion into the table.
   最后，我们保留对象并将其物理插入表中。

6. In getOrder function, we explicitly indicate that this fixture file should be loaded in the 2nd place (only after book_publisher in my case).

   在getOrder函数中，我们明确指示应将夹具文件加载到第二位(在我的情况下，仅在book_publisher之后)。

This data fixture file should be placed under your project root/src/your bundle namespace/DataFixtures/ORM. In my vagrant Symfony setup, the directory is /www/rsywx/src/tr/rsywxBundle/DataFixtures/ORM and the file name is LoadPlace.php.

该数据固定文件应放置在your project root/src/your bundle namespace/DataFixtures/ORM 。 在我无所事事的Symfony设置中，目录为/www/rsywx/src/tr/rsywxBundle/DataFixtures/ORM ，文件名为LoadPlace.php 。

Now we can load the data fixture file:

现在我们可以加载数据夹具文件：

php app/console doctrine:fixtures:load

PHP的应用程序/控制台学说：夹具：负载

This Symfony console command will grab all files resided in the above directory and start to insert sample data in the order specified by each file’s getOrder() function.

这个Symfony控制台命令将抓取上述目录中的所有文件，并开始按照每个文件的getOrder()函数指定的顺序插入示例数据。

Done! Go back to your favorite MySQL administration tool and do a select * from book_place or alike, we will see two records have been inserted into the book_place table.

做完了！ 回到您最喜欢MySQL管理工具，并select * from book_place或类似的地方执行select * from book_place ，我们将看到两个记录已插入book_place表中。

加载书籍数据并引用其他对象 (Load book data and reference to other objects)

Next, let’s load book data. This fixture file will be a bit complicated as it will use a loop to create many book objects and also use previously created object reference to set the value for foreign keys.

接下来，让我们加载书籍数据。 这个Fixture文件会有点复杂，因为它将使用循环来创建许多书本对象，并且还将使用先前创建的对象引用来设置外键的值。

The excerpt of LoadBook.php is as below:

LoadBook.php的摘录如下：

<?php

namespace tr\rsywxBundle\DataFixtures\ORM;

...
use \tr\rsywxBundle\Entity\BookBook as BookBook;

class LoadBookData extends AbstractFixture implements OrderedFixtureInterface
{

    /**
     * 
     * {@inheritDoc}
     */
    public function load(ObjectManager $manager)
    {
        //Now we create a 100 general book
        for ($i = 1; $i <= 100; $i++)
        {
            $p = new BookBook();
            $p->setAuthor('Normal');
...
            $p->setPurchdate(new \DateTime());
            $p->setPubdate(new \DateTime());
...
            $p->setPage($i);
...
            $p->setPublisher($this->getReference('commonPub'));
            $p->setPlace($this->getReference('commonPlace'));
            $manager->persist($p);
        }

        //Create a special book
        $s = new BookBook();
...
        $s->setPurchdate(new \DateTime('1970-1-1'));
        $s->setPubdate(new \DateTime('1970-1-1'));
        $s->setPrintdate(new \DateTime('1970-1-1'));
...
        $this->addReference('aBook', $s);

        $manager->persist($s);

        $manager->flush();

    }

    /**
     * 
     * {@inheritDoc}
     */
    public function getOrder()
    {
        return 3;
    }

}

In this fixture file, we created a total of 101 records. One of them is special as it is created at the very end (thus having a largest id) and its date-related fields are set to EPOCH.

在这个Fixture文件中，我们总共创建了101条记录。 其中之一很特别，因为它是在末尾创建的(因此具有最大id )，并且其与日期相关的字段设置为EPOCH。

Notice how we set the FK fields (publisher and place). As these two fields are FK to other tables, we can’t simply assign an integer to those two. We will have to use $this->getReference() to grab the objects previously created. And YES! That is why we need getOrder() to specify the sequence of loading, i.e., book table must be loaded after publisher and place table.

注意我们如何设置FK字段( publisher和place )。 由于这两个字段对其他表都是FK，因此我们不能简单地为这两个表分配一个整数。 我们将不得不使用$this->getReference()来获取先前创建的对象。 是的！ 这就是为什么我们需要getOrder()来指定加载顺序，即书本表必须在发布者和放置表之后加载。

Also, we created a reference for my “Special Book” so that this object can be used later on for other tables (book_headline, book_taglist).

另外，我们为“ Special Book”创建了一个引用，以便以后可以将该对象用于其他表( book_headline ， book_taglist )。

In my app, there is a total of 6 data fixture files. After loading these 6 files, the database for my app is more or less ready. After some coding on the Controllers, Views, the site will be running:

在我的应用程序中，总共有6个数据夹具文件。 加载这6个文件后，我的应用程序数据库已准备就绪。 在Controllers，Views上进行一些编码之后，该站点将运行：

With sample data, the site is more meaningful and can help in our further fine tuning and functional testing.

通过样本数据，该站点将变得更有意义，并且可以帮助我们进行进一步的微调和功能测试。

We do have a lot of functionality in this index page that is not covered in this article. For example, there are two Dart widgets showing QOTD (Quote Of The Day) and local weather information. This is covered in my previous articles in Sitepoint (Part 1 and Part 2).

在此索引页面中，我们确实具有很多功能，而本文并未涉及。 例如，有两个Dart小部件显示QOTD(每日行情)和本地天气信息。 我以前在Sitepoint中的文章( 第1 部分和第2部分 )中对此进行了介绍。

We are almost done dealing with the Symfony DataFixturesBundle, save for one more thing. Recall my article on Unique Index? In a way, I am a strong protester against using auto-incremented integer fields as a table’s primary key. Also as a perfectionist, once I have created a meaningful unique index, I will simply eliminate the existence of another auto-incremented integer field and use that UI as my PK.

除了另外一件事情，我们几乎已经完成了Symfony DataFixturesBundle的处理。 还记得我关于唯一索引的文章吗？ 在某种程度上，我强烈反对将自动递增的整数字段用作表的主键。 作为一名完美主义者，一旦创建了有意义的唯一索引，我将简单地消除另一个自动递增的整数字段的存在，并将该UI用作我的PK。

But to comply with the data fixture bundle’s constraint, there is one compromise to make. Symfony DataFixturesBundle says:

但是为了遵守数据夹具捆绑包的约束，需要做出一个折衷。 Symfony DataFixturesBundle说：

There must be an auto-incremented integer field as the PK to establish the relationship reference

必须有一个自动递增的整数字段作为PK以建立关系引用

Of course, you can create a UI and more indexes if you like.

当然，您可以根据需要创建UI和更多索引。

So, in my book_book table, even though I have a self-sufficient field (book_id in the form like “12345”, a string to label my books), I have to create another field (id, auto-incremented integer) to act as the PK!

因此，在我的book_book表中，即使我有一个自足的字段(格式为book_id ，例如“ 12345”，这是一个用来标记我的书的字符串)，我也必须创建另一个字段( id ，自动递增的整数)作为PK！

If we skip this step, the database structure itself is still valid and all FK/PK relationships can still be established. However, there will be at least one weird and hard-to-understand error: $this->getReference() in any table that references book_book (without an auto-incremented integer field as PK) will fail, prompting “Undefined index” error.

如果我们跳过此步骤，则数据库结构本身仍然有效，并且仍然可以建立所有FK / PK关系。 但是，将至少存在一个奇怪且难以理解的错误：任何引用book_book表中的$this->getReference() (没有自动递增的整数字段作为PK)将失败，并提示“未定义索引”错误。

结论 (Conclusion)

In this article we covered Data Fixtures, and the proper way to provide sample data to our app via Doctrine. In a followup article coming out next Monday, we’ll go through functional testing and use the data we’ve seeded here.

在本文中，我们介绍了数据夹具，以及通过Doctrine向我们的应用程序提供示例数据的正确方法。 在下周一发布的后续文章中，我们将进行功能测试并使用我们在此处播种的数据。

Feel free to comment and we will be happy to cover this topic into more detail if you are interested.

随时发表评论，如果您有兴趣，我们很乐意对此主题进行更详细的介绍。

翻译自: https://www.sitepoint.com/data-fixtures-symfony2/

symfony 查询数据