Ngicabanga yini. Futhi lokhu kuneminyaka elide kakhulu ukujabulela. Kwi-karriera yami, ndingathanda ukuthi ukulandwa kwe-SQL ebomvu kuyinto ingcindezi yokungasebenzi. Kubalulekile ukuba awukwazi ukwenza i-ORM lapho ufuna. Kubalulekile ukuba iziphakamiso zakho zihlukile. Abacwaningi Abakhiqizi abasebenzisa i-ORMs. Abacwaningi Abasebenzi Abasebenzi wabhala i-SQL. Ngingathanda. Hhayi kuphela ingathanda. Lezi zibonelelo zihlanganisa ngokuvamile. Yenza ngithole ikhowudi okuhlobene futhi efanelekayo kodwa ngokuvamile kuyinto isizukulwane yokusebenza. Ngaba ungathanda kanjani izibuyekezo ze-SQL ezidingekayo ukudlala ikhasi elilodwa kwelinye isicelo ethu? Ibhasi elula le-lists nge-20 izilimi. Futhi inombolo 47. 47 izivakashi round ukuze database futhi back ukunikela amayunithi ezingu-20 data. I-code eyenziwe ngcono kakhulu. I-Type-safe. I-Tested ngokuphelele. Nge-abstractions ezinhle. Futhi ikhowudi wahlukanisa ngokushesha isikhathi yethu yokusabela. Futhi akukho umntu wabhala ukuthi ngoba abstraction wabhala umdla kwethu. I-one thing that no one puts in the ORM's marketing material is that it's very good in hiding what it's really doing with the database. Thola ucwaningo mayelana ne-Your Really Have I-ORM inikeza kakhulu embhedeni. Futhi lokhu kuyinto ingxenye yebhizinisi. Ezinye izinto ezinhle kakhulu ukuthi kungabangela wena ngaphandle kokufunda ukuthi. I-ORM inikeza ukuphathwa kwe-schema - ama-version migrations, i-rollbacks, ne-schema diffing. Futhi lokhu ingxenye kuyinto enhle. Futhi kufanele uqhubeke ukusebenzisa. Ngaphezu kwalokho, inikeza i-API enhle yokusebenza kwe-object efanelekayo - `findById`, `create`, `update`, and `delete`. Futhi lokhu ingxenye kuyinto enhle futhi. Ikhodi kuyinto enhle, uhlobo lokhuseleko kuyinto efanelekayo, futhi akukho izindleko zokukhiqiza okungenani. Ngemuva kwalokho - ukucubungula phakathi kwezinto nezitebhisi. Futhi lokhu kuyinto ingxenye yesisekelo ye-ORM. Futhi, lokhu kuyinto lapho izimo zihlala. Lokhu ukucubungula kuyinto ephelele lapho kusebenza futhi inikeza inkinga emnandi lapho ayikho. I-ORM inikeza i-illusion ye-database portability. Futhi ngamanye amazwe ezinhle, ungakwazi ukushintsha kusuka ku-MySQL ku-PostgreSQL ngaphandle kokuguqulwa kwelanga le-code. Kodwa e-real world, ungenza. Uyaziqha i-database futhi uye uxhumane. Futhi ukuchitha le-illusion kusiza ukunceda i-database-specific features eyenza isicelo yakho ngokukhawuleza. Konke abacwaningi ukwenza izivumelwano. Uma ufunde, uzothola. Uma akufanele, uzothola izivumelwano ezingu-47 (okanye ngaphezulu) ukuze zibonise izilimi ezingu-20. I-N+1 I-Problem I-Symptom, Ayikho I-Disease Konke wonke umphakeli owenziwe nge-ORM wabhala malunga ne-N+1 query problem. I-load relationship emangalisayo ngaphakathi kwelanga, futhi lokhu uzokuzisa isibuyekezo esisodwa ngalinye. Futhi cishe zonke izifundo ze-ORMs zihlanganisa lokhu njengesibonelo. Kodwa ngicabanga ukuthi ukucindezeleka kuphela ku-N + 1 isikhwama akuyona isithombe esikhulu. Isikhwama le-N + 1 kuyinto kuphela ingxenye esibonakalayo ye-iceberg ye-ORM isikhwama-ukwenza kube lula kakhulu ukubhalisa ikhodi okuyinto ekukhanyisa kodwa kuyinto isikhwama ngaphansi kwe-cap. Imibuzo yami ye-47 ayikho zonke imibuzo ye-N + 1. Ezinye izibuyekezo ezihambayo. Kwezinye izibuyekezo, i-ORM isitholela izakhi zokusebenza ephelele lapho izimbobo ezimbili kuphela zitholele. Ezinye izibuyekezo ze-COUNT ezihambayo eziholwe yi-pageination library. Futhi akukho lokhu akuyona ngokucacileyo kusukela kokufunda ikhodi le-application! Ungathola kuphela ngokucacisa ku-query log. Futhi i-Raw SQL inikeza ikhono oluphindwe. Uyazi ngokucacileyo ukuthi izibuyekezo zithunyelwa ku-database. Thola lokhu Raw SQL. SELECT id, name, email FROM users WHERE account_id = ? AND is_active = 1 ORDER BY created_at DESC LIMIT 20 OFFSET 0; Uyazi isibuyekezo. Uyazi izibuyekezo esifanele esithathwe. Ungathanda izibuyekezo ezidingekayo kuleli isibuyekezo. Futhi, ungathanda ngqo ku-database console yakho futhi usebenza isicelo se-EXPLAIN. Ngokusebenzisa i-ORM, yonke into etholakalayo iyinhlangano. Ukukhiqizwa okwenziwe ngempumelelo ngempumelelo ngempumelelo. Futhi imibuzo yokwenene kungabangela ngempumelelo ngokushesha nge-modeli yakho, futhi ungakwazi ukulawula kwabo. Nge-ORMs Eziqinisekile Amasipho Kukho uhlobo lwezinkinga lapho i-ORM ayikho kuphela. I-ORM ayikwazi ukuhlangabezana. Futhi isakhiwo ayikwazi ukuhlangabezana. Windows Ukusebenza I-Window functions - `RANK()`, `ROW_NUMBER()`, `LAG()`, 'LEAD()`, izindleko ezisebenzayo, izindleko ezisebenzayo - kukhona omunye izici ezinzima ze-moder SQL. Enye isibuyekezo zithumela ukwenza izinto ezivela ku-application level. SELECT user_id, amount, order_date, SUM(amount) OVER ( PARTITION BY user_id ORDER BY order_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS running_total FROM orders WHERE account_id = ?; Kulesi isivumelwano uzokuthumela inani elandelayo ye-akhawunti e-request eyodwa. It is fast, elegant and correct. Uma ungayifaka ukuvelisa okufanayo ku-ORM's Query Builder, akukwazi ukwenza lokhu. Akukho ngaphandle kokuphuma ku-Raw SQL ephezulu nangona kunjalo. Ngakho-ke kukhona izinketho ezimbini ukwenza okuhlolwa. Uyazi zonke izilimi ku-memory futhi ufakele ku-application-level, noma uyazi i-SQL encane. Akukho ingxenye yesithathu. Ukusebenza Bulk Ukukhangisa ifenisha. Ufuna ukufaka ama-records angu-10,000 kusuka ku-API ye-external futhi kufanele ukufaka noma ukuhlaziywa ku-database yakho. Ama-records ezintsha kufanele ifakwe futhi ama-records ezikhoyo kufanele ifakwe uma idatha eduze iyahora. Ukulungiswa kwe-ORM foreach ($incomingRecords as $data) { $record = $this->repository->findOneBy(['external_id' => $data['id']]); if ($record === null) { $record = new SyncRecord(); $record->setExternalId($data['id']); } $record->setPayload($data['payload']); $record->setSyncedAt(new \DateTimeImmutable()); $this->em->persist($record); } $this->em->flush(); Ukuze kusetshenziswe i-10k idokhumenti, uzodingeka ukuguqulwa kwe-10,000 i-select queries kanye ne-10,000 i-select or update queries. Futhi lokhu uzodingeka isikhathi esifushane. Ukulungiswa kwe-SQL INSERT INTO sync_records (external_id, payload, synced_at) VALUES (?, ?, NOW()), (?, ?, NOW()), ... ON DUPLICATE KEY UPDATE payload = VALUES(payload), synced_at = VALUES(synced_at); I-batch ye-500-1,000 rows. I-execution time ephelele: i-seconds. 'ON DUPLICATE KEY UPDATE' iyisici ye-MySQL. Akukho i-ORM equivalent. I-abstraction akuyona kuphela lokhu. Ukusebenza kwe-JSON Uma uxhumane i-JSON ku-database yakho, futhi ngemva kwe-MySQL 8 ne-PostgreSQL iye yenzelwe kahle kakhulu. Futhi izicelo eziningi zenza. Uyakwazi ukuqhuba ekupheleni kwe-Wall ye-ORM yakho. Ukuhlobisa nge-JSON path? Ukuhlobisa ku-JSON field value? Ukusetshenziswa kwe-JSON_TABLE ukuguqulwa kwe-JSON array ku-rows? Lezi zokusebenza zayo zayo zokusebenza kahle. Lezi zokusebenza ze-ORMs zokusebenza okungabizi, ingxenye, noma akukho. -- Find all products where attributes contain color = 'blue' SELECT * FROM products WHERE JSON_UNQUOTE(JSON_EXTRACT(attributes, '$.color')) = 'blue'; -- This works because we created a functional index: -- CREATE INDEX idx_color ON products((JSON_UNQUOTE(JSON_EXTRACT(attributes, '$.color')))); Okwangempela, ungakwazi ukuguqulwa ibhizinisi we-json ngaphakathi ku-field ye-virtual embhedeni, kodwa kungcono isiko. Debugging: Izimali ezihambile Ngiyazi i-scenario ukuthi ungathanda. Uyakwazi ukucinga isibuyekezo eside ekukhiqizeni. Ayikho eside kakhulu. Kodwa eside kakhulu ukuze amakhasimende bayibuyekeza, noma ukubuyekeza ukuqala ukubuyekeza. Ingabe ufake ikhodi le-application ukuze ufunde isibuyekezo. Kuyinto ucwaningo lwezindlela: izilinganiso ezingu-15 ezingenalutho, ama-filters ezisetshenziselwa ngokuvamile ngokusekelwe kuma-parameter ye-request, a pair of joins added somewhere in a base repository class, ukulayishwa okuphakeme asekelwe ku-model file ukuthi ungavula eminyakeni. Uyakwazi ukucubungula ukucubungula ukucubungula i-SQL. It has 60 izilimi. It has a subquery ukuthi awukwazi ukucubungula. It has a 'ORDER BY' on a column that is not indexed. Ngaba ungenza ukuthi subquery etholakalayo. Yini of the fifteen method chain calls etholakalayo? Kuyinto etholakalayo kusuka ku-base class noma emzimbeni? Kuyinto idatha esifundeni ye-ORM complexity. Uma into esizayo, udinga izindandatho ezimbili ngokulinganayo: isicelo logic kanye isicelo generating logic. Nge SQL esifundeni, udinga kuphela eyodwa. // What you see in code review $users = $this->userRepository ->createQueryBuilder('u') ->leftJoin('u.account', 'a') ->andWhere('u.isActive = :active') ->andWhere('a.plan = :plan') ->setParameter('active', true) ->setParameter('plan', $plan) ->getQuery() ->getResult(); // What actually runs. And nobody reads until something breaks SELECT u.id, u.name, u.email, u.created_at, u.updated_at, u.is_active, u.account_id, a.id AS a_id, a.name AS a_name, a.plan AS a_plan, a.is_active AS a_is_active, a.created_at AS a_created_at, a.updated_at AS a_updated_at, a.meta AS a_meta FROM users u LEFT JOIN accounts a ON a.id = u.account_id WHERE u.is_active = 1 AND a.plan = 'enterprise' Kuyinto 'SELECT *' isebenzayo - ukuchithwa zonke izilimi kusuka kumahhotela eyahlukile ku-memory - kuyinto esithakazelisayo lapho usebenzisa i-entity hydration ephelele. Ngokuvamile, ufuna kuphela izilimi ezingu-20. I-ORM wahlala zonke izilimi ezingu-20 ngoba ufuna ukunikeza isikhwama ephelele. I-Architecture Okufakiwe Kwayo Ngemva kweminyaka engama-10 yokwenza kanye nokuhlola lezi zibonakali, lapha isakhiwo ngama-apply kumeprojekthi ngama-working. ORM owns: - Zonke Imibuzo ye-schema ne-migration Ukuphathwa kwe-entity lifecycle (ukwakha, ukuhambisana, flush, ukususwa) - Ukuhlola okuhlobisa okuhlobisa: Find by ID, Find by Single Filter, Izikhangibavakashi ze-entities - Ukulawula Relationship ngaphakathi amaphuzu amancane, amaphuzu amancane Raw SQL owns: - Iziqu ze-report, i-dashboard, noma i-export - Zonke imisebenzi ze-bulk: ama-batch inserts, ama-batch updates, ama-uperts - Aggregation phezu amasethi eziningi - Yonke isibuyekezo esebenzisa izici database-specific - Futhi ngokuvamile, noma iyiphi isibuyekezo esibonakalayo ku-slow query log Ngo-Symfony, ukuqhuba okuhlukaniswa kubhalwe. I-entity repositories isebenza nge-ORM layer. Isakhiwo esihlalweni esihlalweni esihlalweni - ngithi "read repositories" noma "query repositories" - isebenza nge-SQL layer ngokusebenzisa i-DBAL ngqo: // Entity repository - pure ORM, simple operations class UserRepository extends ServiceEntityRepository { public function findActiveById(int $id): ?User { return $this->findOneBy(['id' => $id, 'isActive' => true]); } public function save(User $user): void { $this->getEntityManager()->persist($user); $this->getEntityManager()->flush(); } } // Query repository - raw SQL, complex reads class UserAnalyticsRepository { public function __construct(private readonly Connection $db) {} public function getRetentionBySignupCohort(\DateTimeImmutable $from, \DateTimeImmutable $to): array { $sql = <<<SQL SELECT DATE_FORMAT(u.created_at, '%Y-%m') AS cohort, COUNT(DISTINCT u.id) AS total_users, COUNT(DISTINCT CASE WHEN u.last_active_at >= DATE_SUB(NOW(), INTERVAL 30 DAY) THEN u.id END) AS active_last_30d, ROUND( COUNT(DISTINCT CASE WHEN u.last_active_at >= DATE_SUB(NOW(), INTERVAL 30 DAY) THEN u.id END) / COUNT(DISTINCT u.id) * 100, 1 ) AS retention_pct FROM users u WHERE u.created_at BETWEEN :from AND :to GROUP BY cohort ORDER BY cohort ASC SQL; return $this->db->fetchAllAssociative($sql, [ 'from' => $from->format('Y-m-d'), 'to' => $to->format('Y-m-d'), ]); } } I-DBAL inikeza yonke into esithathwe - izibalo ezivela, ukulawula ukuxhumanisa, ukweseka kwezohwebo. Ingabe ushiye ukhuseleko yokufaka i-SQL. Ingabe ushiye i-object hydration, okuyinto akufanele kuleli khosi. Kuhlolwe futhi ukuthi inguqulo ye-SQL esithambile inikeza ukuthi inguqulo ye-ORM ayikwazi: isibuyekezo itholakala khona. Noma wonke umphakeli e-team angakwazi ukufunda, ukucacisa, ukucacisa ku-database console, isebenze 'EXPLAIN' ku-it, futhi ukucacisa. Akuningi ukucacisa ku-code. I-Skill That Was Never Optional Kwezinye abantu zibonisa ukuthi SQL kuyinto ubuchwepheshe esidumile, okufanayo lapho izixhobo ezivamile zihlala, njengokufundisa indlela yokusebenzisa isithombe se-physical ngenxa yokuzalwa kwe-telephone yakho. Ngingathanda ukuthi lokhu ivimbele. I-SQL iyilwimi esebenzayo ye-database yakho. I-database inikeza umsebenzi engaphezu kuka-component ye-most backend applications. Ukuphathelela ukuthi ufuna ukwenza akuyona isizukulwane esizodwa. Kuyinto esisodwa. I-developer eyenziwe kakhulu emzimbeni yami uyahlanganyela into efanayo: zihlanganisa zonke izindandatho. Zisebenzisa i-ORM lapho iyatholakala futhi zihlanganisa i-SQL lapho akuyona. Zihlanganisa ukuthi ukufinyelela ku-SQL kuyinto ukuhlangabezana kokuphumula. Zihlanganisa ukuthi isebenzisa isixhobo olufanelekayo. Futhi zihlanganisa isixhobo olufanelekayo ngoba zihlanganisa ngamunye. Thina siphinde ukuthi kungase usebenzisa i-index, iningi amayunithi eyenza ukubuyekeza, futhi iningi idatha eyenza ukubuyekeza.
