Serialize Queries Laravel Package

Getting Started

Minimal Setup

1. Installation:

   composer require laravie/serialize-queries
   

   Publish the config (if needed):

   php artisan vendor:publish --provider="Laravie\SerializeQueries\SerializeQueriesServiceProvider"
   

2. Basic Usage: Serialize a query:

   use Laravie\SerializeQueries\Facades\SerializeQueries;
   
   $query = User::query()->where('active', true)->orderBy('name');
   $serialized = SerializeQueries::serialize($query);
   

   Deserialize and execute:

   $executed = SerializeQueries::deserialize($serialized);
   $results = $executed->get();
   

3. First Use Case: Cache query results for reports or dashboards:

   $cacheKey = 'recent_users';
   $serialized = cache()->get($cacheKey);
   if (!$serialized) {
       $serialized = SerializeQueries::serialize(User::query()->latest()->take(10));
       cache()->put($cacheKey, $serialized, now()->addHours(1));
   }
   $results = SerializeQueries::deserialize($serialized)->get();
   

Implementation Patterns

Query Serialization Workflows

1. Caching Strategies:

   • Short-lived queries (e.g., dashboard data):

     $serialized = SerializeQueries::serialize(Report::query()->where('period', $period));
     cache()->put("report_{$period}", $serialized, now()->addMinutes(5));
     

   • Long-lived queries (e.g., admin previews):

     $serialized = SerializeQueries::serialize(Order::query()->with('user')->where('status', 'pending'));
     cache()->forever("pending_orders_preview", $serialized);
     

2. API Rate Limiting: Serialize expensive queries to avoid repeated execution:

   $key = "user_{$userId}_expensive_query";
   $serialized = cache()->get($key);
   if (!$serialized) {
       $serialized = SerializeQueries::serialize(
           User::query()->with(['orders', 'posts' => function($q) {
               $q->where('published', true);
           }])
       );
       cache()->put($key, $serialized, now()->addHours(2));
   }
   

3. Background Jobs: Offload query serialization to a job for heavy computations:

   class SerializeHeavyQueryJob implements ShouldQueue
   {
       public function handle()
       {
           $serialized = SerializeQueries::serialize(
               Analytics::query()->whereBetween('created_at', [$start, $end])
           );
           cache()->put("analytics_{$start}_{$end}", $serialized, now()->addDays(7));
       }
   }
   

Integration Tips

1. Middleware for Cached Queries: Create middleware to auto-serialize/deserialize queries for specific routes:

   public function handle($request, Closure $next)
   {
       if ($request->has('cached_query')) {
           $results = SerializeQueries::deserialize($request->cached_query)->get();
           return response()->json($results);
       }
       return $next($request);
   }
   

2. Query Builder Extensions: Extend the query builder to add serialization methods:

   use Laravie\SerializeQueries\QueryBuilder;
   
   QueryBuilder::macro('serializeAndCache', function ($minutes) {
       $serialized = SerializeQueries::serialize($this);
       cache()->put('query_' . md5($this->toSql()), $serialized, $minutes);
       return $this;
   });
   

   Usage:

   User::query()->serializeAndCache(10)->get();
   

3. Dynamic Query Reconstruction: Rebuild queries dynamically from serialized data:

   $serialized = '{"query":"select * from users where active = ?","bindings":[1],"orderBy":["name"],"limit":10}';
   $query = SerializeQueries::deserialize($serialized);
   $query->where('id', '>', 100); // Modify before execution
   

Gotchas and Tips

Pitfalls

1. Schema Changes:

   • Issue: Deserializing a query against a database with altered schema (e.g., renamed columns) will fail.
   • Fix: Validate schema compatibility or use migrations to ensure consistency.
   • Workaround: Add a schema_version to your serialized payload and check it before execution.

2. Binding Type Mismatches:

   • Issue: Bindings serialized as strings may not match the expected type (e.g., string vs int).
   • Fix: Use serializeBindingsAs config option:

     'bindings' => [
         'serializer' => 'json', // or 'php'
         'as_array' => true,
     ],
     

3. Raw Expressions:

   • Issue: Raw SQL expressions (e.g., DB::raw('DATE(created_at)')) may not serialize/deserialize correctly.
   • Fix: Avoid raw expressions in serialized queries or use a custom serializer for them.

4. Memory Limits:

   • Issue: Complex queries with large with() relationships may exceed memory limits when serialized.
   • Fix: Use take() or select() to limit the query scope before serialization.

5. Timezone Sensitivity:

   • Issue: Queries with whereDate(), whereTime(), or timezone-aware comparisons may behave differently across environments.
   • Fix: Explicitly set timezone in your config:

     'timezone' => 'UTC',
     

Debugging

1. Inspect Serialized Payload:

   $serialized = SerializeQueries::serialize($query);
   dd($serialized); // Check for missing or malformed parts
   

2. Validate Bindings: Ensure bindings match the expected types:

   $query = SerializeQueries::deserialize($serialized);
   dd($query->getBindings()); // Debug bindings
   

3. Log Query Differences: Compare original and deserialized queries:

   $originalSql = User::query()->where('active', true)->toSql();
   $deserializedSql = SerializeQueries::deserialize($serialized)->toSql();
   \Log::info("Original SQL: {$originalSql}");
   \Log::info("Deserialized SQL: {$deserializedSql}");
   

Extension Points

1. Custom Serializers: Override the default serializer for specific query types:

   SerializeQueries::extend(function ($query) {
       if ($query instanceof \Laravie\SerializeQueries\CustomQuery) {
           return $query->customSerialize();
       }
       return null; // Use default
   });
   

2. Post-Deserialization Hooks: Modify queries after deserialization:

   SerializeQueries::deserialize($serialized, function ($query) {
       $query->where('deleted_at', null);
       return $query;
   });
   

3. Binding Sanitization: Sanitize bindings before execution (e.g., escape user input):

   SerializeQueries::deserialize($serialized, function ($query) {
       $bindings = $query->getBindings();
       $sanitized = array_map('htmlspecialchars', $bindings);
       $query->bindings = $sanitized;
       return $query;
   });
   

4. Query Diffing: Compare serialized queries for testing:

   $serialized1 = SerializeQueries::serialize(User::query()->where('active', true));
   $serialized2 = SerializeQueries::serialize(User::query()->where('active', false));
   dd(json_decode($serialized1) === json_decode($serialized2)); // false
   

Config Quirks

1. Excluded Attributes: Configure which query attributes to exclude:

   'exclude' => [
       'columns', // Exclude selected columns
       'wheres',  // Exclude where clauses (use with caution)
   ],
   

2. Binding Format: Choose between json or php serialization for bindings:

   'bindings' => [
       'serializer' => 'php', // Default is 'json'
   ],
   

3. Environment-Specific Settings: Override settings per environment in .env:

   SERIALIZE_QUERIES_BINDINGS_AS_ARRAY=true
   SERIALIZE_QUERIES_TIMEZONE=America/New_York