toSql() returning "incorrect" query

Hello

I have the following code:

public function activeCoupons()
    {
        return $this->coupons()->active();
    }

    public function coupons()
    {
        return $this->hasManyThrough(Coupon::class, Saving::class);
    }

If I dd($this->coupons()->active()->toSql()); the query I get starts like this:

select * from `coupons` inner join `savings` ...

However, the query that really runs (which you can see in the Laravel error debug page if you add an error to the query on purpose) is:

select `coupons`.*, `savings`.`shop_id` from `coupons` inner join `savings` ...

Quite different as you can see!

Is this on purpose or is it a bug? And if it's the former, what it the logic of this behavior?

I was trying to add a raw select to the query but it was producing incorrect results. When trying to debug with toSql() I couldn't find the problem, as the query that toSql() was giving me was not the one that was really running.

bobbybouwmann

7 years ago

Level 88

The best way to debug this is using the debugbar to see what the actual query is. When using relationships, Laravel runs multiple queries because it will first fetch the ids and use those for the next query to get the relationship. So when you use toSql it's probably not the final/only query!

symos

7 years ago

Level 1

Thanks very much for your reply.

Yes, if I understood one thing from the couple of times I used toSql() is that it's not a very reliable way to debug.

However, having said that, the reason I posted this was to see (just out of curiosity) if there is a logic behind what toSql() returns.

What you're saying makes sense, but in this case, I don't think it's a different query (what toSql() returns and what I'm seeing in the error page). It's the exact same query (same inner join, same where clauses, same order bys), but toSql() starts with

select * ...

when the actual query is

select coupons.*,  `savings`.`shop_id` ...

The way I found this is because I added a raw "Select *" to that query (to do a custom sort) and the results I got where including the savings fields, whereas before Laravel only brought me the coupon fields. This is in line with "select coupons.*" but not with what toSql() was telling me the query was.

So, if the "select coupons.*" was a different level query, then my "Select *" wouldn't affect results, as they would still be filtered at a next level.

Please or to participate in this conversation.