title: Order by future event of a project ASC, followed by past events DESC
body: <p>Edit 2 // This works pretty well, but gives me duplicates and as many rows as I have events. I need the same number of rows as I have projects:</p>
  <pre><code>Project::select('projects.*')
  -&gt;leftJoin('events', 'events.project_id', '=', 'projects.id')
  -&gt;orderByRaw('(start_datetime &lt; NOW()),
  (case when start_datetime &gt; NOW() then start_datetime end) ASC,
  (case when start_datetime &lt; NOW() then start_datetime end) DESC')
  -&gt;with('events')
  -&gt;get(); // -&gt;unique() at the end works, but isn't quite right
  </code></pre>
  <p>Database:</p>
  <pre><code>Project: id
  Event: id, project_id, start_datetime
  </code></pre>
  <p>Project Model:</p>
  <pre><code>public function events(): HasMany
  {
      return $this-&gt;hasMany(Event::class);
  }
  </code></pre>
  <p>Event Model:</p>
  <pre><code>public function project(): BelongsTo
  {
      return $this-&gt;belongsTo(Project::class);
  }
  </code></pre>
  <p>OLD FROM HERE:</p>
  <hr />
  <p>Edit // This code is giving me the grouping and order I need, but how can I make it with a <code>projects</code> collection instead of a <code>projects</code> array?</p>
  <pre class="lang-php prettyprint-override"><code>$events = Event::with('project')
      -&gt;orderByRaw('(start_datetime &lt; NOW()),
      (case when start_datetime &gt; NOW() then start_datetime end) ASC,
      (case when start_datetime &lt; NOW() then start_datetime end) DESC')
      -&gt;get();
  
  $projects = [];
  foreach ($events as $event) {
      $projects[$event-&gt;project_id]['project'] = $event-&gt;project;
      $projects[$event-&gt;project_id]['events'][] = $event;
  }
  </code></pre>
  <p>...</p>
  <p>A project can have one or several events. Every event has a start date and time. How can I order by project that has the next upcoming event ASC, followed by projects with past events DESC. And within each project also order by next upcoming event ASC, followed by events DESC. Mouthful.</p>
  <p>This is what I have that is working great to sort the events within each project:</p>
  <pre class="lang-php prettyprint-override"><code>// Project Model
  public function events(): HasMany
  {
      return $this-&gt;hasMany(Event::class)
      -&gt;orderByRaw('(start_datetime &lt; NOW()),
      (case when start_datetime &gt; NOW() then start_datetime end) ASC,
      (case when start_datetime &lt; NOW() then start_datetime end) DESC');
  }
  </code></pre>
  <p>Now I need to find out how I can sort the projects based on the same criteria.</p>
  <p>I'd love to make it work with just eloquent if possible, but I'm starting to think that I need some joins or unions?</p>
  <p>I tried something random like this with collections, but I'm kind of lost:</p>
  <pre class="lang-php prettyprint-override"><code>$projects-&gt;with('events')-&gt;get()-&gt;sortBy(function ($sort) {
      return $sort-&gt;events-&gt;pluck('start_datetime')-&gt;first();
  });
  </code></pre>
  <p>Here's an example of how I want it to look. Put on your seatbelts ;)</p>
  <pre><code>Project A (1st because it has the next upcoming event)
  - Event 1 Start Date Today
  - Event 2 Start Date in 2 days
  - Event 3 Start Date Yesterday (latest past event of the project)
  - Event 4 Start Date 2 days ago (oldest event of the project at the bottom)
  
  Project B (2nd because it has the second next upcoming event)
  - Event 5 Start Date Tomorrow
  - Event 6 Start Date in 4 days
  - Event 7 Start Date Yesterday
  
  Project C (3rd because it doesn't have a future event, but has the latest past event)
  - Event 8 Start Date Yesterday
  - Event 9 Start Date 2 days ago
  
  Project D (4th because it has the oldest event)
  - Event 10 2 days ago
  - Event 11 3 days ago
  </code></pre>
  <p>I hope it makes sense.</p>