Friday, January 8, 2016
Symfony custom constaint: check total size of several form attachements
The cookbook link on how to create a custom constraint is this one:
http://symfony.com/doc/master/cookbook/validation/custom_constraint.html
Request: total size of form attachments should not be bigger than a given value.
Implementation:
I've created a directory 'Validator' under my Bundle where I added two classes:
- FilesTotalSize which extends class Constaint
- FilesTotalSizeValidator extends ConstaintValidator
FilesTotalSize:
<?php
namespace Bundle\Validator;
use Symfony\Component\Validator\Constraint;
/** @Annotation */
class FilesTotalSize extends Constraint
{
public $message;
public function validatedBy()
{
return get_class($this).'Validator';
}
public function getTargets()
{
return self::CLASS_CONSTRAINT;
}
}
The name of the Validation class will be constraint class concatenated with 'Validator';
Because this constraint is checking values from several fields is considered a class validation (as opposite to field validation which can be seen in the documentation examples ).
For this reason getTargets will return CLASS_CONSTRAINT.
The actual check of the condition is made in the validator class. The variable '$value' will give access to the object to be validated. 'attachement1', 'attachement2' and 'attachement3' are the names of the fields declared in the Entity used for file uploading (file). The size is expressed in bytes, so for 3MB I have 3 millions of bytes. The method 'addViolation' receive as parameter the error message to be displayed.
.
FilesTotalSizeValidator:
<?php
namespace Bundle\Validator;
use Symfony\Component\Validator\Constraint;
use Symfony\Component\Validator\ConstraintValidator;
class FilesTotalSizeValidator extends ConstraintValidator
{
public function validate($value, Constraint $constraint)
{
$size_att1=0;
$size_att2=0;
$size_att3=0;
if (property_exists($value, 'attachement1')){
if(!is_null($value->getAttachement1())){
$size_att1= $value->getAttachement1()->getClientSize();
}
}
if (property_exists($value, 'attachement2')){
if(!is_null($value->getAttachement2())){
$size_att2= $value->getAttachement2()->getClientSize();
}
}
if (property_exists($value, 'attachement3')){
if(!is_null($value->getAttachement3())){
$size_att3= $value->getAttachement3()->getClientSize();
}
}
$totalSize=0;
$totalSize=(int)$size_att1 +(int)$size_att2 +(int)$size_att3;
if ($totalSize > 3145728) { //check if total size of files is bigger than 3MB
$this->context->addViolation('Maximum size is 3MB!');
}
}
}
Usage:
In the entity class:
use MyBundle\Validator\FilesTotalSize;
/**
* @ORM\Entity
* @FilesTotalSize
*/
class EntityName {
Doctrine Single Table Inheritance
I have a request to develop 3 forms to collect information from users. They share some of the information like: name, address, date, amount and some fields are different, each form will have some attachments (3 or 2) which represent different types of documents. More than that on the 'amount ' field there are different validations to make, depending on which form is applied.
How should I implement this? After some research the Doctrine Single Table Inheritance seems to be the answer:
Official documentation: http://docs.doctrine-project.org/en/latest/reference/inheritance-mapping.html
Useful blog: https://blog.liip.ch/archive/2012/03/27/table-inheritance-with-doctrine.html
Because the objects (corresponding to the forms) to be saved in the database have a lot in common they can all be kept in just one table. The table needs to allow NULL for the fields which are not common for all forms.
In Symfony I will create a base entity class which will be extended by child entities (one child for each form). The base entity can be "abstract' as it is not supposed to be directly used (the child entity classes will be used just as regular entities ).
Because I want to have different validation on the 'amount' field (and I want to keep them in entity) I will move the 'amount' from the base class to child classes.
Also in the table there will be a column which will keep the type of the record (object typeA from formA, object typeB from formB, etc ). The equivalent of this column in Doctrine language is DiscriminatorColumn.
@ORM\DiscriminatorColumn: indicates which column will be used as discriminator (i.e. to store the type of item). You don’t have to define this column in the entity, it will be automagically created by Doctrine.
Exaple:
Base entity class:
<?php
namespace Bundle\Entity;
use Doctrine\ORM\Mapping as ORM;
use Symfony\Component\Validator\Constraints as Assert;
/**
* Base entity class
*
* @ORM\Table(name="table")
* @ORM\Entity
* @ORM\InheritanceType("SINGLE_TABLE")
* @ORM\DiscriminatorColumn(name="refund_type", type="string")
* @ORM\DiscriminatorMap( {"typeA_in_database" = "ChildEntityA", "typeB_in_database" = "ChildEntityB" } )
*/
abstract class Base
{
--------------------------------------
---------------------------------------
Child entity class
use Doctrine\ORM\Mapping as ORM;
use Symfony\Component\Validator\Constraints as Assert;
use DDRCBundle\Validator\FilesTotalSize;
/**
* @ORM\Entity
* Child
*
*/
class Child extends Base
How should I implement this? After some research the Doctrine Single Table Inheritance seems to be the answer:
Official documentation: http://docs.doctrine-project.org/en/latest/reference/inheritance-mapping.html
Useful blog: https://blog.liip.ch/archive/2012/03/27/table-inheritance-with-doctrine.html
Because the objects (corresponding to the forms) to be saved in the database have a lot in common they can all be kept in just one table. The table needs to allow NULL for the fields which are not common for all forms.
In Symfony I will create a base entity class which will be extended by child entities (one child for each form). The base entity can be "abstract' as it is not supposed to be directly used (the child entity classes will be used just as regular entities ).
Because I want to have different validation on the 'amount' field (and I want to keep them in entity) I will move the 'amount' from the base class to child classes.
Also in the table there will be a column which will keep the type of the record (object typeA from formA, object typeB from formB, etc ). The equivalent of this column in Doctrine language is DiscriminatorColumn.
@ORM\DiscriminatorColumn: indicates which column will be used as discriminator (i.e. to store the type of item). You don’t have to define this column in the entity, it will be automagically created by Doctrine.
Exaple:
Base entity class:
<?php
namespace Bundle\Entity;
use Doctrine\ORM\Mapping as ORM;
use Symfony\Component\Validator\Constraints as Assert;
/**
* Base entity class
*
* @ORM\Table(name="table")
* @ORM\Entity
* @ORM\InheritanceType("SINGLE_TABLE")
* @ORM\DiscriminatorColumn(name="refund_type", type="string")
* @ORM\DiscriminatorMap( {"typeA_in_database" = "ChildEntityA", "typeB_in_database" = "ChildEntityB" } )
*/
abstract class Base
{
--------------------------------------
---------------------------------------
Child entity class
use Doctrine\ORM\Mapping as ORM;
use Symfony\Component\Validator\Constraints as Assert;
use DDRCBundle\Validator\FilesTotalSize;
/**
* @ORM\Entity
* Child
*
*/
class Child extends Base
Monday, November 30, 2015
Debugging Symfony with XDebug
Many times the information from the Symfony Debugger is enough to figure what's the problem, but sometimes I need to debug using XDebug. When I tried to debug my code using XDebug (with Notepadd++ and DBGp plugin) I noticed that my breakpoints were ignored.. hmm weird.
Because of the caching done by Symfony those breakpoints are never reached because that code is not executed. In order to overcome this issue the app_dev.php should be changed.
Source: https://www.adayinthelifeof.nl/2014/12/31/debugging-symfony-components/
https://gist.github.com/johnkary/601b4071a4b923b22ac2
I wrote on how to use Notepad++ with XDebug on this post.
Because of the caching done by Symfony those breakpoints are never reached because that code is not executed. In order to overcome this issue the app_dev.php should be changed.
Source: https://www.adayinthelifeof.nl/2014/12/31/debugging-symfony-components/
// CHANGE: Change from bootstrap.php.cache to autoload.phpA more complex solution can be found here:
$loader = require_once __DIR__.'/../app/autoload.php';
Debug::enable();
require_once __DIR__.'/../app/AppKernel.php';
$kernel = new AppKernel('dev', true);
// CHANGE: Comment the next line to disable cache loading
//$kernel->loadClassCache();
$request = Request::createFromGlobals();
https://gist.github.com/johnkary/601b4071a4b923b22ac2
I wrote on how to use Notepad++ with XDebug on this post.
Tuesday, November 24, 2015
Symfony and Doctrine - One-to-Many Bidirectional associations
As you could saw in the two previous posts I am using 2 entities, Ads and SpecificItems, found in a One-to-Many relationship unidirectional. One Ad can have none or several SpecificItems.
What makes it unidirectional is the fact that the Many side(specific items) contain "ads" objects, but the "ads" do not know which are their specific items.
Example of specific items : 2012 (year of manufacturing of a car etc. SpecificItems holds only the value, the type name (Year of manufacturing, Color etc) is hold in a different class (entity).
Because of the unidirectional relation at this moment I am retrieving the data with 2 different queries, first query get the ads, the second one retrieve the specific items which have ads in the first query result. Bot quires return data as arrays and not as object (as I selected only some fields).
$ads = $em->getRepository('CPANAClassifiedsBundle:Ads')
->findAllAdsMyPagination($currentPage, $limit);
$allSpecificItems = $em->getRepository('CPANAClassifiedsBundle:SpecificItems')
->findItemsForAdsList($ads);
And in the view I am combining the two arrays.
I feel that I am not using really the Doctrine's capabilities by retrieving arrays instead of actual objects. It is also more natural for an Ad to contain the list of specific items.
Official documentation Doctrine associations:
http://doctrine-orm.readthedocs.org/projects/doctrine-orm/en/latest/reference/association-mapping.html
http://doctrine-orm.readthedocs.org/projects/doctrine-orm/en/latest/reference/unitofwork-associations.html
use Doctrine\Common\Collections\ArrayCollection;
........
/**
* @var integer
*
* @ORM\OneToMany(targetEntity="CPANA\ClassifiedsBundle\Entity\SpecificItems", mappedBy="idAds")
* @ORM\JoinColumn(name="items_id", referencedColumnName="id")
*/
private $items;
public function __construct() {
$this->items = new ArrayCollection();
}
public function getItems()
{
return $this->items;
}
public function addItem(\CPANA\ClassifiedsBundle\Entity\SpecificItems $item) {
$this->items[] = $item;
$item->setIdAds($this);
return $this;
}
public function removeItems($item) {
$this->items->removeElement($item);
}
In the Many side (SpecificItems entity) I make sure to specific the inversedBy clause:
/**
* @var \CPANA\ClassifiedsBundle\Entity\Ads
*
* @ORM\ManyToOne(targetEntity="CPANA\ClassifiedsBundle\Entity\Ads", inversedBy="items")
* @ORM\JoinColumns({
* @ORM\JoinColumn(name="id_ads", referencedColumnName="id_ads")
* })
*/
private $idAds;
public function findAllAdsCustom($currentPage = 1, $limit)
{
$query= $this->getEntityManager()
->createQuery(
'SELECT a,p,u,cat,c,ca,city,type
FROM CPANAClassifiedsBundle:Ads a
LEFT JOIN a.items p
LEFT JOIN a.idUser u
LEFT JOIN a.idCategory cat
LEFT JOIN a.idCurrency c
LEFT JOIN a.idCityArea ca
LEFT JOIN ca.idCity city
LEFT JOIN p.idSpecificItemType type
ORDER BY a.idAds'
);
$paginator = $this->paginate($query, $currentPage, $limit);
return $paginator;
}
public function paginate($dql, $page, $limit)
{
$paginator = new Paginator($dql);
$paginator->setUseOutputWalkers(false);
$paginator->getQuery()
->setFirstResult($limit * ($page - 1)) // Offset
->setMaxResults($limit); // Limit
return $paginator;
}
public function showAllAction($currentPage)
{
$limit = 1; // limit variable holds the number of ads per page
$em = $this->getDoctrine()->getManager();
$ads = $em->getRepository('CPANAClassifiedsBundle:Ads')->findAllAdsCustom($currentPage, $limit);
$totalAds = $ads->count();
$maxPages = ceil($totalAds/$limit);
$thisPage = $currentPage;
if (!$ads) {
throw $this->createNotFoundException('Unable to find ads.');
}
return $this->render('CPANAClassifiedsBundle:Pub\Ads:show_all.html.twig', array(
'all_ads' => $ads,
'maxPages'=>$maxPages,
'thisPage' => $thisPage,
) );
}
{% block leftcolumn %} {{ render(controller('CPANA\ClassifiedsBundle:Pub/Categories:index')) }} {% endblock %}
{% block content %}
<table style="width:100%">
{% for ad in all_ads %}
<tr><hr></tr>
<tr><h1> {{ ad.title }}</h1></tr>
<tr><p><b>Posted by: </b> {{ ad.idUser.name }}</p></tr>
<tr><p><b>Category: </b> {{ ad.idCategory.categoryName }}</p></tr>
<tr><p><b>Area: </b> {{ ad.idCityArea.cityAreaName }}</p></tr>
<tr><p><b>City: </b> {{ ad.idCityArea.idCity.cityName }}</p></tr>
<tr><h2>Description</h2></tr>
<tr><p>{{ ad.description }} </p></tr>
{% for item in ad.items %}
<tr><p><b> {{ item.idSpecificItemType.itemTypeName }} </b> - {{ item.itemValue }} </p> </tr>
{% endfor %}
<tr><p><b>Price: </b> {{ ad.price }} -- <b> {{ ad.idCurrency.currencyName }} </b> </p></tr>
{% endfor %}
</table>
{% if maxPages > 1 %}
<ul>
{%if thisPage > 1 %}
<li >
<a href="{{ path('cpana_classifieds_public_ads_show_all', {currentPage: thisPage-1 < 1 ? 1 : thisPage-1}) }}">«</a>
</li>
{% endif %}
{# Render each page number #}
{% for i in 1..maxPages %}
<li>
<a href="{{ path('cpana_classifieds_public_ads_show_all', {currentPage: i}) }}">{{ i }}</a>
</li>
{% endfor %}
{# `»` arrow #}
{%if thisPage < maxPages %}
<li>
<a href="{{ path('cpana_classifieds_public_ads_show_all', {currentPage: thisPage+1 <= maxPages ? thisPage+1 : thisPage}) }}">»</a>
</li>
{% endif %}
</ul>
{% endif %}
{% endblock %}
What makes it unidirectional is the fact that the Many side(specific items) contain "ads" objects, but the "ads" do not know which are their specific items.
Example of specific items : 2012 (year of manufacturing of a car etc. SpecificItems holds only the value, the type name (Year of manufacturing, Color etc) is hold in a different class (entity).
Because of the unidirectional relation at this moment I am retrieving the data with 2 different queries, first query get the ads, the second one retrieve the specific items which have ads in the first query result. Bot quires return data as arrays and not as object (as I selected only some fields).
$ads = $em->getRepository('CPANAClassifiedsBundle:Ads')
->findAllAdsMyPagination($currentPage, $limit);
$allSpecificItems = $em->getRepository('CPANAClassifiedsBundle:SpecificItems')
->findItemsForAdsList($ads);
And in the view I am combining the two arrays.
I feel that I am not using really the Doctrine's capabilities by retrieving arrays instead of actual objects. It is also more natural for an Ad to contain the list of specific items.
Official documentation Doctrine associations:
http://doctrine-orm.readthedocs.org/projects/doctrine-orm/en/latest/reference/association-mapping.html
http://doctrine-orm.readthedocs.org/projects/doctrine-orm/en/latest/reference/unitofwork-associations.html
1. First step - modify the entities
In the One side of the One to Many, in the Ads entity, I've added $items, which is a collection object, it will hold all the associated SpecificItems objects.use Doctrine\Common\Collections\ArrayCollection;
........
/**
* @var integer
*
* @ORM\OneToMany(targetEntity="CPANA\ClassifiedsBundle\Entity\SpecificItems", mappedBy="idAds")
* @ORM\JoinColumn(name="items_id", referencedColumnName="id")
*/
private $items;
public function __construct() {
$this->items = new ArrayCollection();
}
public function getItems()
{
return $this->items;
}
public function addItem(\CPANA\ClassifiedsBundle\Entity\SpecificItems $item) {
$this->items[] = $item;
$item->setIdAds($this);
return $this;
}
public function removeItems($item) {
$this->items->removeElement($item);
}
In the Many side (SpecificItems entity) I make sure to specific the inversedBy clause:
/**
* @var \CPANA\ClassifiedsBundle\Entity\Ads
*
* @ORM\ManyToOne(targetEntity="CPANA\ClassifiedsBundle\Entity\Ads", inversedBy="items")
* @ORM\JoinColumns({
* @ORM\JoinColumn(name="id_ads", referencedColumnName="id_ads")
* })
*/
private $idAds;
2. Modify the Repository
Make sure to add all the classes in the SELECT statement otherwise they will be loaded lazy resulting in a large number of SELECTS into the database (N+1 queries issue in ORMs). I am using the pagination capabilities from Doctrine.public function findAllAdsCustom($currentPage = 1, $limit)
{
$query= $this->getEntityManager()
->createQuery(
'SELECT a,p,u,cat,c,ca,city,type
FROM CPANAClassifiedsBundle:Ads a
LEFT JOIN a.items p
LEFT JOIN a.idUser u
LEFT JOIN a.idCategory cat
LEFT JOIN a.idCurrency c
LEFT JOIN a.idCityArea ca
LEFT JOIN ca.idCity city
LEFT JOIN p.idSpecificItemType type
ORDER BY a.idAds'
);
$paginator = $this->paginate($query, $currentPage, $limit);
return $paginator;
}
public function paginate($dql, $page, $limit)
{
$paginator = new Paginator($dql);
$paginator->setUseOutputWalkers(false);
$paginator->getQuery()
->setFirstResult($limit * ($page - 1)) // Offset
->setMaxResults($limit); // Limit
return $paginator;
}
3. The controller
No need to call a special query for retrieving the SpecificItems, evrything will be now return in the all_ads array which will contain objects of the Ads class. And these Ads objects contain now the $items collection.public function showAllAction($currentPage)
{
$limit = 1; // limit variable holds the number of ads per page
$em = $this->getDoctrine()->getManager();
$ads = $em->getRepository('CPANAClassifiedsBundle:Ads')->findAllAdsCustom($currentPage, $limit);
$totalAds = $ads->count();
$maxPages = ceil($totalAds/$limit);
$thisPage = $currentPage;
if (!$ads) {
throw $this->createNotFoundException('Unable to find ads.');
}
return $this->render('CPANAClassifiedsBundle:Pub\Ads:show_all.html.twig', array(
'all_ads' => $ads,
'maxPages'=>$maxPages,
'thisPage' => $thisPage,
) );
}
4. The view
{% extends 'CPANAClassifiedsBundle::layout.html.twig' %}{% block leftcolumn %} {{ render(controller('CPANA\ClassifiedsBundle:Pub/Categories:index')) }} {% endblock %}
{% block content %}
<table style="width:100%">
{% for ad in all_ads %}
<tr><hr></tr>
<tr><h1> {{ ad.title }}</h1></tr>
<tr><p><b>Posted by: </b> {{ ad.idUser.name }}</p></tr>
<tr><p><b>Category: </b> {{ ad.idCategory.categoryName }}</p></tr>
<tr><p><b>Area: </b> {{ ad.idCityArea.cityAreaName }}</p></tr>
<tr><p><b>City: </b> {{ ad.idCityArea.idCity.cityName }}</p></tr>
<tr><h2>Description</h2></tr>
<tr><p>{{ ad.description }} </p></tr>
{% for item in ad.items %}
<tr><p><b> {{ item.idSpecificItemType.itemTypeName }} </b> - {{ item.itemValue }} </p> </tr>
{% endfor %}
<tr><p><b>Price: </b> {{ ad.price }} -- <b> {{ ad.idCurrency.currencyName }} </b> </p></tr>
{% endfor %}
</table>
{% if maxPages > 1 %}
<ul>
{%if thisPage > 1 %}
<li >
<a href="{{ path('cpana_classifieds_public_ads_show_all', {currentPage: thisPage-1 < 1 ? 1 : thisPage-1}) }}">«</a>
</li>
{% endif %}
{# Render each page number #}
{% for i in 1..maxPages %}
<li>
<a href="{{ path('cpana_classifieds_public_ads_show_all', {currentPage: i}) }}">{{ i }}</a>
</li>
{% endfor %}
{# `»` arrow #}
{%if thisPage < maxPages %}
<li>
<a href="{{ path('cpana_classifieds_public_ads_show_all', {currentPage: thisPage+1 <= maxPages ? thisPage+1 : thisPage}) }}">»</a>
</li>
{% endif %}
</ul>
{% endif %}
{% endblock %}
5. See the results
When loading the "Show all ads" I can see them nicely paginated and also having the associated SpecificItems listed. In total 3 queries were executed to obtain the result, same as before when I had One-To-Many unidirectional relationship between ads and specific items.
Thursday, November 12, 2015
Symfony and Doctrine: Pagination
In my previous post I've wrote a custom DQL to retrieve all the ads from the database. This is not really the situation in a normal application with thousands of classifieds ads, pagination should be used to limit the number of rows returned from database and keep track of the next sets of rows.
I will first implement pagination in a similar way as I did for CPANABasicBlogBundle in this post.
Documentation:
http://doctrine-orm.readthedocs.org/projects/doctrine-orm/en/latest/tutorials/pagination.html
http://anil.io/post/41/symfony-2-and-doctrine-pagination-with-twig - tutorial
Starting with version 2.2 Doctrine ships with a Paginator for DQL queries. It has a very simple API
and implements the SPL interfaces Countable and IteratorAggregate.
The path should have a parameter "currentPage" which can be optional, and the default value is 1:
cpana_classifieds_public_ads_show_all:
path: /classifieds/ads/showall/{currentPage}
defaults: { _controller: CPANAClassifiedsBundle:Pub\Ads:showAll,currentPage:1}
2. Modify the repository :
public function findAllAdsCustom($currentPage = 1, $limit)
{
//retrieve the associated Query object with the processed DQL
$qb= $this->getEntityManager()
->createQuery(
'SELECT a.idAds, a.title, a.description, a.price
,u.name
,cat.categoryName
,c.currencyName
,ca.cityAreaName
,city.cityName
FROM CPANAClassifiedsBundle:Ads a
JOIN a.idUser u
JOIN a.idCategory cat
JOIN a.idCurrency c
JOIN a.idCityArea ca
JOIN ca.idCity city'
)
->getQuery();
$paginator = $this->paginate($query, $currentPage, $limit);
return $paginator;
}
public function paginate($dql, $page = 1, $limit = 3)
{
$paginator = new Paginator($dql);
$paginator->setUseOutputWalkers(false);
$paginator->getQuery()
->setFirstResult($limit * ($page - 1)) // Offset
->setMaxResults($limit); // Limit
return $paginator;
}
-------------------------------------------
If you try to run the code without this line " $paginator->setUseOutputWalkers(false);" you get this error "Error: Not all identifier properties can be found in the ResultSetMapping:idAds"
http://www.doctrine-project.org/jira/browse/DDC-1927
http://stackoverflow.com/questions/24837913/doctrine-2-pagination
https://groups.google.com/forum/#!topic/doctrine-user/AVbeIyaGvfQ
3. In the controller add:
....
use Doctrine\ORM\Tools\Pagination\Paginator
...
public function showAllAction($currentPage)
{
$limit = 3;
$em = $this->getDoctrine()->getManager();
$ads = $em->getRepository('CPANAClassifiedsBundle:Ads')->findAllAdsCustom($currentPage, $limit);
$allSpecificItems = $em->getRepository('CPANAClassifiedsBundle:SpecificItems')->findAllItemsCustom();
$maxPages = ceil($ads->count()/$limit);
$thisPage = $currentPage;
if (!$ads) {
throw $this->createNotFoundException('Unable to find ads.');
}
return $this->render('CPANAClassifiedsBundle:Pub\Ads:show_all.html.twig', array(
'all_ads' => $ads,
'maxPages'=>$maxPages,
'thisPage' => $thisPage,
'all_items' => $allSpecificItems
) );
}
----------------------
4. View
-------
{% block content %}
<table style="width:100%">
{% for ad in all_ads %}
<tr><hr></tr>
<tr><h1> {{ ad['title'] }}</h1></tr>
<tr><p><b>Posted by: </b> {{ ad['name'] }}</p></tr>
.....
</table>
{% if maxPages > 1 %}
<ul>
{%if thisPage > 1 %}
<li >
<a href="{{ path('cpana_classifieds_public_ads_show_all', {currentPage: thisPage-1 < 1 ? 1 : thisPage-1}) }}">«</a>
</li>
{% endif %}
{# Render each page number #}
{% for i in 1..maxPages %}
<li>
<a href="{{ path('cpana_classifieds_public_ads_show_all', {currentPage: i}) }}">{{ i }}</a>
</li>
{% endfor %}
{# `»` arrow #}
{%if thisPage < maxPages %}
<li>
<a href="{{ path('cpana_classifieds_public_ads_show_all', {currentPage: thisPage+1 <= maxPages ? thisPage+1 : thisPage}) }}">»</a>
</li>
{% endif %}
</ul>
{% endif %}
{% endblock %}
I have only 6 ads so I modified the limit=1 to get the following:
--------------------------------------------------------------------------------------------------
http://doctrine-orm.readthedocs.org/projects/doctrine-orm/en/latest/reference/dql-doctrine-query-language.html
1. Modify AdsRepository.php by adding:
public function findAllAdsMyPagination($currentPage = 1, $limit)
{
$query= $this->getEntityManager()
->createQuery(
'SELECT a.idAds, a.title, a.description, a.price
,u.name
,cat.categoryName
,c.currencyName
,ca.cityAreaName
,city.cityName
FROM CPANAClassifiedsBundle:Ads a
JOIN a.idUser u
JOIN a.idCategory cat
JOIN a.idCurrency c
JOIN a.idCityArea ca
JOIN ca.idCity city'
);
$query->setFirstResult($limit * ($currentPage - 1)) // Offset
->setMaxResults($limit); // Limit
$result = $query->getResult();
return $result;
}
public function countAllAds()
{
$query= $this->getEntityManager()
->createQuery(
'SELECT count(a.idAds) FROM CPANAClassifiedsBundle:Ads a');
$count = $query->getSingleScalarResult();;
return $count;
}
Note: As you can see I've added a new method which is counting all the records in ads table.
2. Modify controller to use the new method findAllAdsMyPagination():
public function showAllAction($currentPage)
{
$limit = 3;
$em = $this->getDoctrine()->getManager();
$ads = $em->getRepository('CPANAClassifiedsBundle:Ads')->findAllAdsMyPagination($currentPage, $limit);
$totalAds = $em->getRepository('CPANAClassifiedsBundle:Ads')->countAllAds();
$allSpecificItems = $em->getRepository('CPANAClassifiedsBundle:SpecificItems')->findAllItemsCustom();
$maxPages = ceil($totalAds/$limit);
$thisPage = $currentPage;
if (!$ads) {
throw $this->createNotFoundException('Unable to find ads.');
}
return $this->render('CPANAClassifiedsBundle:Pub\Ads:show_all.html.twig', array(
'all_ads' => $ads,
'maxPages'=>$maxPages,
'thisPage' => $thisPage,
'all_items' => $allSpecificItems
) );
}
-----------------------
Nothing changes in the view
-------------------------------
1. In "SpecificItemsRepository" I created a new method which receive as parameter a list of ads as an array of arrays.
public function findItemsForAdsList($ads)
{
$ads_id_list = array();
foreach($ads as $ad) {
$ads_id_list[]=$ad['idAds'];
}
$query = $this->getEntityManager()
->createQuery(
'SELECT si.itemValue
,IDENTITY(si.idAds)
,ty.itemTypeName
FROM CPANAClassifiedsBundle:SpecificItems si
JOIN si.idSpecificItemType ty
WHERE IDENTITY(si.idAds) IN (:list)'
);
$query->setParameter('list', $ads_id_list);
return $query->getResult();
}
------
2. In controller just replace FindAllItemsCustom() with
$allSpecificItems = $em->getRepository('CPANAClassifiedsBundle:SpecificItems')->findItemsForAdsList($ads);
3. Nothing changes in the view.
The SQL queries generated by Doctrine are the following:
I will first implement pagination in a similar way as I did for CPANABasicBlogBundle in this post.
Documentation:
http://doctrine-orm.readthedocs.org/projects/doctrine-orm/en/latest/tutorials/pagination.html
http://anil.io/post/41/symfony-2-and-doctrine-pagination-with-twig - tutorial
Starting with version 2.2 Doctrine ships with a Paginator for DQL queries. It has a very simple API
and implements the SPL interfaces Countable and IteratorAggregate.
I. Pagination using Paginator class
1. First modify the path.The path should have a parameter "currentPage" which can be optional, and the default value is 1:
cpana_classifieds_public_ads_show_all:
path: /classifieds/ads/showall/{currentPage}
defaults: { _controller: CPANAClassifiedsBundle:Pub\Ads:showAll,currentPage:1}
2. Modify the repository :
public function findAllAdsCustom($currentPage = 1, $limit)
{
//retrieve the associated Query object with the processed DQL
$qb= $this->getEntityManager()
->createQuery(
'SELECT a.idAds, a.title, a.description, a.price
,u.name
,cat.categoryName
,c.currencyName
,ca.cityAreaName
,city.cityName
FROM CPANAClassifiedsBundle:Ads a
JOIN a.idUser u
JOIN a.idCategory cat
JOIN a.idCurrency c
JOIN a.idCityArea ca
JOIN ca.idCity city'
)
->getQuery();
$paginator = $this->paginate($query, $currentPage, $limit);
return $paginator;
}
public function paginate($dql, $page = 1, $limit = 3)
{
$paginator = new Paginator($dql);
$paginator->setUseOutputWalkers(false);
$paginator->getQuery()
->setFirstResult($limit * ($page - 1)) // Offset
->setMaxResults($limit); // Limit
return $paginator;
}
-------------------------------------------
If you try to run the code without this line " $paginator->setUseOutputWalkers(false);" you get this error "Error: Not all identifier properties can be found in the ResultSetMapping:idAds"
http://www.doctrine-project.org/jira/browse/DDC-1927
http://stackoverflow.com/questions/24837913/doctrine-2-pagination
https://groups.google.com/forum/#!topic/doctrine-user/AVbeIyaGvfQ
3. In the controller add:
....
use Doctrine\ORM\Tools\Pagination\Paginator
...
public function showAllAction($currentPage)
{
$limit = 3;
$em = $this->getDoctrine()->getManager();
$ads = $em->getRepository('CPANAClassifiedsBundle:Ads')->findAllAdsCustom($currentPage, $limit);
$allSpecificItems = $em->getRepository('CPANAClassifiedsBundle:SpecificItems')->findAllItemsCustom();
$maxPages = ceil($ads->count()/$limit);
$thisPage = $currentPage;
if (!$ads) {
throw $this->createNotFoundException('Unable to find ads.');
}
return $this->render('CPANAClassifiedsBundle:Pub\Ads:show_all.html.twig', array(
'all_ads' => $ads,
'maxPages'=>$maxPages,
'thisPage' => $thisPage,
'all_items' => $allSpecificItems
) );
}
----------------------
4. View
-------
{% block content %}
<table style="width:100%">
{% for ad in all_ads %}
<tr><hr></tr>
<tr><h1> {{ ad['title'] }}</h1></tr>
<tr><p><b>Posted by: </b> {{ ad['name'] }}</p></tr>
.....
</table>
{% if maxPages > 1 %}
<ul>
{%if thisPage > 1 %}
<li >
<a href="{{ path('cpana_classifieds_public_ads_show_all', {currentPage: thisPage-1 < 1 ? 1 : thisPage-1}) }}">«</a>
</li>
{% endif %}
{# Render each page number #}
{% for i in 1..maxPages %}
<li>
<a href="{{ path('cpana_classifieds_public_ads_show_all', {currentPage: i}) }}">{{ i }}</a>
</li>
{% endfor %}
{# `»` arrow #}
{%if thisPage < maxPages %}
<li>
<a href="{{ path('cpana_classifieds_public_ads_show_all', {currentPage: thisPage+1 <= maxPages ? thisPage+1 : thisPage}) }}">»</a>
</li>
{% endif %}
</ul>
{% endif %}
{% endblock %}
I have only 6 ads so I modified the limit=1 to get the following:
--------------------------------------------------------------------------------------------------
II. Pagination directly from DQL
http://doctrine-orm.readthedocs.org/projects/doctrine-orm/en/latest/reference/dql-doctrine-query-language.html
14.7.6.5. First and Max Result Items (DQL Query Only)---------------------------------------------------------------------------------------------------------
You can limit the number of results returned from a DQL query as well as specify the starting offset, Doctrine then uses a strategy of manipulating the select query to return only the requested number of results:
Query::setMaxResults($maxResults)
Query::setFirstResult($offset)
If your query contains a fetch-joined collection specifying the result limit methods are not working as you would expect. Set Max Results restricts the number of database result rows, however in the case of fetch-joined collections one root entity might appear in many rows, effectively hydrating less than the specified number of results.
1. Modify AdsRepository.php by adding:
public function findAllAdsMyPagination($currentPage = 1, $limit)
{
$query= $this->getEntityManager()
->createQuery(
'SELECT a.idAds, a.title, a.description, a.price
,u.name
,cat.categoryName
,c.currencyName
,ca.cityAreaName
,city.cityName
FROM CPANAClassifiedsBundle:Ads a
JOIN a.idUser u
JOIN a.idCategory cat
JOIN a.idCurrency c
JOIN a.idCityArea ca
JOIN ca.idCity city'
);
$query->setFirstResult($limit * ($currentPage - 1)) // Offset
->setMaxResults($limit); // Limit
$result = $query->getResult();
return $result;
}
public function countAllAds()
{
$query= $this->getEntityManager()
->createQuery(
'SELECT count(a.idAds) FROM CPANAClassifiedsBundle:Ads a');
$count = $query->getSingleScalarResult();;
return $count;
}
Note: As you can see I've added a new method which is counting all the records in ads table.
2. Modify controller to use the new method findAllAdsMyPagination():
public function showAllAction($currentPage)
{
$limit = 3;
$em = $this->getDoctrine()->getManager();
$ads = $em->getRepository('CPANAClassifiedsBundle:Ads')->findAllAdsMyPagination($currentPage, $limit);
$totalAds = $em->getRepository('CPANAClassifiedsBundle:Ads')->countAllAds();
$allSpecificItems = $em->getRepository('CPANAClassifiedsBundle:SpecificItems')->findAllItemsCustom();
$maxPages = ceil($totalAds/$limit);
$thisPage = $currentPage;
if (!$ads) {
throw $this->createNotFoundException('Unable to find ads.');
}
return $this->render('CPANAClassifiedsBundle:Pub\Ads:show_all.html.twig', array(
'all_ads' => $ads,
'maxPages'=>$maxPages,
'thisPage' => $thisPage,
'all_items' => $allSpecificItems
) );
}
-----------------------
Nothing changes in the view
-------------------------------
III. Loading information from the many side of the Many-To-One relation.
Now let's improve also the way I retrieve the associated SpecificItems. At this moment I retrieve the entire table, but I should load only the values for the set of ads listed on one page (after applying pagination).
1. In "SpecificItemsRepository" I created a new method which receive as parameter a list of ads as an array of arrays.
public function findItemsForAdsList($ads)
{
$ads_id_list = array();
foreach($ads as $ad) {
$ads_id_list[]=$ad['idAds'];
}
$query = $this->getEntityManager()
->createQuery(
'SELECT si.itemValue
,IDENTITY(si.idAds)
,ty.itemTypeName
FROM CPANAClassifiedsBundle:SpecificItems si
JOIN si.idSpecificItemType ty
WHERE IDENTITY(si.idAds) IN (:list)'
);
$query->setParameter('list', $ads_id_list);
return $query->getResult();
}
------
2. In controller just replace FindAllItemsCustom() with
$allSpecificItems = $em->getRepository('CPANAClassifiedsBundle:SpecificItems')->findItemsForAdsList($ads);
3. Nothing changes in the view.
The SQL queries generated by Doctrine are the following:
Saturday, November 7, 2015
Doctrine tutorials & Doctrine performance: Eager loading vs custom DQL
Some good articles to understand Doctrine, starting with relations between objects to performance (lazy loading vs eager loading), good practices.
http://www.krueckeberg.org/notes/relationships.html - Doctrine's objects relationships explained
http://www.krueckeberg.org/notes/d2.html - other important aspects
http://www.uvd.co.uk/blog/some-doctrine-2-best-practices/
https://tideways.io/profiler/blog/5-doctrine-orm-performance-traps-you-should-avoid
OK, now let's see how my classifieds bundle can benefit from all this knowledge :)
Requirement: display all ads.
Ads table contains fields like title, description price, a FK pointing to the currency table, FK for category, FK for SpecificItems.
SpecificItems table- contains the Value(a value can pe 1992 for Type "Year of manufacture") and a FK pointing to SpecificItemsTypes
SpecificItemsTypes table- contains the SpecificItemType name
Example of specific items types: cars ads should have as specific item type "year of manufacture", "color", "cylindrical capacity" etcetera.
In controller I retrieve all data from Ads and SpecificItems using built in findAll() method from Doctrine.
public function showAllAction()
{
$em = $this->getDoctrine()->getEntityManager();
$allAds = $em->getRepository('CPANAClassifiedsBundle:Ads')->findAll();
$allSpecificItems = $em->getRepository('CPANAClassifiedsBundle:SpecificItems')->findAll();
if (!$allAds) {
throw $this->createNotFoundException('Unable to find categories.');
}
return $this->render('CPANAClassifiedsBundle:Pub\Ads:show_all.html.twig', array( 'all_ads' => $allAds, 'all_items' => $allSpecificItems ) );
}
In the view I am joining the information from 2 tables (Ads and SpecificItems) by the common Ads.id value. For the moment the scope here is just to see some data on the screen using Doctrine generated entities.
view:
{% for ad in all_ads %}
<tr><hr></tr>
<tr><h1> {{ ad.getTitle() }}</h1></tr>
<tr><p><b>Posted by: </b> {{ ad.getIdUser().getName() }}</p></tr>
<tr><p><b>Category: </b> {{ ad.getIdCategory().getCategoryName() }}</p></tr>
<tr><p><b>Area: </b> {{ ad.getIdCityArea().getCityAreaName() }}</p></tr>
<tr><p><b>City: </b> {{ ad.getIdCityArea().getIdCity.getCityName() }}</p></tr>
<tr><h2>Description</h2></tr>
<tr><p>{{ ad.getDescription() }} </p></tr>
{% for item in all_items %}
{% if item.getIdSpecificItem() == ad.getIdAds() %}
<tr><p><b>{{ item.getIdSpecificItemType().getItemTypeName() }} </b> -- {{ item.getItemValue() }} </p></tr>
{% endif %}
{% endfor %}
<tr><p><b>Price: </b> {{ ad.getPrice() }} -- <b> {{ ad.getIdCurrency().getCurrencyName() }} </b> </p></tr>
{% endfor %}
-------------------------------------------------
It works! Just that the smart profiler from Symfony is showing to me that I did 13 queries in order to display my 2 ads from Database!!!!Weird, I thought I did only 2 queries????
Click on the icon and Symfony is giving more details:
Let's see what happened:
I queried first the "ads" table and secondly the "specific_items" as I was expecting from doing it in the controller.
Next I queried the User table. Well not me, Doctrine was smart enough to do that by lazy loading the User object from the Database when I asked in the view for "ad.getIdUser().getName()". Doctrine is not loading by default the information from dependent tables because sometimes it will result in some massive data load, slowing down the application. On the other hand doing 13 separate queries is also bad for performance.
If I delete this line "ad.getIdUser().getName()" from view we will see that Doctrine will not query any more the table Users for getting the associated values.
From documentation on how to query objects:
http://doctrine-orm.readthedocs.org/en/latest/reference/annotations-reference.html#manytoone
Defines that the annotated instance variable holds a reference that describes a many-to-one
relationship between two entities.
Required attributes:
targetEntity: FQCN of the referenced target entity. Can be the unqualified class name if both classes are in the same namespace. IMPORTANT: No leading backslash!
Optional attributes:
cascade: Cascade Option
fetch: One of LAZY or EAGER
inversedBy - The inversedBy attribute designates the field in the entity that is the inverse side of the relationship.
Example:
<?php
/**
* @ManyToOne(targetEntity="Cart", cascade={"all"}, fetch="EAGER")
*/
private $cart;
In my case I want to fetch eager the User object associated with the ad.
/**
* @var \AppBundle\Entity\User
*
* @ORM\ManyToOne(targetEntity="AppBundle\Entity\User", fetch="EAGER")
* @ORM\JoinColumns({
* @ORM\JoinColumn(name="id_user", referencedColumnName="id")
* })
*/
private $idUser;
Load the page and magic => 11 queries executed (instead of 13).
Looking into profiler details we can see doctrine generated a left join SQL query between ads and fos_user table.
adding - fetch="EAGER" - to several more fields found in a many-to-one relation:
- in "Ads" entity f: idCurrency, idCityArea, idCategory
- in "CityAreas" entity: idCity
- in "SpecificItems" entity: idSpecificItemType
Load the page again and great news! Only 3 queries
OK, now let's see how it scales, I copied the ads in phpMyAdmin and now I have 6 of them, when loading the page the number of queries stays constant: 3! Most of the action happens in the below query, there are another 2: one join between city_areas and cities, and one join between specific items and specific items types.
I created a "Repository" directory under "Entity" here I will add 2 files "AdsRepository.php" and "SpecificItemsRepository.php"
Do not forget to mention in each entity class to use the repository:
OK, now let's write the query for retrieving information from Ads object and it's associated objects:
CPANA/ClassifiedsBundle/Entity/Repository/AdsRepository.php
Second query for retrieving information from SpecificItems object:
CPANA/ClassifiedsBundle/Entity/Repository/SpecificItemsRepository.php
I am using IDENTITY function for not loading the associated object, just the foreign key value. Initially I used just si.idAds which resulted in an error "...Error: Invalid PathExpression. Must be a StateFieldPathExpression..."
When I try to load the page, other error:
"Key "idAds" for array with keys "itemValue, 1, itemTypeName" does not exist in CPANAClassifiedsBundle:Pub\Ads:show_all.html.twig at line 16 "
So there is no key "idAds" using IDENTITY it returns the value at key "1".
The view looks like this now:
Writing custom DQL seems to be the the best way to use Doctrine, it takes a little bit more time to implement but you are controlling better how Doctrine behaves.
http://www.krueckeberg.org/notes/relationships.html - Doctrine's objects relationships explained
http://www.krueckeberg.org/notes/d2.html - other important aspects
http://www.uvd.co.uk/blog/some-doctrine-2-best-practices/
https://tideways.io/profiler/blog/5-doctrine-orm-performance-traps-you-should-avoid
OK, now let's see how my classifieds bundle can benefit from all this knowledge :)
Requirement: display all ads.
Ads table contains fields like title, description price, a FK pointing to the currency table, FK for category, FK for SpecificItems.
SpecificItems table- contains the Value(a value can pe 1992 for Type "Year of manufacture") and a FK pointing to SpecificItemsTypes
SpecificItemsTypes table- contains the SpecificItemType name
Example of specific items types: cars ads should have as specific item type "year of manufacture", "color", "cylindrical capacity" etcetera.
In controller I retrieve all data from Ads and SpecificItems using built in findAll() method from Doctrine.
public function showAllAction()
{
$em = $this->getDoctrine()->getEntityManager();
$allAds = $em->getRepository('CPANAClassifiedsBundle:Ads')->findAll();
$allSpecificItems = $em->getRepository('CPANAClassifiedsBundle:SpecificItems')->findAll();
if (!$allAds) {
throw $this->createNotFoundException('Unable to find categories.');
}
return $this->render('CPANAClassifiedsBundle:Pub\Ads:show_all.html.twig', array( 'all_ads' => $allAds, 'all_items' => $allSpecificItems ) );
}
In the view I am joining the information from 2 tables (Ads and SpecificItems) by the common Ads.id value. For the moment the scope here is just to see some data on the screen using Doctrine generated entities.
view:
{% for ad in all_ads %}
<tr><hr></tr>
<tr><h1> {{ ad.getTitle() }}</h1></tr>
<tr><p><b>Posted by: </b> {{ ad.getIdUser().getName() }}</p></tr>
<tr><p><b>Category: </b> {{ ad.getIdCategory().getCategoryName() }}</p></tr>
<tr><p><b>Area: </b> {{ ad.getIdCityArea().getCityAreaName() }}</p></tr>
<tr><p><b>City: </b> {{ ad.getIdCityArea().getIdCity.getCityName() }}</p></tr>
<tr><h2>Description</h2></tr>
<tr><p>{{ ad.getDescription() }} </p></tr>
{% for item in all_items %}
{% if item.getIdSpecificItem() == ad.getIdAds() %}
<tr><p><b>{{ item.getIdSpecificItemType().getItemTypeName() }} </b> -- {{ item.getItemValue() }} </p></tr>
{% endif %}
{% endfor %}
<tr><p><b>Price: </b> {{ ad.getPrice() }} -- <b> {{ ad.getIdCurrency().getCurrencyName() }} </b> </p></tr>
{% endfor %}
-------------------------------------------------
It works! Just that the smart profiler from Symfony is showing to me that I did 13 queries in order to display my 2 ads from Database!!!!Weird, I thought I did only 2 queries????
Click on the icon and Symfony is giving more details:
Let's see what happened:
I queried first the "ads" table and secondly the "specific_items" as I was expecting from doing it in the controller.
Next I queried the User table. Well not me, Doctrine was smart enough to do that by lazy loading the User object from the Database when I asked in the view for "ad.getIdUser().getName()". Doctrine is not loading by default the information from dependent tables because sometimes it will result in some massive data load, slowing down the application. On the other hand doing 13 separate queries is also bad for performance.
If I delete this line "ad.getIdUser().getName()" from view we will see that Doctrine will not query any more the table Users for getting the associated values.
From documentation on how to query objects:
7.8.4. By Eager Loading
Whenever you query for an entity that has persistent associations and these associations are mapped as EAGER, they will automatically be loaded together with the entity being queried and is thus immediately available to your application.
7.8.5. By Lazy Loading
Whenever you have a managed entity instance at hand, you can traverse and use any associations of that entity that are configured LAZY as if they were in-memory already. Doctrine will automatically load the associated objects on demand through the concept of lazy-loading.
Possible solution 1: change fetch mode to EAGER in many-to-one unidirectional relationships
From documentation:http://doctrine-orm.readthedocs.org/en/latest/reference/annotations-reference.html#manytoone
Defines that the annotated instance variable holds a reference that describes a many-to-one
relationship between two entities.
Required attributes:
targetEntity: FQCN of the referenced target entity. Can be the unqualified class name if both classes are in the same namespace. IMPORTANT: No leading backslash!
Optional attributes:
cascade: Cascade Option
fetch: One of LAZY or EAGER
inversedBy - The inversedBy attribute designates the field in the entity that is the inverse side of the relationship.
Example:
<?php
/**
* @ManyToOne(targetEntity="Cart", cascade={"all"}, fetch="EAGER")
*/
private $cart;
In my case I want to fetch eager the User object associated with the ad.
/**
* @var \AppBundle\Entity\User
*
* @ORM\ManyToOne(targetEntity="AppBundle\Entity\User", fetch="EAGER")
* @ORM\JoinColumns({
* @ORM\JoinColumn(name="id_user", referencedColumnName="id")
* })
*/
private $idUser;
Load the page and magic => 11 queries executed (instead of 13).
Looking into profiler details we can see doctrine generated a left join SQL query between ads and fos_user table.
adding - fetch="EAGER" - to several more fields found in a many-to-one relation:
- in "Ads" entity f: idCurrency, idCityArea, idCategory
- in "CityAreas" entity: idCity
- in "SpecificItems" entity: idSpecificItemType
Load the page again and great news! Only 3 queries
OK, now let's see how it scales, I copied the ads in phpMyAdmin and now I have 6 of them, when loading the page the number of queries stays constant: 3! Most of the action happens in the below query, there are another 2: one join between city_areas and cities, and one join between specific items and specific items types.
Possible solution 2: write custom DQL queries
First delete the fetch="EAGER" from the entities modified previously. Loading the page and we have again 13 queries.I created a "Repository" directory under "Entity" here I will add 2 files "AdsRepository.php" and "SpecificItemsRepository.php"
Do not forget to mention in each entity class to use the repository:
"Ads.php"
/**
* Ads
*
* @ORM\Table(name="ads", indexes={@ORM\Index(name="fk_id_user", columns={"id_user"}), @ORM\Index(name="fk_id_city_area", columns={"id_city_area"}), @ORM\Index(name="fk_id_currency", columns={"id_currency"})})
* @ORM\Entity(repositoryClass="CPANA\ClassifiedsBundle\Entity\Repository\AdsRepository")
*/
OK, now let's write the query for retrieving information from Ads object and it's associated objects:
CPANA/ClassifiedsBundle/Entity/Repository/AdsRepository.php
public function findAllAdsCustom()
{
return $this->getEntityManager()
->createQuery(
'SELECT a.idAds, a.title, a.description, a.price
,u.name
,cat.categoryName
,c.currencyName
,ca.cityAreaName
,city.cityName
FROM CPANAClassifiedsBundle:Ads a
JOIN a.idUser u
JOIN a.idCategory cat
JOIN a.idCurrency c
JOIN a.idCityArea ca
JOIN ca.idCity city'
)
->getResult();
}
Second query for retrieving information from SpecificItems object:
CPANA/ClassifiedsBundle/Entity/Repository/SpecificItemsRepository.php
public function findAllItemsCustom()
{
return $this->getEntityManager()
->createQuery(
'SELECT si.itemValue
,IDENTITY(si.idAds)
,ty.itemTypeName
FROM CPANAClassifiedsBundle:SpecificItems si
JOIN si.idSpecificItemType ty'
)
->getResult();
}
I am using IDENTITY function for not loading the associated object, just the foreign key value. Initially I used just si.idAds which resulted in an error "...Error: Invalid PathExpression. Must be a StateFieldPathExpression..."
When I try to load the page, other error:
"Key "idAds" for array with keys "itemValue, 1, itemTypeName" does not exist in CPANAClassifiedsBundle:Pub\Ads:show_all.html.twig at line 16 "
So there is no key "idAds" using IDENTITY it returns the value at key "1".
The view looks like this now:
<table style="width:100%">And in the controller I am calling the 2 methods from repositories:
{% for ad in all_ads %}
<tr><hr></tr>
<tr><h1> {{ ad['title'] }}</h1></tr>
<tr><p><b>Posted by: </b> {{ ad['name'] }}</p></tr>
<tr><p><b>Category: </b> {{ ad['categoryName'] }}</p></tr>
<tr><p><b>Area: </b> {{ ad['cityAreaName'] }}</p></tr>
<tr><p><b>City: </b> {{ ad['cityName'] }}</p></tr>
<tr><h2>Description</h2></tr>
<tr><p>{{ ad['description'] }} </p></tr>
{% for item in all_items %}
{% if item[1] == ad['idAds'] %}
<tr><p><b>{{ item['itemTypeName'] }} </b> -- {{ item['itemValue'] }} </p></tr>
{% endif %}
{% endfor %}
<tr><p><b>Price: </b> {{ ad['price'] }} -- <b> {{ ad['currencyName'] }} </b> </p></tr>
{% endfor %}
</table>
public function showAllAction()
{When I load the page again I can see that only 2 SQL queries were executed:
$em = $this->getDoctrine()->getEntityManager();
$allAds = $em->getRepository('CPANAClassifiedsBundle:Ads')->findAllAdsCustom();
$allSpecificItems = $em->getRepository('CPANAClassifiedsBundle:SpecificItems')->findAllItemsCustom();
if (!$allAds) {
throw $this->createNotFoundException('Unable to find categories.');
}
return $this->render('CPANAClassifiedsBundle:Pub\Ads:show_all.html.twig', array( 'all_ads' => $allAds, 'all_items' => $allSpecificItems ) );
}
3. Conclusion:
Writing custom DQL seems to be the the best way to use Doctrine, it takes a little bit more time to implement but you are controlling better how Doctrine behaves.
Sunday, November 1, 2015
Generate Entities from an Existing Database with Symfony and Doctrine
As said in a previous post, I am trying to see how easy is to create a Symfony bundle starting from an existing Database (like when you want to migrate an old app to Symfony). From Symfony documentation:
Below the steps I made to generate entities from existing tables in database:
1. Change current path to home directory of my Symfony installation.
Execute this command:
php app/console doctrine:mapping:import --force CPANAClassifiedsBundle xml
The metadata files are generated under "/src/CPANAClassifiedsBundle/Resources/config/doctrine/". Delete .orm.xml files related to other tables than the ones related to your bundle
2. Once the metadata files are generated, you can ask Doctrine to build related entity classes by executing the following two commands.
php app/console doctrine:mapping:convert annotation ./src/CPANA/ClassifiedsBundle
check if the data was exported into the correct path otherwise copy it to Entity directory.
php app/console doctrine:generate:entities CPANAClassifiedsBundle
NOTE: If you want to have a one-to-many relationship, you will need to add it manually into the entity or to the generated XML or YAML files. Add a section on the specific entities for one-to-many defining the inversedBy and the mappedBy pieces.
http://stackoverflow.com/questions/12493865/what-is-the-difference-between-inversedby-and-mappedby
3. Delete the mapping generated by Doctrine in the directory "/src/CPANA/ClassifiedsBundle/Resources/config/doctrine".
You may need to clear the cache:
/**
* @var \AppBundle\Entity\User
*
* @ORM\ManyToOne(targetEntity="AppBundle\Entity\User")
* @ORM\JoinColumns({
* @ORM\JoinColumn(name="id_user", referencedColumnName="id")
* })
*/
private $idUser;
and
public function setIdUser(\AppBundle\Entity\User $idUser = null)
1. Add a new route to routing.yml
cpana_classifieds_categories_index:
path: /classifieds/categories
defaults: { _controller: CPANAClassifiedsBundle:Categories:index }
2. Add controller class CategoriesController.php:
class CategoriesController extends Controller
{
public function indexAction()
{
$em = $this->getDoctrine()->getEntityManager();
$allCategories = $em->getRepository('CPANAClassifiedsBundle:Category')->findAll();
if (!$allCategories) {
throw $this->createNotFoundException('Unable to find categories.');
}
return $this->render('CPANAClassifiedsBundle:Categories:index.html.twig', array( 'all_cat' => $allCategories ) );
}
}
3. Add the index.html.twig view:
{% extends 'CPANAClassifiedsBundle::layout.html.twig' %}
{% block content %}
<table style="width:100%">
{% for category in all_cat %}
<tr>
<td>{{ category.getIdCategory() }}</td>
<td>{{ category.getCategoryName() }}</td>
{% if category.getIdParent() != false %}
<td>{{ category.getIdParent().getCategoryName() }}</td>
{% else %}
<td>root category</td>
{% endif %}
</tr>
{% endfor %}
</table>
{% endblock %}
--------------------------------------------------------------------------------------------------------------
Some explanations:
The foreign keys from my tables are transformed in objects holding the entire row for that foreign key id. Results from this that category.getIdParent() is returning an object. If you try to display that object you will get an error like "Catchable Fatal Error: "Object of class CPANA\ClassifiedsBundle\Entity\Category could not be converted to string"
So I will be displaying a certain property of that object, let's say CategoryName using the getter: "category.getIdParent().getCategoryName() ".
I had a problem because Doctrine transformed the column "parent_id" into "parent" field which is not good cause "parent" is a key word used by PHP. I had to alter the table and generate again the entities. I suppose Doctrine transformed "parent_id" to "parent" because of the default mapping logic:
As the Doctrine tools documentation says, reverse engineering is a one-time process to get started on a project. Doctrine is able to convert approximately 70-80% of the necessary mapping information based on fields, indexes and foreign key constraints. Doctrine can't discover inverse associations, inheritance types, entities with foreign keys as primary keys or semantical operations on associations such as cascade or lifecycle events. Some additional work on the generated entities will be necessary afterwards to design each to fit your domain model specificities.
Below the steps I made to generate entities from existing tables in database:
1. Change current path to home directory of my Symfony installation.
Execute this command:
php app/console doctrine:mapping:import --force CPANAClassifiedsBundle xml
The metadata files are generated under "/src/CPANAClassifiedsBundle/Resources/config/doctrine/". Delete .orm.xml files related to other tables than the ones related to your bundle
2. Once the metadata files are generated, you can ask Doctrine to build related entity classes by executing the following two commands.
php app/console doctrine:mapping:convert annotation ./src/CPANA/ClassifiedsBundle
check if the data was exported into the correct path otherwise copy it to Entity directory.
php app/console doctrine:generate:entities CPANAClassifiedsBundle
NOTE: If you want to have a one-to-many relationship, you will need to add it manually into the entity or to the generated XML or YAML files. Add a section on the specific entities for one-to-many defining the inversedBy and the mappedBy pieces.
http://stackoverflow.com/questions/12493865/what-is-the-difference-between-inversedby-and-mappedby
3. Delete the mapping generated by Doctrine in the directory "/src/CPANA/ClassifiedsBundle/Resources/config/doctrine".
You may need to clear the cache:
php app/console doctrine:cache:clear-metadataAlso you need to modify the Ads entity to correctly point to your User entity. In my case:
/**
* @var \AppBundle\Entity\User
*
* @ORM\ManyToOne(targetEntity="AppBundle\Entity\User")
* @ORM\JoinColumns({
* @ORM\JoinColumn(name="id_user", referencedColumnName="id")
* })
*/
private $idUser;
and
public function setIdUser(\AppBundle\Entity\User $idUser = null)
Testing how the model classes (entities) generated by Doctrine work
1. Add a new route to routing.yml
cpana_classifieds_categories_index:
path: /classifieds/categories
defaults: { _controller: CPANAClassifiedsBundle:Categories:index }
2. Add controller class CategoriesController.php:
class CategoriesController extends Controller
{
public function indexAction()
{
$em = $this->getDoctrine()->getEntityManager();
$allCategories = $em->getRepository('CPANAClassifiedsBundle:Category')->findAll();
if (!$allCategories) {
throw $this->createNotFoundException('Unable to find categories.');
}
return $this->render('CPANAClassifiedsBundle:Categories:index.html.twig', array( 'all_cat' => $allCategories ) );
}
}
3. Add the index.html.twig view:
{% extends 'CPANAClassifiedsBundle::layout.html.twig' %}
{% block content %}
<table style="width:100%">
{% for category in all_cat %}
<tr>
<td>{{ category.getIdCategory() }}</td>
<td>{{ category.getCategoryName() }}</td>
{% if category.getIdParent() != false %}
<td>{{ category.getIdParent().getCategoryName() }}</td>
{% else %}
<td>root category</td>
{% endif %}
</tr>
{% endfor %}
</table>
{% endblock %}
--------------------------------------------------------------------------------------------------------------
Some explanations:
The foreign keys from my tables are transformed in objects holding the entire row for that foreign key id. Results from this that category.getIdParent() is returning an object. If you try to display that object you will get an error like "Catchable Fatal Error: "Object of class CPANA\ClassifiedsBundle\Entity\Category could not be converted to string"
So I will be displaying a certain property of that object, let's say CategoryName using the getter: "category.getIdParent().getCategoryName() ".
I had a problem because Doctrine transformed the column "parent_id" into "parent" field which is not good cause "parent" is a key word used by PHP. I had to alter the table and generate again the entities. I suppose Doctrine transformed "parent_id" to "parent" because of the default mapping logic:
5.11. Mapping Defaults
The @JoinColumn and @JoinTable definitions are usually optional and have sensible default values.
The defaults for a join column in a one-to-one/many-to-one association is as follows:
name: "<fieldname>_id"
referencedColumnName: "id"
As an example, consider this mapping:
PHP
<?php
/** @OneToOne(targetEntity="Shipping") **/
private $shipping;
XML
YAML
This is essentially the same as the following, more verbose, mapping:
PHP
<?php
/**
* @OneToOne(targetEntity="Shipping")
* @JoinColumn(name="shipping_id", referencedColumnName="id")
**/
private $shipping;
Subscribe to:
Posts (Atom)