DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Refcards Trend Reports
Events Video Library
Refcards
Trend Reports

Events

View Events Video Library

Zones

Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks

Last call! Secure your stack and shape the future! Help dev teams across the globe navigate their software supply chain security challenges.

Modernize your data layer. Learn how to design cloud-native database architectures to meet the evolving demands of AI and GenAI workloads.

Releasing software shouldn't be stressful or risky. Learn how to leverage progressive delivery techniques to ensure safer deployments.

Avoid machine learning mistakes and boost model performance! Discover key ML patterns, anti-patterns, data strategies, and more.

Related

  • Introduction to Couchbase for Oracle Developers and Experts: Part 2 - Database Objects
  • FHIR Data Model With Couchbase N1QL
  • The Complete Tutorial on the Top 5 Ways to Query Your Relational Database in JavaScript - Part 2
  • RION - A Fast, Compact, Versatile Data Format

Trending

  • The Human Side of Logs: What Unstructured Data Is Trying to Tell You
  • Hybrid Cloud vs Multi-Cloud: Choosing the Right Strategy for AI Scalability and Security
  • Apache Doris vs Elasticsearch: An In-Depth Comparative Analysis
  • The Cypress Edge: Next-Level Testing Strategies for React Developers
  1. DZone
  2. Data Engineering
  3. Databases
  4. Practical PHP Patterns: Query Object

Practical PHP Patterns: Query Object

By 
Giorgio Sironi user avatar
Giorgio Sironi
·
Jul. 07, 10 · Interview
Likes (0)
Comment
Save
Tweet
Share
6.2K Views

Join the DZone community and get the full member experience.

Join For Free

An ORM provides an abstraction of storage as an in-memory object graph, but it is difficult to navigate that graph via object pointers without loading a large part of it. Typical problems of this approach are the performance issues related to loading of the various objects, and the transfer of business logic execution from the database side to the client code side, with the resulting duplication.

Anyway, when we start navigating an object graph we have to obtain a reference to an entity somehow (an Aggregate Root), from which we can navigate to the other ones. ORMs and, in general, Data Mappers provide different ways to select a subset of objects (or a single one) and reconstitute only that subset from the data storage.

  • Custom mapper classes with domain-specific methods are the the simplest solution, which is often recommended when not using a generic Data Mapper.
  • Custom mapper classes with finder methods are an half-baked solution, which mixes up domain-specific mappers with general purpose methods, sometimes needed to allow flexibility on the user side.
  • Generic mapper classes with finder methods can be provided as a way to parametrize fields, resulting in methods like findBy($entityName, $field, $value).
  • Generic mapper classes with query objects are employed when there is the necessity of composing queries and pass them around for further elaboration or refining. Promoting the query as an object helps this use case.

Note that once a mapper implements query objects, they can be effectively used in finder methods, which are a subset of the functionality provided by query objects.

In fact, query objects are the most versatile way to ask for the objects that satisfy certain conditions, and they are an Interpreter implementation over a query language adapt for an object model.

All of us already know a query language: SQL. But SQL is pertinent to relational databases, while an ORM strives for keeping the illusion of an object-only model into existence. As a result, it must adopt a different language which describes object features, like HQL (Hibernate) or DQL (the Doctrine equivalent).

Object query languages

There are several differences between an object query language and SQL in the entities you can refer to within queries:

  • SQL refers to tables; object query languages refer to classes and some tables like the association tables for many-to-many relationships simply vanish.
  • SQL refers to rows; object query languages to objects.
  • SQL refers to other tables for making JOINs; object query languages to object collaborators.
  • SQL refers to columns, which also include foreign keys; object query languages only to fields of the objects.

When a full-featured language is involved, there must be a component of the ORM that parses the strings containing language statements into a Query Object. Another way to define such an object (Interpreter) is constructing it by hand, by calling a series of setter methods or by implementing a Builder pattern.

Advantages

  • A Query Object hides the relational model (the schema) from the user, as it can be inferred by the union of the queries and the Metadata Mapping anyway. The information contained in the metadata, like foreign keys and additional tables, do not have to be repeated in the various components of client code.
  • It hides also the peculiarities of the particular database vendor, since the generation of SQL can be addressed by a driver.
  • It promotes queries as first-class citizens, making them objects that can be passed around, cloned or modified.

The database abstraction layers like PDO make of statement objects (PDOStatement) one of their first modelling points.

Disadvantages

The implementation of the parser for a query language is a task of great complexity, which makes this pattern only feasible in generic Data Mappers. Even when using only Query Objects made by hand, it is advisable to employ an external Data Mapper to take advantage of the translation of object-based queries to SQL.

Examples

Doctrine 2 contains a parser for its Doctrine Query Language, which lets you define queries like you would do with PDO, but still referring to an object model. The documentation of the query language itself is pretty complete, so I won't go into details but I'll give you a feel of how using DQL is like. The language itself is compatible with the Doctrine 1 version, if you happen to have used it.

<?php
$query = $em->createQuery('SELECT u FROM MyProject\Model\User u WHERE u.age > 20');
$users = $query->getResult();

$query = $em->createQuery("SELECT u, a FROM User u JOIN u.address a WHERE a.city = 'Berlin'");
$users = $query->getResult();

uery = $em->createQuery('SELECT u, p FROM CmsUser u JOIN u.phonenumbers p');
$users = $query->getResult(); // array of CmsUser objects with the phonenumbers association loaded
$phonenumbers = $users[0]->getPhonenumbers();

$query = $em->createQuery('SELECT u, a, p, c FROM CmsUser u JOIN u.articles a JOIN u.phonenumbers p JOIN a.comments c');
$users = $query->getResult();

Sometimes there are no fixed queries, but a dynamic query has to be constructed from its various parts, as a union of conditions, joins and sorting parameters; not all the parameters may be available at a certain time and concatenating strings to compose a DQL statement is prone to error. Doctrine 2 includes a Query Builder which has methods you can call orthogonally, in any order and combination.

<?php

// $qb instanceof QueryBuilder

// example6: how to define: "SELECT u FROM User u WHERE u.id = ? ORDER BY u.name ASC" using QueryBuilder string support
$qb->add('select', 'u')
->add('from', 'User u')
->add('where', 'u.id = :identifier')
->add('orderBy', 'u.name ASC');
->setParameter('identifier', 100); // Sets :identifier to 100, and thus we will fetch a user with u.id = 100
Relational database Database Object (computer science) PHP sql

Opinions expressed by DZone contributors are their own.

Related

  • Introduction to Couchbase for Oracle Developers and Experts: Part 2 - Database Objects
  • FHIR Data Model With Couchbase N1QL
  • The Complete Tutorial on the Top 5 Ways to Query Your Relational Database in JavaScript - Part 2
  • RION - A Fast, Compact, Versatile Data Format

Partner Resources

×

Comments

The likes didn't load as expected. Please refresh the page and try again.

ABOUT US

  • About DZone
  • Support and feedback
  • Community research
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Core Program
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 3343 Perimeter Hill Drive
  • Suite 100
  • Nashville, TN 37211
  • support@dzone.com

Let's be friends: