How do I get the actual query used to retrieve a relation?

Question

If I dump the sql for the query using toSql(), I get this:

select * from `permissions` inner join `vendor_permissions` on `permissions`.`id` = `vendor_permissions`.`permission_id` where `vendor_permissions`.`vendor_id` = ?

But the ACTUAL query that is being sent to my database, is this:

select `permissions`.*, `vendor_permissions`.`vendor_id` as `pivot_vendor_id`, `vendor_permissions`.`permission_id` as `pivot_permission_id` from `permissions` inner join `vendor_permissions` on `permissions`.`id` = `vendor_permissions`.`permission_id` where `vendor_permissions`.`vendor_id` = ?

as you can see, they are quite a bit different. Why is this? I am trying to clear the query cache for a relation, and to do that I need to be able to get the actual query being used, so how do I do that?

Answer 1

To reconstruct the query that is actually being run, you need to add to the select the pivot columns. This function does it:

function buildRelationQuery($relation)
{
    $wheres = $relation->newPivotStatementForId(null)->wheres;
    $select = [$relation->getRelated()->getTable().'.*'];
    foreach($wheres as $w) {
        $select[] = $relation->getTable().'.'.$w['column'].' as pivot_'.$w['column'];
    }
    $relation->getQuery()->addSelect($select);
    return $relation;
}

So to clear the cache for the relation attribute, you can do this:

Cache::forget(
    buildRelationQuery($model->relationattribute())
        ->getQuery()
        ->getQuery()
        ->getCacheKey()
    );

Answer 2

The simplest way to see exactly which queries are being run is to use the Laravel Debugbar by barryvdh:

Laravel 4

https://github.com/barryvdh/laravel-debugbar/tree/1.8

Laravel 5

https://github.com/barryvdh/laravel-debugbar

This way you don't have to write specific code targeting queries when you need them, you can just click on the handy bar at the bottom to see what queries it has executed.

It event has "stacked queries" which allows you to see queries executed as the result of an event that was fired during the request.