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

[camohub]

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

• Yes, it's still reproducable

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]

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

[camohub]

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]

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 #4133 (comment), you could try to do a ->clone() and see if that works for you

[camohub]

The clone() does not work in this case.

[DmiBai]

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]

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