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
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.
