Details
-
Type:
Bug
-
Status:
Resolved
-
Priority:
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
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.