Laravel Mysql Schema Relations

Question

I have pages table and logos, faqs, carousel, testmonials, cta tables. Each page can have sections of group of logos, group of faqs, single carousel, can have more than one ctas like page builder. How to establish mysql schema relations in Laravel.

I tried Pages - one to many - sections - many to many logos / faqs. But not getting desired api format. I want to achive below api format for a single page. Can any one suggest the DB schema.

    {
      "section_type": "logos",
      "data": [
        { "id": 1, "image": "adsadfa", "order": 1 },
        { "id": 1, "image": "aadfad", "order": 1 }
      ]
    },
    {
      "section_type": "cta",
      "data": { "id": 1, "image": "adfa" }
    },
    {
      "section_type": "faqs",
      "data": [
        { "id": 1, "faq_que": "data", "order": 1 },
        { "id": 1, "faq_que": "data", "order": 2 }
      ]
    },
    {
      "section_type": "carousel",
      "data": { "id": 1, "image": "adfa" }
    },
    {
      "section_type": "cta",
      "data": { "id": 1, "image": "adfa" }
    }
  ]
}

Answer 1

Let your ctas, testimonials, faqs, carousels, logos be the seperate tables as it is like you have right now, and make many to many polymorphic relationship for showing groupwise multiple data having below DB structure:

Table : page_types

id | key | name  | created_at
------------------------------------
1  |logos| Logos | 2023-05-06 15:30:19
2  |cta  | CTA   | 2023-05-06 15:30:19

Table : pages

id | home_page_type_id | showable_type     | showable_id | created_at
------------------------------------------------------------------------------
1  | 1                 | App\Models\Logo   | 1           | 2023-05-06 15:30:19
2  | 1                 | App\Models\Logo   | 2           | 2023-05-06 15:30:19
3  | 2                 | App\Models\Cta    | 1           | 2023-05-06 15:30:19

and then define your relation like Model : Page

    public function type()
    {
        return $this->belongsTo(PageType::class);
    }


    public function showable()
    {
        return $this->morphTo();
    }

Model : PageType

    public function page()
    {
        return $this->hasMany(Page::class);
    }

Then in your respective individual models make relation like below. Model: Logo

    public function show()
    {
        return $this->morphOne(Page::class, 'showable');
    }

Model: Cta

    public function show()
    {
        return $this->morphOne(Page::class, 'showable');
    }

And at last fetch all that at once in your desired format with below query in controller.

$data = HomePageType::select('id', 'key', 'name')
                            ->with(['page.showable'])
                            ->get();