Writing a query that optionally only shows records between dates
<p>I am writing a MySQL query as part of a Laravel project which will allow me to only return records that match specific conditions.</p>

<p>For this solution, I have the following database migration:</p>

<pre><code>Schema::create('slides', function (Blueprint $table) {
    $table-&gt;increments('id');
    $table-&gt;integer('order')-&gt;default(0);
    $table-&gt;boolean('enabled')-&gt;default(0);
    $table-&gt;dateTime('start')-&gt;nullable();
    $table-&gt;dateTime('end')-&gt;nullable();            
});
</code></pre>

<p>This will be queried as follows, theoretically (this isn't correct SQL!):</p>

<pre><code>SELECT * FROM slides
IF enabled == 1
    IF start IS NOT NULL
        Only return the record if the current timestamp is after start
    IF end IS NOT NULL
        Only return the record if the current timestamp is before end
ORDER BY order ASC
</code></pre>

<p>It's important to bear in mind that neither start nor end is dependent on the other - I can set a start date and no end date, and have it appear from the start date indefinitely. The same applies to the end date.</p>

<p>I have some working code, adapted from the current answers, which seems to work correctly, but looks very convoluted:</p>

<pre><code>SELECT *
 FROM slides
WHERE `enabled` = 1
    AND ((`start` &lt; NOW() AND `end` &gt; NOW()) OR (`start` IS NULL AND `end` &gt; NOW()) OR (`start` &lt; NOW() AND `end` IS NULL) OR (`start` IS NULL AND `end` IS NULL))
ORDER BY `order`
</code></pre>

<p><strong>FOLLOW-UP</strong>
With the help of Spencer's solution (the accepted solution), I successfully created the Eloquent code to enable me to perform this query. For reference, here it is:</p>

<pre><code>$slides = Slide::where('enabled', true)
    -&gt;where(function($query) {
        return $query-&gt;where('start', '&lt;', Carbon::now()-&gt;toDateTimeString())
            -&gt;orWhereNull('start');
    })
    -&gt;where(function($query) {
        return $query-&gt;where('end', '&gt;', Carbon::now()-&gt;toDateTimeString())
            -&gt;orWhereNull('end');
    })                  
    -&gt;orderBy('order', 'asc')
    -&gt;get();
</code></pre>