Multiple ->leftJoin() cause PHP error

Capamania

I have multiple ->leftJoin( ) in my Editor instance which by itself work fine. Now I combined them and get the following error:

DataTables warning: table id=table - SQLSTATE[42000]: Syntax error or access violation: 1104 The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET MAX_JOIN_SIZE=# if the SELECT is okay

I'm on a hosted shared server ... is there any way to SET SQL_BIG_SELECTS=1 in the Editor instance itself? Or is there any other solution/workaround for multiple ->leftJoin( ) ?

Many thanks

allan

That sounds like a limitation in your host I'm afraid. You would need to contact your hosting provider to have them modify the MySQL configuration. That is not something that can be modified at runtime.

Allan

Capamania

Yes I think so too. But since I'm on a shared server they might not change it ... I'm checking. Nevertheless, is it not possible to send this configuration to the server prior to the Editor select? So e.g. if I include the file change.php .... the query itself seems to be successful, but I still get the same error message ...

Setup Change successful {"error":"SQLSTATE[42000]: Syntax error or access violation: 1104 The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET MAX_JOIN_SIZE=# if the SELECT is okay","data":[]}

```
<?php

include( "../../php/change.php" );

include( "../../php/DataTables.php" );

use
DataTables\Editor,
DataTables\Editor\Field,
DataTables\Editor\Format,
DataTables\Editor\Join,
DataTables\Editor\Upload,
DataTables\Editor\Validate;

Editor::inst( $db, 'table' )
->fields(
...
)

->leftJoin( ... )        
->leftJoin( ... )
->leftJoin( ... )
->leftJoin( ... )
->leftJoin( ... )

->process( $_POST )
->json();

And in file change.php

<?php

$mysqli = new mysqli("host", "user", "password", "datatabase");

$setupChange = $mysqli->query("SET SESSION SQL_BIG_SELECTS=1");

// or
// $setupChange = $mysqli->query("SET SQL_BIG_SELECTS=1");

if ($setupChange === TRUE) {
echo "Setup Change successful";
} else {
echo "Setup Change unsuccessful: " . mysqli_error($mysqli);
}

$mysqli->close();

<?php > ``` ?>

allan

If you have permission to set the session variables like that, try:

$db->sql( "SET SESSION SQL_BIG_SELECTS=1" );

before you initialise the Editor class.

Allan

Capamania

Perfect ... this works!! Many thanks!