Use a model type to fetch another table based on its table

Question

I have a Todo and this Todo can have a SubTodo. Each SubTodo can be of the following types:

text
dropdown
boolean

Each of these SubTodo have specifics that is related to each one of its type so I resolved to separated into its own table. The database table structure is as follow:

todo
- id

sub_todo
- id
- todo_id
- type [dropdown, text, boolean]

sub_todo_dropdown
- id
- sub_todo_id

sub_todo_text
- id
- sub_todo_id

sub_todo_boolean
- id
- sub_todo_id

A SubTodo needs to have a relationship called meta that will resolve the fields that is specific to which type. I have defined the following relationship:

Models/SubTodo.php

// The SubTodo will have meta information on other tables
// that will be depending on the type of the SubTodo.
// To fetch those meta information's we will
// have this set where will map the
// relationship to its model
const META_MODELS = [
    self::SUB_TODO_TYPE_TEXT => SubTodoText::class,
    self::SUB_TODO_TYPE_BOOLEAN => SubTodoBoolean::class,
    self::SUB_TODO_TYPE_DROPDOWN => SubTodoDropdown::class,
];

public function meta(): HasOne
{
    return $this->hasOne(self::META_MODELS[$this->type], 'sub_todo_id');
}

But when I try to load this relationship with

$subTodos = SubTodo::with('meta')->paginate();

I get "message": "Undefined index: ". After doing

public function meta(): HasOne
{
    dd($this->type);
    return $this->hasOne(self::META_MODELS[$this->type], 'sub_todo_id');
}

I get null. My best guess is that the model wasn't loaded yet so I need to load the model first and then call meta:

$subTodos = SubTodo::limit(10)->get()->each(function (SubTodo $subtodo) {
   $subtodo->load('meta');
});

But this approach will cause a N+1 problem. Is there any way I can achieve to load meta without having to load all models first? Is this a good usage for one to one polymorphic relationship?

Do you need to maintain the three different tables or it can be done within one table? If your answer is with one table, you can use query scope. — Mehedi Hassan, May 29 '21 at 18:06
For better maintanability into the future this needs to be three different tables. For example, the `dropdown` type has a `dropdown` attached to it while a `text` type doesn't have a `dropdown` attached to it. — Bruno Francisco, May 29 '21 at 18:08
how you are setting $this->type ? Through any constructor or any setter or default property value? — Mehedi Hassan, May 29 '21 at 18:12
the `$this->type` is an attribute of the model. It comes directly from the migrations — Bruno Francisco, May 29 '21 at 18:16
I am afraid you are maybe missing the $this->type at self::META_MODELS[$this->type]. There might a case it only works for any of the three value in the META_MODELS array and getting null for the other two — Mehedi Hassan, May 29 '21 at 18:27
Please check this out, maybe your answer is here https://stackoverflow.com/questions/43668153/how-to-setup-conditional-relationship-on-eloquent — Tohid Dadashnezhad, May 29 '21 at 19:09
@TohidDadashnezhad That approach makes you load first the `User` model and then eager load the resource creating `N+1` problems — Bruno Francisco, May 29 '21 at 19:31

Tohid Dadashnezhad · Accepted Answer · 2021-05-29T21:00:59.367

Alright, I think there is no way except overriding the Model.

1 Create the SubTodoBuilder class:

<?php


namespace App\Builder;


use Illuminate\Database\Eloquent\Builder;

class SubTodoBuilder extends Builder
{
  const RELATIONS = [
     "text" => "SubTodoText",
     "boolean" => "SubTodoBoolean",
     "dropdown" => "SubTodoDropdown"
  ];

 /**
  * Eager load the relationships for the models.
  *
  * @param array $models
  * @return array
  */
 public function eagerLoadRelations(array $models): array
 {
     foreach ($this->eagerLoad as $name => $constraints) {
         if ($name === "meta") {
             $groupedModels = collect($models)->groupBy("type");
             $models = [];
             foreach ($groupedModels as $type => $subModels) {
                 $relation = self::RELATIONS[$type];
                 $result = $this->eagerLoadRelation($subModels->all(), $relation, $constraints);
                 $models = array_merge($models, $result);
             }
         }
         //This part may need some modification
         if (strpos($name, '.') === false && $name !== "meta") {
             $models = $this->eagerLoadRelation($models, $name, $constraints);
         }
     }
     return $models;
 }
}

2 In SubTodo model:

<?php

 namespace App\Models;

 use App\Builder\SubTodoBuilder;
 use Illuminate\Database\Eloquent\Builder;
 use Illuminate\Database\Eloquent\Factories\HasFactory;
 use Illuminate\Database\Eloquent\Model;
 use Illuminate\Database\Eloquent\Relations\HasOne;

 class SubTodo extends Model
 {
   use HasFactory;


   protected $fillable = [
       "todo_id",
       "type"
   ];

   public function SubTodoText(): HasOne
   {
       return $this->hasOne(SubTodoText::class);
   }

   public function SubTodoDropDown(): HasOne
   {
       return $this->hasOne(SubTodoDropdown::class);
   }

   public function SubTodoBoolean(): HasOne
   {
       return $this->hasOne(SubTodoBoolean::class);
   }

   public function meta(): HasOne
   {
       return $this->hasOne("meta");
   }

   /**
    * Create a new Eloquent query builder for the model.
    *
    * @param  \Illuminate\Database\Query\Builder  $query
    * @return Builder|static
    */
   public function newEloquentBuilder($query)
   {
       return new SubTodoBuilder($query);
   }

}

3 In your controller

public function index()
{
   $subTodos=SubTodo::with("meta")->get();
}

Now if you have 30 SubTodo to fetch and each 10 SubTodo has one of the three types, it will execute 1+3 queries.

Use a model type to fetch another table based on its table

1 Answers1