Laravel $query->toSql() loses relation extras
<p>I've got the below function, which attempts to match users on specific whitelisted fields, which works brilliantly, for small amounts of data, but in our production environment, we can have > 1 million user records, and Eloquent is (understandably) slow when creating models in: <code>$query-&gt;get()</code> at the end. I asked a question this <a href="https://stackoverflow.com/questions/34672381/laravel-querybuilder-to-dbselect">morning</a> about how to speed this up and the accepted answer was brilliant and worked a treat, the only problem now, is that the resulting query being sent to <code>DB::select($query-&gt;toSql()...</code> has lost all of the required extra relational information I need. So is there any way (keeping as much of the current function as possible), to add joins to <code>DB::select</code> so that I can maintain speed and not lose the relations, or will it require a complete re-write?</p>

<p>The <code>recipients</code> query should include relations for tags, contact details, contact preferences etc, but the resulting sql from <code>$query-&gt;toSql()</code> has no joins and only references the one table.</p>

<pre><code>public function runForResultSet()
{
    $params = [];

    // Need to ensure that when criteria is empty - we don't run
    if (count($this-&gt;segmentCriteria) &lt;= 0) {
        return;
    }

    $query = Recipient::with('recipientTags', 'contactDetails', 'contactPreferences', 'recipientTags.tagGroups');

    foreach ($this-&gt;segmentCriteria as $criteria) {
        $parts = explode('.', $criteria['field']);
        $fieldObject = SegmentTableWhiteListFields::where('field', '=', $parts[1])-&gt;get();
        foreach ($fieldObject as $whiteList) {
            $params[0] = [$criteria-&gt;value];

            $dateArgs = ((strtoupper($parts[1]) == "AGE" ? false : DatabaseHelper::processValue($criteria)));
            if ($dateArgs != false) {
                $query-&gt;whereRaw(
                    DatabaseHelper::generateOperationAsString(
                        $parts[1],
                        $criteria,
                        true
                    ),
                    [$dateArgs['prepared_date']]
                );
            } else {
                // Need to check for empty value as laravel's whereRaw will not run if the provided
                // params are null/empty - In which case we need to use whereRaw without params.
                if (!empty($criteria-&gt;value)) {
                    $query-&gt;whereRaw(
                        \DatabaseHelper::generateOperationAsString(
                            $parts[1],
                            $criteria
                        ),
                        $params[0]
                    );
                } else {
                    $query-&gt;whereRaw(
                        \DatabaseHelper::generateOperationAsString(
                            $parts[1],
                            $criteria
                        )
                    );
                }
            }
        }
    }

    // Include any tag criteria
    foreach ($this-&gt;segmentRecipientTagGroupCriteria as $criteria) {
        $startTagLoopTime = microtime(true);

        switch (strtoupper($criteria-&gt;operator)) {
            // IF NULL check for no matching tags based on the tag group
            case "IS NULL":
                $query-&gt;whereHas(
                    'recipientTags',
                    function ($subQuery) use ($criteria) {
                        $subQuery-&gt;where('recipient_tag_group_id', $criteria-&gt;recipient_tag_group_id);
                    },
                    '=',
                    0
                );
                break;
            // IF NOT NULL check for at least 1 matching tag based on the tag group
            case "IS NOT NULL":
                $query-&gt;whereHas(
                    'recipientTags',
                    function ($subQuery) use ($criteria) {
                        $subQuery-&gt;where('recipient_tag_group_id', $criteria-&gt;recipient_tag_group_id);
                    },
                    '&gt;=',
                    1
                );
                break;
            default:
                $query-&gt;whereHas(
                    'recipientTags',
                    function ($subQuery) use ($criteria) {
                        $dateArgs = (DatabaseHelper::processValue($criteria));

                        $subQuery-&gt;where('recipient_tag_group_id', $criteria-&gt;recipient_tag_group_id);

                        if ($dateArgs != false) {
                            $subQuery-&gt;whereRaw(
                                DatabaseHelper::generateOperationAsString(
                                    'name',
                                    $criteria,
                                    true
                                ),
                                [$dateArgs['prepared_date']]
                            );
                        } else {
                            // Need to check for empty value as laravel's whereRaw will not run if the provided
                            // params are null/empty - In which case we need to use whereRaw without params.
                            if (!empty($criteria-&gt;value)) {
                                $subQuery-&gt;whereRaw(
                                    \DatabaseHelper::generateOperationAsString(
                                        'name',
                                        $criteria
                                    ),
                                    [$criteria-&gt;value]
                                );
                            } else {
                                $subQuery-&gt;whereRaw(
                                    \DatabaseHelper::generateOperationAsString(
                                        'name',
                                        $criteria
                                    )
                                );
                            }
                        }
                    },
                    '&gt;=',
                    1
                );
        }
    }

    //$collection = $query-&gt;get(); // slow when dealing with &gt; 25k rows
    $collection = DB::select($query-&gt;toSql(), $query-&gt;getBindings()); // fast but loses joins / relations
    // return the response
    return \ApiResponse::respond($collection);
}
</code></pre>