Uploaded image for project: 'Doctrine 1'
  1. Doctrine 1
  2. DC-1040

allow queries with table joins across different databases


    • Type: Improvement
    • Status: Open
    • Priority: Blocker
    • Resolution: Unresolved
    • Affects Version/s: 1.2.3
    • Fix Version/s: 1.2.3
    • Component/s: None
    • Labels:
    • Environment:
      Windows XP SP3, Apache 2, PHP 5.3, MySQL 5.1.36, Symfony 1.4.8, Doctrine 1.2.3


      I'm currently working on a project which relies upon several databases declared in databases.yml in symfony 1.4.8.

      I was facing an issue that has already been raised by other people, namely that you can't join tables which reference each other among different mysql databases.

      I've dug a bit in the Doctrine_Query class and came to a solution that is acceptable for us, and allows now to make joins accross databases. Description follows :

      first change is in the Doctrine_Core class, that gets stuffed with a new constant :
      const ATTR_DATABASE_NAME = 0x1DB;

      this constant allows us to add a new attribute to the databases.yml file as in :
      class: sfDoctrineDatabase
      dsn: mysql:host=;dbname=gesdoc
      username: root

      1. ************* NEW ATTRIBUTE BELOW ************
        database_name: gesdoc
        default_table_collate: utf8_general_ci
        default_table_charset: utf8

      after that, a few changes have been done in the Doctrine_Query class which is attached to this issue for the sake of readability.

      This may not be optimal, and probably need some regression testing, but it is currently working fine on our test server.

      after this is done, I was able to issue queries like the following :
      $x = DocumentTable::getInstance()->createQuery('d')
      ->leftJoin('d.Travail t')
      ->leftJoin('t.CdcIndInt ci')
      ->leftJoin('ci.CdcIndExt ce')
      ->leftJoin('ce.Cahierdescharge cdc')
      ->where('cdc.cdc_chro = ?', $cdc_chro)
      ->addWhere('d.id != ?', $document_id)

      where the referenced tables are in different databases. The necessary object binding has been done in every model class following the paradigm :

      Doctrine_Manager::getInstance()->bindComponent('CdcIndInt ', 'gescdc');
      abstract class BaseCdcIndInt extends sfDoctrineRecord

      I don't know if this description is clear enough, so let me know if something is missing/wrong.

      1. Query.php
        84 kB
        Fabrice Agnello


        There are no comments yet on this issue.


          • Assignee:
            jwage Jonathan H. Wage
            fabrice.agnello Fabrice Agnello
          • Votes:
            0 Vote for this issue
            1 Start watching this issue


            • Created: