Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

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

Closed
1 task done
camohub opened this issue May 30, 2024 · 8 comments
Closed
1 task done

Comments

@camohub
Copy link

camohub commented May 30, 2024

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.

@camohub camohub added the bug label May 30, 2024
@patrickbrouwers
Copy link
Member

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

@camohub
Copy link
Author

camohub commented May 30, 2024

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.

@camohub camohub changed the title The query() method is called twice and the second call ruins my query builder sql The query() method is called twice and the second call brokes my query builder sql May 30, 2024
@camohub camohub changed the title The query() method is called twice and the second call brokes my query builder sql The query() method is called twice and the second call brokes query builder sql May 30, 2024
@camohub camohub changed the title The query() method is called twice and the second call brokes query builder sql The query() method is called twice and the second call breaks the query builder sql May 30, 2024
@patrickbrouwers
Copy link
Member

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
Copy link
Author

camohub commented Jun 3, 2024

The clone() does not work in this case.

@DmiBai
Copy link

DmiBai commented Jun 5, 2024

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
Copy link
Author

camohub commented Jun 16, 2024

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

@stale stale bot added the stale label Aug 16, 2024
Copy link

stale bot commented Aug 18, 2024

This bug report has been automatically closed because it has not had recent activity. If this is still an active bug, please comment to reopen. Thank you for your contributions.

@Synchro
Copy link

Synchro commented Oct 16, 2024

This is definitely a bug, but I managed to work around it by adding a check to see whether query() had been called before, thus avoiding the duplicate joins. More info in here, but the short version is to add this at the top of your query method:

static $done = false;
if ($done) {
    return $this->query;
}
$done = true;
//Now carry on making your modifications to `$query`

Synchro added a commit to Synchro/Laravel-Excel that referenced this issue Oct 16, 2024
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

4 participants