Doctrine ORM

A quick introduction to using Doctrine ORM.

Transcript

1. Doctrine Because We're Software Developers Kristopher Wilson / @mrkrstphr
2. Queries Kind of Suck SELECT * FROM orders o JOIN customers c ON c.id = o.customer_id JOIN invoices i ON i.order_id = o.id JOIN employees rep ON rep.id = c.sales_rep_id WHERE o.due_date <= now() AND o.status = 'open';
3. Queries Kind of Suck
4. Some People Suck at Queries SELECT DISTINCT q.parent_agent_company_id, c.agent_company, q.agent_company_id, COALESCE(c1.agent_company, c2.agent_company) AS sub_agent_company, q.circuit_id, q.mrc, CASE WHEN chargebacks.mrc < 0 THEN q.mrc + chargebacks.mrc ELSE q.mrc END AS mrc, CASE WHEN chargebacks.mrc < 0 THEN chargebacks.mrc ELSE 0 END AS chargeback_amount, q.account_id, agents.is_account_past_due(q.account_id), q.expected_mrc, q.bill_start_date, q.om_customer_nm, q.order_type_cd, q.base_order_id, q.service_type_cd, q.service_bandwidth_bps, CASE WHEN q.term_cd = \'NOCH\' or q.term_cd = \'UK\' THEN agents.get_term_fallback(q.base_order_id) ELSE q.term_cd END AS term_cd, q.customer_nm, u.last_nm || \', \' || u.first_nm as channel_manager_nm, q.order_submitted, extract(epoch from q.bill_start_date) as epoch_timestamp, CASE WHEN ((:previous_bill_date)::date - \'1 month\'::interval) < q.bill_start_date THEN false ELSE true END AS was_previously_sold, CASE WHEN ((:previous_bill_date)::date - \'1 month\'::interval) < q.bill_start_date AND q.order_type_cd IN(\'NW\',\'RW\') AND q.term_cd NOT IN (\'NOCH\',\'UK\') AND agents.get_term_sub(q.term_cd) != \'MTM\' THEN true ELSE false END AS apply_upfront, CASE WHEN agents.is_upfront_blocked(q.base_order_id) THEN true ELSE false END AS block_upfront, q.expected_nrc, COALESCE(c.assuming_agent_company_id, q.parent_agent_company_id) as assuming_agent_company_id, c.assumed_revenue_level, agents.get_order_submitted_fallback(q.base_order_id) as ica_order_submitted, q.product_cd FROM ( SELECT q.circuit_id, q.mrc,q.expected_mrc, q.expected_nrc, bo.bill_start_date, bo.order_submitted, bo.order_type_cd, bo.service_type_cd, omc.om_customer_nm, ac.agent_company_id, bo.base_order_id, bo.term_cd, q.customer_nm, q.customer_service_bundle_id, COALESCE(ac.parent_agent_company_id, ac.agent_company_id) as parent_agent_company_id, q.account_id, q.first_applied, bo.product_cd, sb.service_bandwidth_bps FROM ( SELECT * FROM crm.billing_snapshot WHERE statement_date = (:previous_bill_date)::date ) q JOIN om."BASE_ORDERS" bo ON q.circuit_id = bo.uss_circuit_id LEFT OUTER JOIN om."SERVICE_BANDWIDTHS" sb ON sb.service_bandwidth_id = bo.service_bandwidth_id JOIN om."OM_CUSTOMERS" omc ON bo.om_customer_id = omc.om_customer_id
5. Personally, I like Objects $orders = $repository->getOverDueOrders(); foreach ($orders as $order) { $parcelFactory->createParcels($order); $order->markOrderShipped(); $messenger->notifyShipment( $order, new EmailNotifier() ); } $repository->flush();
6. Personally, I like Objects $customer = new Customer(); $customer->setSalesRep(Auth::getUser()); $customer->setCreditLimit(5000); $customer->getAddresses()->add( (new Address()) ->setStreet('123 AnyStreet') ->setCity('Grand Rapids') ->setState('MI') ->setPostal(49418) ); $repository->persist($customer)->flush();
7. The Doctrine Project Doctrine is a set of libraries: ▪ DBAL: Database Abstract Layer ▪ ORM: Object Relational Mapper
8. The Doctrine Project Doctrine is a set of libraries: ▪ DBAL: Database Abstract Layer ▪ ORM: Object Relational Mapper ▪ ODM: Object Document Mapper (Mongo, Couch) ▪ Migrations: Database Versioning
9. Doctrine ORM Doctrine ORM is a Data Mapper. Essentially, this allows you to map Plain Old PHP Objects (POPOs) to database tables. It's a way for returning, building, and saving POPOs, rather than working with queries and data directly.
10. Doctrine ORM: Entities You start with a PHP object, called an Entity: class Customer { protected $id; protected $name; public function getId() {return $this->id;} public function setId($id) {$this->id = $id;} public function getName() {return $this->name;} public function setName($name) {$this->name = $name;} }
11. Doctrine ORM: Mappings Which you then map to a database (Yaml): Customer: type: entity table: customers id: id: column: customer_id fields: name: length: 100
12. Doctrine ORM: Mappings Which you then map to a database (XML): <doctrine-mapping> <entity name="Customer" table="customers"> <id name="id" type="integer" /> <field name="name" length="100" /> </entity> </doctrine-mapping>
13. Doctrine ORM: Mappings Which you then map to a database (Annotations): /** * @Entity * @Table(name="customers") */ class Customer { /** * @Id * @Column(type="integer") */ protected $id; /** * @Column(length=100) */ protected $name; }
14. Doctrine ORM: Mappings
15. Doctrine ORM: EntityManager Once you have your Entities and Mappings in place, you can use Doctrine's EntityManager to instantiate Repositories for your entities and get data! $customer = $entityManager->find('Customer', 14); Magic! $customer is now a Customer, whose $id is 14!
16. Doctrine ORM: EntityManager All database operations can now be done through the EntityManager by simply calling repository methods to get existing data, or by passing Entities to create or update them. Now you're working with objects!
17. Doctrine ORM: Associations Associations can be mapped: Order: type: entity table: orders id: id: column: order_id manyToOne: channel: targetEntity: Customer joinColumn: name: customer_id referencedColumnName: customer_id class Order { protected $customer; public function setCustomer($customer) { $this->customer = $customer; } public function getCustomer() { return $this->customer; } }
18. Doctrine ORM: Associations Associations can be used fluently: $order = $em->find('Customer', 101); $customer = $order->getCustomer(); echo 'Order Number: ' . $order->getNumber() . '<br>'; echo 'Customer Name: ' . $customer->getName() . '<br>'; Doctrine takes care of loading the data on demand (lazy loading, unless otherwise specified in the mapping file).
19. Doctrine ORM: Associations Doctrine handles all relationship types: // M-1: $order = $em->find('Customer', 101); $customer = $order->getCustomer(); // Doctrine handles all relationship types // 1-M: foreach ($customer->getOrders() as $order) {} // M-M (through linking tables): foreach ($customer->getContacts() as $contact) {} // 1-1: $billing = $customer->getBillingInfo();
20. Doctrine ORM: Associations Doctrine handles all relationship types: // M-1: $order = $em->find('Customer', 101); $customer = $order->getCustomer(); // Doctrine handles all relationship types // 1-M: foreach ($customer->getOrders() as $order) {} // M-M (through linking tables): foreach ($customer->getContacts() as $contact) {} // 1-1: $billing = $customer->getBillingInfo();
21. Doctrine ORM: Repositories Doctrine generates repositories for making interacting with data easier: $repository = $em->getRepository('Customer'); $customer = $repository->find(15); $customers = $repository->findBy(['type' => 'Retail']); $customers = $repository->findAll();
22. Doctrine ORM: DQL Doctrine provides Doctrine Query Language (DQL) to query entities: $builder = $this->entityManager->createQueryBuilder() ->select('customer') ->from('Customer', 'customer') ->join('billing', 'billing') ->where('customer.id = :customerId') ->setParameter('customerId', $customerId);