MySQL error -- LIMIT in SUBQUERY

#1

I’m trying to throw up a CRUD on a table and getting an error:

SQLSTATE[42000]: Syntax error or access violation: 1235 This version of MySQL doesn’t yet support 'LIMIT & IN/ALL/ANY/SOME subquery
(followed by a very long query)

All I’m doing is
$contributions = $casestudy->ref(‘contribs’);
$app->add([‘CRUD’])->setModel($contributions);

Has anyone seen this before? If so, how do you eliminate it? I’d give more information, but I’m still struggling to distill it down and figure out the minimal code necessary to cause it (I suspect if I could do so, I’d be able to figure out what’s causing it).

The odd thing is that if I go into the table behind the ref(‘contribs’) from a different path (through a different model), then I can throw up the CRUD with no problem at all. So there’s definitely something that’s causing my query to end up with LIMITS in a subquery – I just have no idea what.

#2

I worked a bit more to find as small a set of code to replicate this error, and I was successful. First, to explain my application. I’m doing a simulation application, and there’s a top level of the simulation where you can define various parameters or records. However, there are also separate sub-groups that can also have their own parameters or records. If you’re simulating a sub-group, you factor in the parameters and records from the top level and your sub-group. To set this up, I associate the parameters and records with both a sub-group and a top-level. If the parameter or record is for the top-level, not any particular sub-group, then that parameter or record will have a subgroup_id of null.

So here are the models:

in TopLevel.php:

namespace Model;

class TopLevel extends \atk4\data\Model
{
public $table = ‘TopLevel’;

function init()
{
    parent::init();
    $this->addField('name');
    $this->hasMany('subgroups', new SubGroup());
    $this->hasMany('records', new Record());
}

}

in SubGroup.php:

namespace Model;

class SubGroup extends \atk4\data\Model
{
public $table = ‘SubGroup’;

function init()
{
    parent::init();
    $this->addField('name');
    $this->hasOne('TopLevel_id', new TopLevel())->addTitle();
    $this->hasMany('records', new Record());
    $this->hasMany('WorkingItems', new WorkingItem());
}

}

in Record.php:

namespace Model;

class Record extends \atk4\data\Model
{
public $table = ‘Record’;

function init()
{
    parent::init();
    $this->addField('name');
    $this->hasOne('TopLevel_id', new TopLevel())->addTitle();
    $this->hasOne('SubGroup_id', new SubGroup())->addTitle();
}

}

and in WorkingItem.php:

class WorkingItem extends \atk4\data\Model
{
public $table = ‘WorkingItem’;

function init()
{
    parent::init();
    $this->addField('name');
    $this->hasOne('TopLevel_id', new TopLevel())->addTitle();
    $this->hasOne('SubGroup_id', new SubGroup())->addTitle();
    $this->hasOne('Record_id', function($m)
    {
        $records = $m->ref('TopLevel_id')->ref('records');
        $records->addCondition([
            ['SubGroup_id', null],
            ['SubGroup_id', $m['SubGroup_id']]
        ]);
        return ($records);
    })->addTitle();
}

}

(hope you’re still with me). The final line of this example.php will trigger the limit error:

require ‘vendor/autoload.php’;

$app = new App();

$topLevel = new Model\TopLevel($app->db);
$topLevel->tryLoad(1);
$subgroup = $topLevel->ref(‘subgroups’);
$record = new Model\Record($app->db);
$workingItem = $subgroup->ref(‘WorkingItems’);

$crud = $app->add(‘CRUD’)->setModel($topLevel);
$crud = $app->add(‘CRUD’)->setModel($subgroup);
$crud = $app->add(‘CRUD’)->setModel($record);
$crud = $app->add(‘CRUD’)->setModel($workingItem);

I know it’s a bit much, but that’s the smallest I’ve been able to get it to replicate the issue.

So, what am I doing wrong and, more importantly, how do I do this right so that I don’t cause Agile Data to set up a LIMIT in the sub-query and trigger this issue in the version of MySQL I’m using?

Thanks for staying with me – I know it’s been a bit of a slog to get through this one.

#3

Still trying to add a bit of information here, and I think the following is useful. If I change the “$workingItem = $subgroup->ref(‘WorkingItems’);” to “$workingItem = $topLevel->ref(‘WorkingItems’);” then I no longer get the error message. However, something’s still wrong with things:

  1. If I use the final CRUD to take a create a WorkingItem I can assign it to a subgroup. However, even once I’ve assigned it to a sub-group I can’t see the sub-group’s Records – I can only assign the record to a record that has no group associated with it. Once I save the WorkingItem, I can edit it, and then I can see the records that are associated with the top-level (no subgroup) and the subgroup, which is what should happen.
  2. Once I edit the WorkingItem and save it with a record from the subgroup that WorkingItem is in, the CRUD shows a blank in the “Record” column. If I edit the WorkingItem record again, I can see that it has retained the value of the record that I chose – this seems to be an issue with the CRUD.

I can fix those items by adding a “$workingItem->addCondition(‘SubGroup_id’, 1);” after the assignment of workingItem – but them I’m restricted to SubGroup 1.

Hope this is all helpful. I may have found a workaround to my needs (I’ll have to try it), but it still seems like I’m triggering a subtle interplay between hasOne conditions and CRUDs which may show up in other areas, too. Perhaps I’m doing something wrong (if so, please tell me).

Oh, one last item. If I remove the “$topLevel->tryLoad(1);” line (yes, I know that the 1 is hard coded – I had a record of that value and was trying to see what the minimal changes were to trigger the issue), then I’m back to the LIMIT in a subquery SQL issue, no matter what I do with the addCondition.