Is there a way to get all queries from a builder, specifically query getting the eager loaded information

Question

Using Laravel 5.8.11 I have a polymorphic has many relationship a clients can have many addresses addresses, other thing can have addresses in this case so it's polymorphic. (called addresses)

I also keep a polymorphic "has one" relationship to the clients current address. (called address)

When I try to run this query:

return Client::with('address')
    ->where('first_name', 'like', '%' . $search . '%')
    ->orWhere('last_name', 'like', '%' . $search . '%')
    ->get();

I'm getting some odd results back. I get all the clients but only the last client has an address.

When checking the seed data all the other addresses are there, and if I change the results by sending in different search criteria, the last person will always be returned with the correct address, while the others will always have null.

It's not a UI issue as I'm just dumping the result in a unit test

Here is the code for the relationships on the client object.

   /**
     * Get the address records associated with the Model.
     */
    public function addresses() :MorphMany
    {
        return $this->morphMany(Location::class, 'has_location')->latest();
    }

    /**
     * Get the latest address record associated with the Model.
     */
    public function address() :MorphOne
    {
        return $this->morphOne(Location::class, 'has_location')->latest('id')->limit(1);
    }

As I try and figure out what I'm call from the database, I'm trying to trap the SQL thats being sent. I'm just using a simple function to put rebuilding the full sql for easier reading with this:

function ddsql($builder, $replace = false)
{
    if (!$replace)
        return ddf([$builder->toSql(), $builder->getBindings()]);
    $sql = $builder->toSql();
    ddf(Str::replaceArray('?', $builder->getBindings(), $sql));
}

ddf() is basically dd() but with some helpers to show the calling function from the backtrace logs has no bearing.

I'm using it as follows on the original code block

ddsql(
    Client::with('address')
        ->where('first_name', 'like', '%' . $search . '%')
        ->orWhere('last_name', 'like', '%' . $search . '%')
);

Which returns the first query but never the eager loading step which si probably where the issue is.

Array
[
    "select * from `clients` where `first_name` like ? or `last_name` like ?",
    [
        "%co%",
        "%co%"
    ]
]

However, I only ever get the first query back from the builder.

Is there a way to get all the queries?

Any Advice is appreciated.

---

UPDATE:

Per @tim-lewis, (thanks) I updated the function I was using to this it's ugly but gets the job done.

/** @test */
    public function clients_matching_search_are_returned_with_their_addresses() {
        $client1 = ObjectFactory::clientWithAnAddress(['client'=>['first_name'=>'Test 1']]);
        $client2 = ObjectFactory::clientWithAnAddress(['client'=>['first_name'=>'Test 2']]);
        $client3 = ObjectFactory::clientWithAnAddress(['client'=>['first_name'=>'Test 3']]);


        ddSqlCollection(function(){return Client::with('address')->get();});

}

//WHERE ddSqlCollection is the following

function ddSqlCollection($callback, $replace = false){

    $callback = $callback ?: function () {
        return true;
    };

    DB::flushQueryLog();
    DB::enableQueryLog();
    $callback();
    DB::disableQueryLog();
    ddf(DB::getQueryLog());

}

I'm sure there is a prettier way and I'm still not sure why the data I'm getting doesn't match what I believe SQL should be returning, however I'll ask those in a more focused follow up question.

Here is the output which was expected:

"ddSqlCollection(["Closure"])"
"Tests\Feature\Clinet\SearchTest\clients_matching_search_are_returned_with_their_addresses([])"
"-------------------------------------------"
array:2 [
  0 => array:3 [
    "query" => "select * from "clients""
    "bindings" => []
    "time" => 0.06
  ]
  1 => array:3 [
    "query" => "select * from "locations" where "locations"."has_location_id" in (1, 2, 3) and "locations"."has_location_type" = ? order by "id" desc limit 1"
    "bindings" => array:1 [
      0 => "App\Client"
    ]
    "time" => 0.19
  ]
]

Answer 1

Unfortunately, ->toSql() doesn't show the full query, but there is a way to turn on query logging and display the last query run, including sub-queries and bindings, and that's using the enableQueryLog() function:

\DB::connection()->enableQueryLog();

Then, after executing a query, you can immediately dd() the result to show your query. For example:

$user = User::with(["role"])->first();
$lastQuery = collect(\DB::getQueryLog())->last(); // or end(\DB::getQueryLog());

dd($lastQuery);

In my situation, this results in the following:

array:3 [▼
  "query" => "select `roles`.*, `role_users`.`user_id` as `pivot_user_id`, `role_users`.`role_id` as `pivot_role_id` from `roles` inner join `role_users` on `roles`.`id` = `role_users`.`role_id` where `role_users`.`user_id` in (?)"
  "bindings" => array:1 [▼
    0 => 1
  ]
  "time" => 0.9
]

Note: Using collect(\DB::getQueryLog())->last(); is just a quick way to return only the last query run. If you want to see all queries run in the current request, simply do:

dd(\DB::getQueryLog());