Laravel 5 - Elequent GROUP BY is failing

Question

I am trying to do the following:

I have two tables:

1) Content
   id, 
   section_id
   parent_id, 
   sequence,

2) Sections
   id,
   title, 
   description,
   date_entered

Each Content has to have a section, which is defined by a foreign key, the content can have a sub section, where if the content have the same parent_id - then this is classed as a sub section.. So for example:

 1. My first section
    1.1. My first sub section 
 2. My second section
 3. My third section
    3.1 My third sub section 

I am using Eloquent and have used the following:

$sections = Content::orderBy('sequence', 'desc')
               ->groupBy('parent_id')->get();

If I output these within a foreach loop, then it will only show one of the records, where there are multiple that have the same parent_id, if I remove the groupBy then it will display all the records, but not in groups

I have set up the relationship so that: there is a belongsTo relationship.. So

  public function sections()
  {
     return $this->belongsTo('App\Sections', 'section_id');
  }

Where am I going wrong here?

UPDATE:

     1) Content
           id, 
           section_id
           parent_id, 
           sequence,

           FOREIGN KEYS:
           parent_id -> id,

           section_id -> id on Sections (below)

2) Sections
   id,
   title, 
   description,
   date_entered

Answer 1

If I understand correctly, you'd like to fetch a list of Content objects together with their children Content objects, correct?

Easiest way to do that is to create a parent-child relation in your Eloquent Content model and then use that to load parents with children:

<?php
class Content extends Model {
  public function children() {
    //this defines a relation one-to-many using parent_id field as the foreign key
    return $this->hasMany(Content::class, 'parent_id'); 
  }

  public function parent() {
    return $this->belongsTo(Content::class, 'parent_id'); 
  }

  public function section() {
    return $this->belongsTo(Section::class);
  }
}

Then, if you want to list Content objects their Section together with with their children and their sections, you can fetch the data like that:

$contents = Content::with(['children', 'section', 'children.section'])->whereNull('parent_id')->get();

$contents will contain a collection of all Content objects that have no parent. Each of the objects will have a $content->children attribute that holds a collection of all children Content objects. All children objects will also hold a reference to their parent in $childContent->parent. Both parents and children will have their corresponding section in ->section attribute.

If you wanted to display some Content hierarchy now in your Blade template, you can pass the $contents variable to the view and do the following:

<ul>
@foreach($contents as $content)
  <li>{{$content->title}}</li>
  @if($content->children->count() > 0)
    <ul>
      @foreach($content->children as $childContent)
        <li>{{$childContent->title}}</li>
      @endforeach
   </ul>
  @endif
@endforeach
</ul>  

I noticed that you have a sequence field in your model. I assue that you want content to be sorted by that field. In this case you'll need modify the way you fetch the data:

$contents = Content::with(['children' => function($builder) {
  $builder->orderBy('sequence', 'desc');
}, 'section', 'children.section'])->whereNull('parent_id')->get();