Database & Eloquent Orm Interview Questions

Eloquent ORM (Object-Relational Mapping) is Laravel's built-in ORM that allows developers to interact with database tables using elegant object-oriented models. Each database table has a corresponding Model that is used for interacting with that table.

Models can be created using Artisan command: 'php artisan make:model ModelName'. Adding -m flag creates a migration file too. Models are placed in app/Models directory and extend Illuminate\Database\Eloquent\Model class.

Migrations are like version control for databases, allowing you to define and modify database schema using PHP code. They enable team collaboration by maintaining consistent database structure across different development environments.

Basic Eloquent operations include: Model::all() to retrieve all records, Model::find(id) to find by primary key, Model::create([]) to create new records, save() to update, and delete() to remove records.

Relationships are defined as methods in model classes. Common types include hasOne(), hasMany(), belongsTo(), belongsToMany(). These methods specify how models are related to each other in the database.

Seeders are used to populate database tables with sample or initial data. They are created using 'php artisan make:seeder' and can be run using 'php artisan db:seed'. Useful for testing and initial application setup.

Eloquent provides methods like where(), orWhere(), whereBetween(), orderBy() for querying. Example: User::where('active', 1)->orderBy('name')->get(). Queries return collections of model instances.

Mass assignment allows setting multiple model attributes at once. $fillable property in models specifies which attributes can be mass assigned, while $guarded specifies which cannot. This prevents unintended attribute modifications.

Database connections are configured in config/database.php and .env file. Multiple connections can be defined for different databases. The default connection is specified in .env using DB_CONNECTION.

Model factories generate fake data for testing and seeding. Created using 'php artisan make:factory'. They use Faker library to generate realistic test data. Factories can define states for different scenarios.

Eager loading reduces N+1 query problems by loading relationships in advance using with() method. Example: User::with('posts')->get(). Can eager load multiple or nested relationships: with(['posts', 'profile']).

Model events are triggered during various stages of a model's lifecycle (creating, created, updating, etc.). Observers group event listeners for a model in a single class. Created using 'php artisan make:observer'.

Soft deletes add deleted_at timestamp instead of actually deleting records. Use SoftDeletes trait in model and add deleted_at column in migration. Provides withTrashed(), onlyTrashed() scopes for querying.

Query scopes are reusable query constraints. Global scopes automatically apply to all queries. Local scopes are methods prefixed with 'scope' and can be chained. Example: public function scopeActive($query) { return $query->where('active', 1); }

Polymorphic relationships allow a model to belong to multiple types of models. Uses morphTo(), morphMany(), morphToMany() methods. Requires type and ID columns in database. Common for comments, likes, tags scenarios.

Accessors and mutators modify attribute values when retrieving or setting them. Defined using get{Attribute}Attribute and set{Attribute}Attribute methods. Useful for formatting dates, calculating values, or encoding data.

Transactions ensure multiple database operations succeed or fail as a unit. Use DB::transaction() or beginTransaction(), commit(), rollback(). Can be nested. Important for maintaining data integrity.

Pivot tables implement many-to-many relationships. Created using createPivotTable migration method. Can have additional columns accessed via withPivot(). Pivot model can be customized using using() method.

Query results can be cached using remember() or rememberForever() methods. Cache duration and key can be specified. Useful for expensive queries. Example: User::remember(60)->get();

Subqueries can be used in selects and joins using addSelect() and joinSub(). Useful for complex queries involving aggregates or related data. Can improve performance over multiple queries.

Custom collections extend Illuminate\Database\Eloquent\Collection. Override newCollection() in model to use custom collection. Add methods for specialized collection operations specific to model type.

Models can be replicated using replicate() method. Specify which attributes to exclude. Handle relationships manually. Useful for creating similar records with slight modifications.

Custom model binding resolvers modify how models are resolved from route parameters. Defined in RouteServiceProvider using Route::bind() or by overriding resolveRouteBinding() in model.

Composite keys require overriding getKeyName() and getIncrementing(). Additional configuration needed for relationships. Consider performance implications. May need custom query scopes.

Custom query builders extend Illuminate\Database\Eloquent\Builder. Override newEloquentBuilder() in model. Add methods for specialized query operations. Useful for complex, reusable queries.

Customize toArray() and toJson() methods. Use hidden and visible properties. Implement custom casts for complex attributes. Handle relationship serialization. Consider API resource classes.

Sharding requires custom connection resolvers. Override getConnection() in models. Implement logic for determining shard. Consider transaction and relationship implications across shards.

Real-time observers can broadcast model changes using events. Implement ShouldBroadcast interface. Configure broadcast driver. Handle authentication and authorization for broadcasts.

Recursive relationships use self-referential associations. Implement methods for traversing tree structures. Consider performance with nested eager loading. Use closure table pattern for complex hierarchies.

Custom connection handling requires extending Connection class. Implement custom query grammar and processor. Handle transaction management. Consider read/write splitting scenarios.