Level 73
Do you use soft deletes on your model? Having deleted_at column in your table, Eloquent will automatically ignore those rows, unless you add withTrashed().
Jun 1, 2022
9
Level 27
Missing rows in Query Builder vs SQL
I extracted a long and complex query from Query Builder using ->toSql() and ran it manually to get this https://imgur.com/a/N7kSLAl
Then I used Laravel code to list the data in a browser, what happened was that:
- row3 was not present
- row5 and 6 was present (implying exact duplicates can returned)
- row100 was present
Any idea why row 3 could have disappeared?
Level 27
Yes I do, but doesn't it filter out using SQL? Let me check.
Level 27
@nakov ok you are right, there is no sign of deleted_at anywhere in the SQL.
Level 27
I added ->withTrashed() to my query, getting this Call to undefined method Illuminate\Database\Eloquent\Builder::withTrashed()
Level 73
@laracoft you said you are using eloquent. If that's right then it should be Model::withTrashed()->... if you use the query builder then you should just add ->orWhereNotNull('deleted_at') so in your SQL script in order to get the same results you should add the same check where deleted_at is null or is not null.
Level 27
Ok my bad, it's query builder. Does Laravel automatically filter out records with deleted_at for query builder as well?
Level 73
@laracoft no it does not. You can run ->toSql() on that and inspect why is it different then your custom SQL query. You are not sharing any code, so I don't know how to help.
Level 27
I would like to, but the raw query constructed by the query builder is 60+ lines long and involves multiple tables. It will almost certainly raise more questions than answers.
What puzzles me is why the raw query yields my desired output, but gives the wrong output when executed via code.
Let me try to dig a bit further and simplify the query if I can.
Level 27
Thanks for your help, I just want to further add that:
- It was eloquent, however, once I started calling
selectandjoinetc, it became query builder, and thuswithTrashed()did not work - The query was really complex and there were multiple problems
- Nonetheless,
withTrashedpointed me in the right direction eventually solving the issue
Please or to participate in this conversation.