Doctrine 1
  1. Doctrine 1
  2. DC-1040

allow queries with table joins across different databases

    Details

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

      Description

      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 :
      gesdoc:
      class: sfDoctrineDatabase
      param:
      dsn: mysql:host=127.0.0.1;dbname=gesdoc
      username: root
      password:
      attributes:

      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')
      ->distinct()
      ->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)
      ->execute();

      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

        Activity

        Fabrice Agnello created issue -

        This list may be incomplete, as errors occurred whilst retrieving source from linked applications:

        • Request to http://www.doctrine-project.org/fisheye/ failed: Error in remote call to 'FishEye 0 (http://www.doctrine-project.org/fisheye/)' (http://www.doctrine-project.org/fisheye) [AbstractRestCommand{path='/rest-service-fe/search-v1/crossRepositoryQuery', params={query=DC-1040, expand=changesets[0:20].revisions[0:29],reviews}, methodType=GET}] : Received status code 503 (Service Temporarily Unavailable)

          People

          • Assignee:
            Jonathan H. Wage
            Reporter:
            Fabrice Agnello
          • Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

            • Created:
              Updated: