Doctrine 1
  1. Doctrine 1
  2. DC-933

Results from Doctrine_Query::execute inconsistent with results from Doctrine_Query::getSqlQuery()

    Details

    • Type: Bug Bug
    • Status: Resolved
    • Priority: Critical Critical
    • Resolution: Invalid
    • Affects Version/s: 1.2.3
    • Fix Version/s: None
    • Component/s: Query
    • Labels:
      None
    • Environment:
      Redhat Linux, Apache, PHP 5,

      Description

      The DQL Query:

      $query = Doctrine_Query::create()
      ->select("
      b.borrowers_date as borrowers_date,
      b.borrower_id as borrower_id,
      borrower_contact.first_name as borrower_first_name,
      borrower_contact.last_name as borrower_last_name,
      lo_contact.first_name as lo_first_name,
      lo_contact.last_name as lo_last_name,
      realtor_contact.first_name as realtor_first_name,
      realtor_contact.last_name as realtor_last_name,
      lo_company_contact.first_name as lender,
      b.current_status as current_status
      ")
      ->from("Borrowers b")
      >innerJoin("b.UserBorrowerAssigned ubass WITH ubass.user_id = " . $this>user_id)
      ->innerJoin("b.ContactInfo borrower_contact")
      ->innerJoin("b.LoBorrowerAssigned lobass")
      ->innerJoin("lobass.LoanOfficers lo")
      ->innerJoin("lo.ContactInfo lo_contact")
      ->innerJoin("lo.Companies lo_company")
      ->innerJoin("lo_company.ContactInfo lo_company_contact")
      ->leftJoin("b.RealtorBorrowerAssigned realbass")
      ->leftJoin("realbass.Realtors realtor")
      ->leftJoin("realtor.ContactInfo realtor_contact");
      ...
      $query->where("b.current_status != 'finialized'")
      ->andWhere("b.current_status != 'ignored'")
      ->andWhere("b.current_status != 'dead'");

      $query->execute Returns only 1 row

      --------------------------------------------------------------------------------

      $query->getSqlQuery() returns:

      SELECT b.borrowers_date AS b_0, b.borrower_id AS b1, c.first_name AS c2, c.last_name AS c3, c2.first_name AS c24, c2.last_name AS c25, c5.first_name AS c56, c5.last_name AS c57, c4.first_name AS c48, b.current_status AS b_9 FROM borrowers b INNER JOIN user_borrower_assigned u ON b.borrower_id = u.borrower_id AND (u.user_id = 129) INNER JOIN contact_info c ON b.contact_info_id = c.contact_info_id INNER JOIN lo_borrower_assigned l ON b.borrower_id = l.borrower_id INNER JOIN loan_officers l2 ON l.loan_officer_id = l2.loan_officer_id INNER JOIN contact_info c2 ON l2.contact_info_id = c2.contact_info_id INNER JOIN companies c3 ON l2.company_id = c3.company_id INNER JOIN contact_info c4 ON c3.contact_info_id = c4.contact_info_id INNER JOIN realtor_borrower_assigned r ON b.borrower_id = r.borrower_id INNER JOIN realtors r2 ON r.realtor_id = r2.realtor_id INNER JOIN contact_info c5 ON r2.contact_info_id = c5.contact_info_id WHERE (b.current_status != 'finialized' AND b.current_status != 'ignored' AND b.current_status != 'dead')

      Running this query in PhpMyAdmin returns 1,095 rows.

      -----------------------------------------------------------------------------------

      Results Inconsistent

        Activity

        Hide
        Roger Webb added a comment -

        I have stripped the query down to this:

        ->select("
        b.borrowers_date as borrowers_date,
        b.borrower_id as borrower_id,
        borrower_contact.first_name as borrower_first_name,
        borrower_contact.last_name as borrower_last_name,
        b.current_status as current_status
        ")
        ->from("Borrowers b")
        ->innerJoin("b.ContactInfo borrower_contact")
        ->innerJoin("b.UserBorrowerAssigned ubass");

        $query->where("b.current_status != 'finialized'")
        ->andWhere("b.current_status != 'ignored'")
        ->andWhere("b.current_status != 'dead'")
        >andWhere("ubass.user_id = ?", $this>user_id);

        Results are still consistent with but report above.

        Classes:

        class Borrowers extends BaseBorrowers
        {

        function setUp()

        { parent::setUp(); $this->hasOne('ContactInfo', array('local' => 'contact_info_id', 'foreign' => 'contact_info_id')); $this->hasOne('LoBorrowerAssigned', array('local' => 'borrower_id', 'foreign' => 'borrower_id')); $this->hasOne('RealtorBorrowerAssigned', array('local' => 'borrower_id', 'foreign' => 'borrower_id')); $this->hasOne('UserBorrowerAssigned', array('local' => 'borrower_id', 'foreign' => 'borrower_id')); $this->unshiftFilter(new Doctrine_Record_Filter_Compound(array('ContactInfo'))); }

        }

        class UserBorrowerAssigned extends BaseUserBorrowerAssigned {

        function setUp()

        { parent::setUp(); $this->hasOne("Borrowers", array("local" => "borrower_id", "foreign" => "borrower_id")); $this->hasOne("Users", array("local" => "user_id", "foreign" => "user_id")); }

        }

        class Users extends BaseUsers {

        function setUp()

        { parent::setUp(); $this->hasOne("ContactInfo", array("local" => "contact_info_id", "foreign" => "contact_info_id")); $this->unshiftFilter(new Doctrine_Record_Filter_Compound(array('ContactInfo'))); }

        }

        The users class also contains the function that issues the query in question.

        Show
        Roger Webb added a comment - I have stripped the query down to this: ->select(" b.borrowers_date as borrowers_date, b.borrower_id as borrower_id, borrower_contact.first_name as borrower_first_name, borrower_contact.last_name as borrower_last_name, b.current_status as current_status ") ->from("Borrowers b") ->innerJoin("b.ContactInfo borrower_contact") ->innerJoin("b.UserBorrowerAssigned ubass"); $query->where("b.current_status != 'finialized'") ->andWhere("b.current_status != 'ignored'") ->andWhere("b.current_status != 'dead'") >andWhere("ubass.user_id = ?", $this >user_id); Results are still consistent with but report above. Classes: class Borrowers extends BaseBorrowers { function setUp() { parent::setUp(); $this->hasOne('ContactInfo', array('local' => 'contact_info_id', 'foreign' => 'contact_info_id')); $this->hasOne('LoBorrowerAssigned', array('local' => 'borrower_id', 'foreign' => 'borrower_id')); $this->hasOne('RealtorBorrowerAssigned', array('local' => 'borrower_id', 'foreign' => 'borrower_id')); $this->hasOne('UserBorrowerAssigned', array('local' => 'borrower_id', 'foreign' => 'borrower_id')); $this->unshiftFilter(new Doctrine_Record_Filter_Compound(array('ContactInfo'))); } } class UserBorrowerAssigned extends BaseUserBorrowerAssigned { function setUp() { parent::setUp(); $this->hasOne("Borrowers", array("local" => "borrower_id", "foreign" => "borrower_id")); $this->hasOne("Users", array("local" => "user_id", "foreign" => "user_id")); } } class Users extends BaseUsers { function setUp() { parent::setUp(); $this->hasOne("ContactInfo", array("local" => "contact_info_id", "foreign" => "contact_info_id")); $this->unshiftFilter(new Doctrine_Record_Filter_Compound(array('ContactInfo'))); } } The users class also contains the function that issues the query in question.
        Hide
        Roger Webb added a comment -

        Non issue. Wasn't familiar with use of Doctrine.

        Show
        Roger Webb added a comment - Non issue. Wasn't familiar with use of Doctrine.
        Hide
        Jacob Spizziri added a comment -

        I know this is an old issue, but I'm experiencing a similar problem. What did you do to fix this?

        Here is my QueryBuilder query:

        $qb = $query = $this->repoLibrary->createQueryBuilder('l');

        $query = $qb
        ->select('l')
        ->innerJoin('l.productVariant', 'v')
        ->innerJoin('v.product', 'p')
        ->innerJoin('p.taxons', 't', 'WITH', 't.id IN (:array)')
        ->where('l.user = :user')
        ->groupBy('l.id HAVING count(DISTINCT t.id) >= :count')
        ->setParameter('user', $user)
        ->setParameter('array', $s)
        ->setParameter('count', count($taxons))
        ->getQuery();

        $query->getSql() executes perfectly in MySQL but
        $query->getResult() doesn't return what I'm looking for.

        Show
        Jacob Spizziri added a comment - I know this is an old issue, but I'm experiencing a similar problem. What did you do to fix this? Here is my QueryBuilder query: $qb = $query = $this->repoLibrary->createQueryBuilder('l'); $query = $qb ->select('l') ->innerJoin('l.productVariant', 'v') ->innerJoin('v.product', 'p') ->innerJoin('p.taxons', 't', 'WITH', 't.id IN (:array)') ->where('l.user = :user') ->groupBy('l.id HAVING count(DISTINCT t.id) >= :count') ->setParameter('user', $user) ->setParameter('array', $s) ->setParameter('count', count($taxons)) ->getQuery(); $query->getSql() executes perfectly in MySQL but $query->getResult() doesn't return what I'm looking for.

          People

          • Assignee:
            Guilherme Blanco
            Reporter:
            Roger Webb
          • Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: