Showing posts with label database. Show all posts
Showing posts with label database. Show all posts

Thursday, January 21, 2016

Using multiple databases with Symfony2 and Doctrine2

I was looking for documentation on how to use multiple databases with Symfony and all the results were talking about having 2 connections with 2 entity managers and adding mappings for each bundle ...too complicated for a simple thing.

But I kept on searching and I found this article:  https://techpunch.co.uk/development/using-multiple-databases-with-symfony2-and-doctrine2  which is saying:

"
You will need to use only connection that spans all of your databases, if you want to build a relationship between entities then they must use the same connection. Do to this you will need a user that has access to all of the databases the you wish to access. Setup this user as per usual within your Symfony2 application, for the database name just select one of the databases, it doesn't matter which one.
No extra Doctrine config is needed to get this working, ....

The key to getting multiple databases to work is within your entity classes, you need to specify the table name of the entity with a prefix of the name of the database to which the table belongs. Here is an example using annotations:

<?php
namespace Demo\UserBundle\Entity;

use DoctrineORMMapping as ORM;

/**
 * Demo\UserBundle\Entity\User
 *
 * @ORMTable(name="users.User")
 */
class User implements
{
  /* ... */

"
I tried this solution on my project and worked fine.

You may want to look also at Doctrine's  Master-Slave Connection:  http://blog.alejandrocelaya.com/2014/04/18/configure-multiple-database-connections-in-doctrine-with-zend-framework-2-2/

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

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.