Query Builders & ORM Concepts 🗃️

Map database tables to PHP objects with query builders, the Active Record pattern, and Eloquent/Doctrine-style relationships.

What You'll Learn in This Lesson

• Build a fluent query builder with chained methods
• Understand the Active Record and Data Mapper patterns
• Implement CRUD operations with model classes
• Define relationships: hasMany, belongsTo, manyToMany
• Compare Eloquent (Laravel) vs Doctrine (Symfony)

Note: PHP runs on a server. Our examples simulate PHP logic using JavaScript. To run real PHP, install PHP.

🎯 Real-World Analogy: A query builder is like building a sandwich order step by step: "wheat bread, add turkey, add lettuce, no mayo, toast it." An ORM goes further — instead of thinking about bread and ingredients (tables and columns), you just think about the sandwich object and the ORM handles the kitchen.

Building a Query Builder

A query builder lets you construct SQL queries with a fluent, chainable API. Each method returns $this, so you can chain ->where()->orderBy()->limit() into readable, composable queries — no string concatenation, no SQL injection risk.

Try It: Query Builder

Build SQL queries with a fluent API — select, where, join, order, limit

Try it Yourself »

JavaScript

// Building a Query Builder from Scratch
console.log("=== Why Query Builders? ===");
console.log();
console.log("Raw SQL:   'SELECT * FROM users WHERE age > 25 AND city = \"London\" ORDER BY name LIMIT 10'");
console.log("Builder:   $db->table('users')->where('age', '>', 25)->where('city', 'London')->orderBy('name')->limit(10)->get()");
console.log();
console.log("Benefits: No SQL injection, readable, composable, database-agnostic");
console.log();

class QueryBuilder {
  constructor(table) {
  
...

ORM: Active Record Pattern

An ORM (Object-Relational Mapper) maps each database table to a PHP class and each row to an object. The Active Record pattern (used by Laravel's Eloquent) puts CRUD methods directly on the model: User::find(1), $user->save(), User::where('role', 'admin')->get().

Try It: Active Record ORM

Create, read, update models and define relationships

Try it Yourself »

JavaScript

// ORM: Mapping Tables to Objects
console.log("=== Active Record Pattern ===");
console.log();
console.log("Each model class = one database table");
console.log("Each instance = one row in that table");
console.log();

class Model {
  static tableName = "models";
  static records = [];
  static nextId = 1;

  constructor(attrs = {}) {
    this.id = attrs.id || null;
    this.attributes = { ...attrs };
    this.dirty = {};
  }

  static create(attrs) {
    let instance = new this(attrs);
    inst
...

⚠️ Common Mistakes

⚠️

N+1 query problem — loading 100 users then accessing $user->posts on each triggers 101 queries. Use eager loading: User::with('posts')->get().

⚠️

Using ORM for everything — complex reports with aggregations, sub-queries, and unions are often cleaner in raw SQL. Use the ORM for CRUD, raw SQL for analytics.

💡

Pro Tip: Enable query logging in development to see exactly what SQL your ORM generates. You'll be surprised how many queries run on a single page.

📋 Quick Reference — ORM

Concept	Eloquent (Laravel)
Find by ID	User::find(1)
Filter	User::where('role', 'admin')->get()
Create	User::create(['name' => 'Alice'])
Update	$user->update(['name' => 'Bob'])
Relationship	$user->posts (hasMany)
Eager load	User::with('posts')->get()

🎉 Lesson Complete!

You understand query builders and ORMs! Next, dive into authentication with JWT, OAuth2, and API tokens.