The equivalent DQL query would look something like this:
$dql = <<< EOQ
SELECT
t,
a1.addressName, a1.email, a1.phone1, a1.postcode, a1.addressType,
a2.addressName AS vehicleAddressName, a2.email AS vehicleEmail,
a2.phone1 AS vehiclePhone1, a2.postcode AS vehicleLocation, a2.addressType AS
vehicleAddressType,
a3.addressName AS insurerAddressName
FROM Transaction t
LEFT JOIN Address a1 WITH a1.transactionid = t.id AND a1.address_type =
:type1
LEFT JOIN Address a2 WITH a2.transactionid = t.id AND a2.address_type =
:type2
LEFT JOIN Address a3 WITH a3.transactionid = t.id AND a3.address_type =
:type3
EOQ;
$q = $em->createQuery($dql);
$q->setParameters(array('type1' => 'INSURED', 'type2' => 'VEHICLE
LOCATION', 'type3' => 'INSURER'));
$result = $q->getScalarResult();
Zend_Db_Select is simply a SQL query builder for Zend_Db, which follows the
Table Data Gateway pattern.
Doctrine2 is an Object Relational Mapper (ORM), which follows the Data Mapper
and Repository patterns.
When migrating from the first to the latter, it's also important to change your
way of thinking.
With Zend_Db you're thinking from the database. With Doctrine 2 you should
forget your database (with the exception of defining the mappings) and think
from PHP objects (Entities).
In other words, maybe something like this could be appropriate:
Find the transactions:
SELECT t, a FROM Transaction t LEFT JOIN Address a
Add a method like this to the Transaction class (assuming a transaction only
has one type of address at any given time):
/**
* @param string $addressType
* @return Address
*/
public function getAddressByType($addressType)
{
foreach ($this->addresses as $address) {
if ($address->getAddressType() === $addressType) {
return $address;
}
}
return null;
}
Then when you need the "insured address" of a transaction, do:
$transaction->getAddressByType('INSURED');
I hope this helps!
--
Jasper N. Brouwer
(@jaspernbrouwer)
On 21 October 2014 at 20:52:23, Peregrine ([email protected])
wrote:
>
>
> I have a Zend_DB_Select to create a query which gets different types of
> addresses joined to my transactions base table.
>
> I'm migrating this to Doctrine but do not know where to start with this.
>
> The relevant part of the query in Zend looks like:
>
> $select->joinLeft(
> array('a1' => 'addresses'), 'a1.transactionid = t.id AND a1.address_type
> ="INSURED"',
> array('address_name', 'Email', 'Phone1', 'postcode',
> 'address_type'))->joinLeft(
> array('a2' => 'addresses'), 'a2.transactionid = t.id AND a2.address_type
> ="VEHICLE
> LOCATION"',
> array('vehicle_address_name'=>'address_name',
> 'vehicle_Email'=>'Email',
> 'vehicle_Phone1'=>'Phone1',
> 'vehicle_location'=>'postcode',
> 'vehicle_address_type'=>'address_type'))->joinLeft(
> array('a3' => 'addresses'), 'a3.transactionid = t.id AND a3.address_type
> ="INSURER"',
> array('insurer_address_name'=>'address_name'))
>
> So I get each address with a different alias in the result row.
>
> I have created my entities\associations in Doctrine and can run a DQL query
> like:
>
> $dql = "SELECT t, a FROM Transaction t JOIN t.address a"
>
> which can get a collection of the addresses.
>
> But is there a way of aliasing the fields without making multiple
> associations in the mappings? Or am I even asking the right question?
--
You received this message because you are subscribed to the Google Groups
"doctrine-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/doctrine-user.
For more options, visit https://groups.google.com/d/optout.
