I am not sure if the problem is in this library but I really dont know. But when I try to use native Eloquent query builder it works.
The use case is this. I have a Laravel job which store the request filter and then try to generate excel export file at the background. The library for the excel export is Laravel Excel
The excel export class simply build the request object with filters and then try to run QueryBuilder.
Laravel excel use query() method to handle huge data sets.
The problem is that Laravel Excel call the query() method twice where the first call generate right SQL but the second call ruins the SQL renerated by query builder to invalid sql. Look at the results below.
The export class looks like
public function query()
{
Log::info('BEGIN');
// The $this->filter is same in both calls
$request = new QueryBuilderRequest(['filter' => $this->filter]); // Add filters to the QueryBuilder as new Request instance
Log::info(QueryBuilder::for(Customer::class, $request)
->with(['tags'])
->allowedFilters(resolve(ModelFilter::class)->customers)
->orderByDesc('customers.id')
->toSql());
Log::info('END');
return QueryBuilder::for(Customer::class, $request)
->with(['tags'])
->allowedFilters(resolve(ModelFilter::class)->customers)
->orderByDesc('customers.id');
}
And this is the log of the run:
[2024-05-30 16:52:16] local.INFO: BEGIN
[2024-05-30 16:52:16] local.INFO: select * from "customers" where "customers"."id" = ? and exists (select * from "tags" inner join "customer_tags" on "tags"."id" = "customer_tags"."tag_id" where "customers"."id" = "customer_tags"."customer_id" and "tags"."id" = ?) order by "customers"."id" desc
[2024-05-30 16:52:16] local.INFO: END
[2024-05-30 16:52:16] local.INFO: BEGIN
[2024-05-30 16:52:16] local.INFO: select * from "customers" where "customers"."id" = ? and "tags"."id" = ? order by "customers"."id" desc
[2024-05-30 16:52:16] local.INFO: END
[2024-05-30 17:06:24] local.ERROR: SQLSTATE[42P01]: Undefined table: 7 ERROR: missing FROM-clause entry for table "tags"
This is the stack trace
[2024-05-30 17:06:24] local.ERROR: SQLSTATE[42P01]: Undefined table: 7 ERROR: missing FROM-clause entry for table "tags"
LINE 1: ... from "customers" where "customers"."id" = $1 and "tags"."id...
^ (SQL: select * from "customers" where "customers"."id" = 277 and "tags"."id" = 15 order by "customers"."id" desc limit 1000 offset 0) {"prevMessage":"SQLSTATE[42P01]: Undefined table: 7 ERROR: missing FROM-clause entry for table \"tags\"
LINE 1: ... from \"customers\" where \"customers\".\"id\" = $1 and \"tags\".\"id...
^","trace":[{"file":"/application/vendor/laravel/framework/src/Illuminate/Database/Connection.php","line":720,"function":"runQueryCallback","class":"Illuminate\\Database\\Connection","type":"->"},{"file":"/application/vendor/laravel/framework/src/Illuminate/Database/Connection.php","line":405,"function":"run","class":"Illuminate\\Database\\Connection","type":"->"},{"file":"/application/vendor/laravel/framework/src/Illuminate/Database/Query/Builder.php","line":2705,"function":"select","class":"Illuminate\\Database\\Connection","type":"->"},{"file":"/application/vendor/laravel/framework/src/Illuminate/Database/Query/Builder.php","line":2694,"function":"runSelect","class":"Illuminate\\Database\\Query\\Builder","type":"->"},{"file":"/application/vendor/laravel/framework/src/Illuminate/Database/Query/Builder.php","line":3230,"function":"Illuminate\\Database\\Query\\{closure}","class":"Illuminate\\Database\\Query\\Builder","type":"->"},{"file":"/application/vendor/laravel/framework/src/Illuminate/Database/Query/Builder.php","line":2693,"function":"onceWithColumns","class":"Illuminate\\Database\\Query\\Builder","type":"->"},{"file":"/application/vendor/laravel/framework/src/Illuminate/Database/Eloquent/Builder.php","line":710,"function":"get","class":"Illuminate\\Database\\Query\\Builder","type":"->"},{"file":"/application/vendor/laravel/framework/src/Illuminate/Database/Eloquent/Builder.php","line":694,"function":"getModels","class":"Illuminate\\Database\\Eloquent\\Builder","type":"->"},{"file":"/application/vendor/laravel/framework/src/Illuminate/Database/Concerns/BuildsQueries.php","line":42,"function":"get","class":"Illuminate\\Database\\Eloquent\\Builder","type":"->"},{"file":"/application/vendor/laravel/framework/src/Illuminate/Support/Traits/ForwardsCalls.php","line":23,"function":"chunk","class":"Illuminate\\Database\\Eloquent\\Builder","type":"->"},{"file":"/application/vendor/spatie/laravel-query-builder/src/QueryBuilder.php","line":105,"function":"forwardCallTo","class":"Spatie\\QueryBuilder\\QueryBuilder","type":"->"},{"file":"/application/vendor/maatwebsite/excel/src/Sheet.php","line":474,"function":"__call","class":"Spatie\\QueryBuilder\\QueryBuilder","type":"->"},{"file":"/application/vendor/maatwebsite/excel/src/Sheet.php","line":212,"function":"fromQuery","class":"Maatwebsite\\Excel\\Sheet","type":"->"},{"file":"/application/vendor/maatwebsite/excel/src/Writer.php","line":72,"function":"export","class":"Maatwebsite\\Excel\\Sheet","type":"->"},{"file":"/application/vendor/maatwebsite/excel/src/Excel.php","line":140,"function":"export","class":"Maatwebsite\\Excel\\Writer","type":"->"},{"file":"/application/vendor/laravel/framework/src/Illuminate/Support/Facades/Facade.php","line":338,"function":"raw","class":"Maatwebsite\\Excel\\Excel","type":"->"},{"file":"/application/app/Jobs/Exports/ExportCustomersJob.php","line":56,"function":"__callStatic","class":"Illuminate\\Support\\Facades\\Facade","type":"::"},{"file":"/application/vendor/laravel/framework/src/Illuminate/Container/BoundMethod.php","line":36,"function":"handle","class":"App\\Jobs\\Exports\\ExportCustomersJob","type":"->"},{"file":"/application/vendor/laravel/framework/src/Illuminate/Container/Util.php","line":41,"function":"Illuminate\\Container\\{closure}","class":"Illuminate\\Container\\BoundMethod","type":"::"},{"file":"/application/vendor/laravel/framework/src/Illuminate/Container/BoundMethod.php","line":93,"function":"unwrapIfClosure","class":"Illuminate\\Container\\Util","type":"::"},{"file":"/application/vendor/laravel/framework/src/Illuminate/Container/BoundMethod.php","line":35,"function":"callBoundMethod","class":"Illuminate\\Container\\BoundMethod","type":"::"},{"file":"/application/vendor/laravel/framework/src/Illuminate/Container/Container.php","line":661,"function":"call","class":"Illuminate\\Container\\BoundMethod","type":"::"},{"file":"/application/vendor/laravel/framework/src/Illuminate/Bus/Dispatcher.php","line":128,"function":"call","class":"Illuminate\\Container\\Container","type":"->"},{"file":"/application/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php","line":141,"function":"Illuminate\\Bus\\{closure}","class":"Illuminate\\Bus\\Dispatcher","type":"->"},{"file":"/application/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php","line":116,"function":"Illuminate\\Pipeline\\{closure}","class":"Illuminate\\Pipeline\\Pipeline","type":"->"},{"file":"/application/vendor/laravel/framework/src/Illuminate/Bus/Dispatcher.php","line":132,"function":"then","class":"Illuminate\\Pipeline\\Pipeline","type":"->"},{"file":"/application/vendor/laravel/framework/src/Illuminate/Queue/CallQueuedHandler.php","line":123,"function":"dispatchNow","class":"Illuminate\\Bus\\Dispatcher","type":"->"},{"file":"/application/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php","line":141,"function":"Illuminate\\Queue\\{closure}","class":"Illuminate\\Queue\\CallQueuedHandler","type":"->"},{"file":"/application/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php","line":116,"function":"Illuminate\\Pipeline\\{closure}","class":"Illuminate\\Pipeline\\Pipeline","type":"->"},{"file":"/application/vendor/laravel/framework/src/Illuminate/Queue/CallQueuedHandler.php","line":122,"function":"then","class":"Illuminate\\Pipeline\\Pipeline","type":"->"},{"file":"/application/vendor/laravel/framework/src/Illuminate/Queue/CallQueuedHandler.php","line":70,"function":"dispatchThroughMiddleware","class":"Illuminate\\Queue\\CallQueuedHandler","type":"->"},{"file":"/application/vendor/laravel/framework/src/Illuminate/Queue/Jobs/Job.php","line":98,"function":"call","class":"Illuminate\\Queue\\CallQueuedHandler","type":"->"},{"file":"/application/vendor/laravel/framework/src/Illuminate/Queue/Worker.php","line":425,"function":"fire","class":"Illuminate\\Queue\\Jobs\\Job","type":"->"},{"file":"/application/vendor/laravel/framework/src/Illuminate/Queue/Worker.php","line":375,"function":"process","class":"Illuminate\\Queue\\Worker","type":"->"},{"file":"/application/vendor/laravel/framework/src/Illuminate/Queue/Worker.php","line":173,"function":"runJob","class":"Illuminate\\Queue\\Worker","type":"->"},{"file":"/application/vendor/laravel/framework/src/Illuminate/Queue/Console/WorkCommand.php","line":147,"function":"daemon","class":"Illuminate\\Queue\\Worker","type":"->"},{"file":"/application/vendor/laravel/framework/src/Illuminate/Queue/Console/WorkCommand.php","line":130,"function":"runWorker","class":"Illuminate\\Queue\\Console\\WorkCommand","type":"->"},{"file":"/application/vendor/laravel/framework/src/Illuminate/Container/BoundMethod.php","line":36,"function":"handle","class":"Illuminate\\Queue\\Console\\WorkCommand","type":"->"},{"file":"/application/vendor/laravel/framework/src/Illuminate/Container/Util.php","line":41,"function":"Illuminate\\Container\\{closure}","class":"Illuminate\\Container\\BoundMethod","type":"::"},{"file":"/application/vendor/laravel/framework/src/Illuminate/Container/BoundMethod.php","line":93,"function":"unwrapIfClosure","class":"Illuminate\\Container\\Util","type":"::"},{"file":"/application/vendor/laravel/framework/src/Illuminate/Container/BoundMethod.php","line":35,"function":"callBoundMethod","class":"Illuminate\\Container\\BoundMethod","type":"::"},{"file":"/application/vendor/laravel/framework/src/Illuminate/Container/Container.php","line":661,"function":"call","class":"Illuminate\\Container\\BoundMethod","type":"::"},{"file":"/application/vendor/laravel/framework/src/Illuminate/Console/Command.php","line":183,"function":"call","class":"Illuminate\\Container\\Container","type":"->"},{"file":"/application/vendor/symfony/console/Command/Command.php","line":326,"function":"execute","class":"Illuminate\\Console\\Command","type":"->"},{"file":"/application/vendor/laravel/framework/src/Illuminate/Console/Command.php","line":152,"function":"run","class":"Symfony\\Component\\Console\\Command\\Command","type":"->"},{"file":"/application/vendor/symfony/console/Application.php","line":1063,"function":"run","class":"Illuminate\\Console\\Command","type":"->"},{"file":"/application/vendor/symfony/console/Application.php","line":320,"function":"doRunCommand","class":"Symfony\\Component\\Console\\Application","type":"->"},{"file":"/application/vendor/symfony/console/Application.php","line":174,"function":"doRun","class":"Symfony\\Component\\Console\\Application","type":"->"},{"file":"/application/vendor/laravel/framework/src/Illuminate/Console/Application.php","line":102,"function":"run","class":"Symfony\\Component\\Console\\Application","type":"->"},{"file":"/application/vendor/laravel/framework/src/Illuminate/Foundation/Console/Kernel.php","line":155,"function":"run","class":"Illuminate\\Console\\Application","type":"->"},{"file":"/application/artisan","line":35,"function":"handle","class":"Illuminate\\Foundation\\Console\\Kernel","type":"->"}]}
[2024-05-30 17:06:24] local.ERROR: SQLSTATE[42P01]: Undefined table: 7 ERROR: missing FROM-clause entry for table "tags"
LINE 1: ... from "customers" where "customers"."id" = $1 and "tags"."id...
^ (SQL: select * from "customers" where "customers"."id" = 277 and "tags"."id" = 15 order by "customers"."id" desc limit 1000 offset 0) {"exception":"[object] (Illuminate\\Database\\QueryException(code: 42P01): SQLSTATE[42P01]: Undefined table: 7 ERROR: missing FROM-clause entry for table \"tags\"
LINE 1: ... from \"customers\" where \"customers\".\"id\" = $1 and \"tags\".\"id...
^ (SQL: select * from \"customers\" where \"customers\".\"id\" = 277 and \"tags\".\"id\" = 15 order by \"customers\".\"id\" desc limit 1000 offset 0) at /application/vendor/laravel/framework/src/Illuminate/Database/Connection.php:760)
[stacktrace]
#0 /application/vendor/laravel/framework/src/Illuminate/Database/Connection.php(720): Illuminate\\Database\\Connection->runQueryCallback()
#1 /application/vendor/laravel/framework/src/Illuminate/Database/Connection.php(405): Illuminate\\Database\\Connection->run()
#2 /application/vendor/laravel/framework/src/Illuminate/Database/Query/Builder.php(2705): Illuminate\\Database\\Connection->select()
#3 /application/vendor/laravel/framework/src/Illuminate/Database/Query/Builder.php(2694): Illuminate\\Database\\Query\\Builder->runSelect()
#4 /application/vendor/laravel/framework/src/Illuminate/Database/Query/Builder.php(3230): Illuminate\\Database\\Query\\Builder->Illuminate\\Database\\Query\\{closure}()
#5 /application/vendor/laravel/framework/src/Illuminate/Database/Query/Builder.php(2693): Illuminate\\Database\\Query\\Builder->onceWithColumns()
#6 /application/vendor/laravel/framework/src/Illuminate/Database/Eloquent/Builder.php(710): Illuminate\\Database\\Query\\Builder->get()
#7 /application/vendor/laravel/framework/src/Illuminate/Database/Eloquent/Builder.php(694): Illuminate\\Database\\Eloquent\\Builder->getModels()
#8 /application/vendor/laravel/framework/src/Illuminate/Database/Concerns/BuildsQueries.php(42): Illuminate\\Database\\Eloquent\\Builder->get()
#9 /application/vendor/laravel/framework/src/Illuminate/Support/Traits/ForwardsCalls.php(23): Illuminate\\Database\\Eloquent\\Builder->chunk()
#10 /application/vendor/spatie/laravel-query-builder/src/QueryBuilder.php(105): Spatie\\QueryBuilder\\QueryBuilder->forwardCallTo()
#11 /application/vendor/maatwebsite/excel/src/Sheet.php(474): Spatie\\QueryBuilder\\QueryBuilder->__call()
#12 /application/vendor/maatwebsite/excel/src/Sheet.php(212): Maatwebsite\\Excel\\Sheet->fromQuery()
#13 /application/vendor/maatwebsite/excel/src/Writer.php(72): Maatwebsite\\Excel\\Sheet->export()
#14 /application/vendor/maatwebsite/excel/src/Excel.php(140): Maatwebsite\\Excel\\Writer->export()
#15 /application/vendor/laravel/framework/src/Illuminate/Support/Facades/Facade.php(338): Maatwebsite\\Excel\\Excel->raw()
#16 /application/app/Jobs/Exports/ExportCustomersJob.php(56): Illuminate\\Support\\Facades\\Facade::__callStatic()
#17 /application/vendor/laravel/framework/src/Illuminate/Container/BoundMethod.php(36): App\\Jobs\\Exports\\ExportCustomersJob->handle()
#18 /application/vendor/laravel/framework/src/Illuminate/Container/Util.php(41): Illuminate\\Container\\BoundMethod::Illuminate\\Container\\{closure}()
#19 /application/vendor/laravel/framework/src/Illuminate/Container/BoundMethod.php(93): Illuminate\\Container\\Util::unwrapIfClosure()
#20 /application/vendor/laravel/framework/src/Illuminate/Container/BoundMethod.php(35): Illuminate\\Container\\BoundMethod::callBoundMethod()
#21 /application/vendor/laravel/framework/src/Illuminate/Container/Container.php(661): Illuminate\\Container\\BoundMethod::call()
#22 /application/vendor/laravel/framework/src/Illuminate/Bus/Dispatcher.php(128): Illuminate\\Container\\Container->call()
#23 /application/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(141): Illuminate\\Bus\\Dispatcher->Illuminate\\Bus\\{closure}()
#24 /application/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(116): Illuminate\\Pipeline\\Pipeline->Illuminate\\Pipeline\\{closure}()
#25 /application/vendor/laravel/framework/src/Illuminate/Bus/Dispatcher.php(132): Illuminate\\Pipeline\\Pipeline->then()
#26 /application/vendor/laravel/framework/src/Illuminate/Queue/CallQueuedHandler.php(123): Illuminate\\Bus\\Dispatcher->dispatchNow()
#27 /application/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(141): Illuminate\\Queue\\CallQueuedHandler->Illuminate\\Queue\\{closure}()
#28 /application/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(116): Illuminate\\Pipeline\\Pipeline->Illuminate\\Pipeline\\{closure}()
#29 /application/vendor/laravel/framework/src/Illuminate/Queue/CallQueuedHandler.php(122): Illuminate\\Pipeline\\Pipeline->then()
#30 /application/vendor/laravel/framework/src/Illuminate/Queue/CallQueuedHandler.php(70): Illuminate\\Queue\\CallQueuedHandler->dispatchThroughMiddleware()
#31 /application/vendor/laravel/framework/src/Illuminate/Queue/Jobs/Job.php(98): Illuminate\\Queue\\CallQueuedHandler->call()
#32 /application/vendor/laravel/framework/src/Illuminate/Queue/Worker.php(425): Illuminate\\Queue\\Jobs\\Job->fire()
#33 /application/vendor/laravel/framework/src/Illuminate/Queue/Worker.php(375): Illuminate\\Queue\\Worker->process()
#34 /application/vendor/laravel/framework/src/Illuminate/Queue/Worker.php(173): Illuminate\\Queue\\Worker->runJob()
#35 /application/vendor/laravel/framework/src/Illuminate/Queue/Console/WorkCommand.php(147): Illuminate\\Queue\\Worker->daemon()
#36 /application/vendor/laravel/framework/src/Illuminate/Queue/Console/WorkCommand.php(130): Illuminate\\Queue\\Console\\WorkCommand->runWorker()
#37 /application/vendor/laravel/framework/src/Illuminate/Container/BoundMethod.php(36): Illuminate\\Queue\\Console\\WorkCommand->handle()
#38 /application/vendor/laravel/framework/src/Illuminate/Container/Util.php(41): Illuminate\\Container\\BoundMethod::Illuminate\\Container\\{closure}()
#39 /application/vendor/laravel/framework/src/Illuminate/Container/BoundMethod.php(93): Illuminate\\Container\\Util::unwrapIfClosure()
#40 /application/vendor/laravel/framework/src/Illuminate/Container/BoundMethod.php(35): Illuminate\\Container\\BoundMethod::callBoundMethod()
#41 /application/vendor/laravel/framework/src/Illuminate/Container/Container.php(661): Illuminate\\Container\\BoundMethod::call()
#42 /application/vendor/laravel/framework/src/Illuminate/Console/Command.php(183): Illuminate\\Container\\Container->call()
#43 /application/vendor/symfony/console/Command/Command.php(326): Illuminate\\Console\\Command->execute()
#44 /application/vendor/laravel/framework/src/Illuminate/Console/Command.php(152): Symfony\\Component\\Console\\Command\\Command->run()
#45 /application/vendor/symfony/console/Application.php(1063): Illuminate\\Console\\Command->run()
#46 /application/vendor/symfony/console/Application.php(320): Symfony\\Component\\Console\\Application->doRunCommand()
#47 /application/vendor/symfony/console/Application.php(174): Symfony\\Component\\Console\\Application->doRun()
#48 /application/vendor/laravel/framework/src/Illuminate/Console/Application.php(102): Symfony\\Component\\Console\\Application->run()
#49 /application/vendor/laravel/framework/src/Illuminate/Foundation/Console/Kernel.php(155): Illuminate\\Console\\Application->run()
#50 /application/artisan(35): Illuminate\\Foundation\\Console\\Kernel->handle()
#51 {main}
Can somebody look at it and tell me if the problem is in the Query builder or in Laravel excel library?
Thanks a lot.
I am not sure if the problem is in this library but I really dont know. But when I try to use native Eloquent query builder it works. The use case is this. I have a Laravel job which store the request filter and then try to generate excel export file at the background. The library for the excel export is Laravel Excel The excel export class simply build the request object with filters and then try to run QueryBuilder. Laravel excel use query() method to handle huge data sets. The problem is that Laravel Excel call the query() method twice where the first call generate right SQL but the second call ruins the SQL renerated by query builder to invalid sql. Look at the results below. The export class looks like
And this is the log of the run:
This is the stack trace
Can somebody look at it and tell me if the problem is in the Query builder or in Laravel excel library? Thanks a lot.