search

SpartnerNL / Laravel-Excel

🚀 Supercharged Excel exports and imports in Laravel
https://laravel-excel.com
MIT License
12.12k stars 1.89k forks source link

The query() method is called twice and the second call breaks the query builder sql #4140

Open camohub opened 1 month ago

camohub commented 1 month ago

Is the bug applicable and reproducable to the latest version of the package and hasn't it been reported before?

What version of Laravel Excel are you using?

3.1.55

What version of Laravel are you using?

9

What version of PHP are you using?

8.2

Describe your issue

I have a job which is in queue. This job generate xls exports from query. The query method is below. The application uses also Spatie Querybuilder library which works great. As I found out the query() method is called twice which is weird (job runs only once). The first call generate valid SQL but the second call totaly ruin the sql from the builder. The query() method 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');
    }

Thi is the log from 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"

The second sql is invalid cause it refers to table which is not defined in sql.

Can somebody tell me what is wrong with this code?

This is full error 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}

How can the issue be reproduced?

As I described above

What should be the expected behaviour?

As I descripbe above.

patrickbrouwers commented 1 month ago

Probably called twice because the first time it needs to count the rows.

camohub commented 1 month ago

Ok although I am not sure about it. Its cursor not collection what query returns. But why the first sql is valid but second is broken? There is huge difference between them.

patrickbrouwers commented 1 month ago

No clue, it sounds like something isn't right with the query that package produces. We just call the query do a ->count() first and a ->chunk() next, so this package isn't doing anything weird. As mentioned in https://github.com/SpartnerNL/Laravel-Excel/issues/4133#issuecomment-2142287963, you could try to do a ->clone() and see if that works for you

camohub commented 1 month ago

The clone() does not work in this case.

DmiBai commented 1 month ago

Having the same problem, but my query is little bit different I'm also passing Spatie/QueryBuilder, but there is union inside of it.

query() method runs twice, and on the second time it adds order by table.id asc limit 1000 offset 0) There is no opening for this part like SELECT (*) FROM. Removing unopened part that i mentioned first makes query work, but I can not do it via LaravelExcel methods

fixed by $finalQuery = DB::query()->fromSub($this->query->getEloquentBuilder(), 'temp') ->orderBy('id') ->select('*');

camohub commented 2 weeks ago

This is not fix. I would like to use Spatie QueryBuilder with its benefits around filtering.