[NEW FEATURE]Dynamic Multi-Schema Query Support with Prioritization

[lduf]

Description

This feature aims to enable dynamic querying across multiple database schemas (e.g., GLOBAL, SPE) while respecting a defined priority order. Each model should define a databaseScope property, listing the schemas to query in priority order. For example:

• databaseScope = ['global', 'team'] queries both GLOBAL and team-specific schemas, with GLOBAL taking precedence.
• Queries dynamically adapt to team_id for identifying the specific schema.

The system must allow developers to use Eloquent-style methods (all, find, where, etc.) transparently, while the underlying logic manages multi-schema querying, data merging, and conflict prioritization.

Key Features:

1. Query data from multiple schemas dynamically using team_id.
2. Merge data from the schemas based on priority order.
3. Extend Eloquent functionality to support this behavior without disrupting standard query patterns.
4. Implement prioritization logic for cases where data exists in multiple schemas.
5. Ensure relations (hasMany, belongsTo, etc.) respect the same priority and query logic.

---

Tasks

1. Define a Multi-Schema Model Base Class:

   • Create a parent class (e.g., MultiSchemaModel) extending Eloquent.
   • Add a databaseScope property to define the schemas to query.
   • Implement methods for:
     • Fetching all data across schemas (allFromScopes).
     • Applying filters dynamically (queryFromScopes).
     • Handling prioritization during data merging (allWithPriority).

2. Override Core Eloquent Query Methods:

   • all():
     • Query all records across the schemas defined in databaseScope.
     • Merge the results respecting the priority order.
   • find():
     • Retrieve a single record by ID, searching across all schemas in the defined order.
   • where():
     • Apply query filters dynamically on data from multiple schemas.
   • Ensure the overrides remain transparent for users.

3. Implement Multi-Schema Support for Relations:

   • Extend Eloquent relations (hasMany, belongsTo, etc.) to respect the databaseScope.
   • Provide utilities like hasManyUsingScopes to simplify querying related data across schemas.
   • Test cases with nested relationships.

4. Dynamic Schema Resolution Using team_id:

   • Detect the correct schema dynamically for a team_id (e.g., team_1 for team_id=1).
   • Update database connections dynamically for team-specific queries.

5. Develop Sample Models:

   • Define examples such as:
     • Country: Queries both GLOBAL and team-specific schemas.
     • Item: Queries team-specific schema only.

6. Write Tests:

   • Validate querying data from multiple schemas.
   • Test prioritization logic when merging data.
   • Cover edge cases:
     • Table exists only in GLOBAL.
     • Table exists only in SPE.
     • Conflicting data between schemas.

7. Document the System:

   • Provide clear documentation for using the MultiSchemaModel base class.
   • Include usage examples for controllers and services.

---

Code Suggestions

1. Core MultiSchemaModel Class
This parent class will manage multi-schema querying and data merging.

namespace App\Models;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Support\Facades\DB;

abstract class MultiSchemaModel extends Model
{
    protected static $databaseScope = ['global'];

    /**
     * Fetch all data across defined scopes.
     * @param int|null $teamId
     * @return \Illuminate\Support\Collection
     */
    public static function allFromScopes($teamId = null)
    {
        $results = collect();

        foreach (static::$databaseScope as $scope) {
            $connection = $scope === 'team' && $teamId ? "team_{$teamId}" : $scope;
            $table = (new static)->getTable();

            $data = DB::connection($connection)->table($table)->get();
            $results = $results->merge($data);
        }

        return $results;
    }

    /**
     * Query with filters across defined scopes.
     * @param callable|null $callback
     * @param int|null $teamId
     * @return \Illuminate\Support\Collection
     */
    public static function queryFromScopes($callback = null, $teamId = null)
    {
        $query = collect();

        foreach (static::$databaseScope as $scope) {
            $connection = $scope === 'team' && $teamId ? "team_{$teamId}" : $scope;
            $table = (new static)->getTable();

            $data = DB::connection($connection)->table($table);
            if ($callback) {
                $data = $callback($data);
            }

            $query = $query->merge($data->get());
        }

        return $query;
    }

    /**
     * Merge data with prioritization.
     * @param int $teamId
     * @return \Illuminate\Support\Collection
     */
    public static function allWithPriority($teamId)
    {
        $globalData = DB::connection('global')->table((new static)->getTable())->get();
        $specificData = DB::connection("team_{$teamId}")->table((new static)->getTable())->get();

        $mergedData = $specificData->map(function ($item) use ($globalData) {
            $globalItem = $globalData->firstWhere('id', $item->id);
            return $globalItem ?: $item; // Prioritize GLOBAL over SPE
        });

        return $globalData->merge($mergedData);
    }
}

• Override Methods:
  • all(): Fetch data across schemas and merge results transparently.

  public static function all($columns = ['*'])
  {
      return static::allFromScopes(); 
  }

• find(): Retrieve a single record by ID from all schemas.

public static function find($id, $columns = ['*'])
{
   $results = static::queryFromScopes(function ($query) use ($id, $columns) {
       return $query->where('id', $id);
   });

   return $results->first();
}

• where(): Apply query filters and merge results dynamically.

public static function where($column, $operator = null, $value = null, $boolean = 'and')
{
    return static::queryFromScopes(function ($query) use ($column, $operator, $value, $boolean) {
        return $query->where($column, $operator, $value, $boolean);
    });
}

• Dynamic Relationships:
  • Add methods like hasManyUsingScopes to manage relations across schemas.

2. Example Usage in Models
Define models with their databaseScope

• Country Model:

  namespace App\Models;
  
  class Country extends MultiSchemaModel
  {
      protected static $databaseScope = ['global', 'team'];
      protected $table = 'countries';
  
  }

3. Example Usage in Controllers
Querying data remains transparent for developers.

use App\Models\Country;

$countries = Country::all(); // GLOBAL + SPE
$filteredCountries = Country::where('name', 'like', '%France%')->get(); // GLOBAL + SPE with filter
$country = Country::find(42); // Search by ID in GLOBAL + SPE

---

Discussion

1. Performance Implications:

   • How to handle large datasets when merging results?
   • Should we explore database-level optimizations (e.g., UNION ALL) for faster queries?

2. Conflict Resolution:

   • Should GLOBAL always take precedence, or should this be configurable?
   • How to handle cases where SPE overrides GLOBAL (future extension)?

3. Testing Strategy:

   • Ensure coverage for edge cases (e.g., missing tables, conflicting data).
   • Benchmark performance with significant data volumes across schemas.

4. Caching Options:

   • Can frequently queried data (e.g., list_countries) be cached to improve performance?

---

Deliverables

1. Fully functional MultiSchemaModel class with dynamic schema support.
2. Example models and controllers demonstrating usage.
3. Test coverage for all cases.
4. Documentation for developers.

---

Related Issues

Blocked by #512.

[github-actions]

AI-Assisted Response:

Feature Request: Dynamic Multi-Schema Querying with Eloquent

Summary

The proposed feature involves enhancing an Eloquent-based system to support dynamic queries across multiple database schemas with precedence handling. Each model would specify a databaseScope array indicating schemas and their priority for querying. Key functions would extend Eloquent's all, find, where, and relationship methods to operate within this multi-schema context, automatically determining the correct schema to query based on a team_id.

Context

• Database schemas include a GLOBAL schema and multiple team-specific schemas.
• Priority determines which schema’s data is considered first when merging results from different schemas.

Technical Tasks

1. Create a MultiSchemaModel base class extending Laravel's Eloquent with a $databaseScope property.
2. Overhaul Eloquent methods (all, find, where) to work with the defined schema scopes and priority merging logic.
3. Ensure Eloquent relationships work in this multi-schema environment.
4. Implement dynamic resolution of schemas using provided team_id.
5. Develop sample models for real-world testing with multiple schema scopes.
6. Ensure comprehensive test coverage for various scenarios such as conflicts and schema-specific data.
7. Write documentation covering how to use the new system, with sample models and code examples outlined.

Code Suggestions (Note: These are preliminary suggestions and may need further refinement)

• An abstract MultiSchemaModel class is provided to handle multi-schema querying and result merging.
• Overriding methods (all, find, where) to fetch data across schemas transparently.
• Dynamic methods for relationships across schemas.

Discussion Points

• Concerns regarding performance with large dataset merging.
• Possible database optimizations for quicker querying.
• Global precedence rules and the potential for custom configurations.
• Extending conflict resolution strategies.
• Testing best practices for robustness against edge cases.
• Caching frequently queried data for performance enhancement.

Deliverables

1. Functional MultiSchemaModel class with dynamic schema support.
2. Sample models and controllers for demonstrating practical use.
3. Comprehensive tests addressing all specified scenarios.
4. Developer documentation with clear guidelines and examples.

Clarification Questions

• @lduf Would there be cases where the schemas to be queried are not known until runtime, requiring dynamic addition to databaseScope?
• How should we handle schema-specific custom queries that might not be covered by the base overrides?
• Regarding conflict resolution,

[lduf]

What about adding ?

-> push to the lowest scope level ?

eg.
databaseScope = ["global", "team"]; // lowest level = team
databaseScope = ["team", "global"]; // lowest level = global

[vqlion]

Nice!

About performance implications: I don't know if it matters too much, since we'll use this typically for lists or item_types, and they shouldn't end up with an enormous amount of lines?

About adding: should some data be addable to both global and team-scope? I can't think of data that could be in one or another, to me it's either one or the other (e.g. users and always in global, items data always in team...)

[lduf]

About performance implications: I don't know if it matters too much, since we'll use this typically for lists or item_types, and they shouldn't end up with an enormous amount of lines?

True

About adding: should some data be addable to both global and team-scope? I can't think of data that could be in one or another, to me it's either one or the other (e.g. users and always in global, items data always in team...)

It depends on how we handle templating. For example, someone might create something within their team and then want to make it accessible to all other teams (± adding a new template).

So, we could encounter situations where something is added both locally and globally. However, I think this would follow a very specific process, and we wouldn’t directly push data to both scopes simultaneously.

[lduf]

What about adding ?

-> push to the lowest scope level ?

eg. databaseScope = ["global", "team"]; // lowest level = team databaseScope = ["team", "global"]; // lowest level = global

Why not having a dedicated parameters :

databaseWriteScopes -> handle reading operations
databaseReadScopes -> handle add / update / delete operations

[vqlion]

So, we could encounter situations where something is added both locally and globally. However, I think this would follow a very specific process, and we wouldn’t directly push data to both scopes simultaneously.

In this case, it might just be something like:

• Create something locally as normal
• Copy it in global as part of a separate process

So we keep the frontier between the two scopes and we can still get data from one to another, kind of like what you mentioned:

Why not having a dedicated parameters :

databaseWriteScopes -> handle reading operations
databaseReadScopes -> handle add / update / delete operations