multiple matching or innerJoin calls drops dot notated association's conditions

[rrd108]

This is a (multiple allowed):

• [x] bug

• [ ] enhancement

• [ ] feature-discussion (RFC)

• CakePHP Version: 3.3.7

• Platform and Target: Apache/2.4.18 (Ubuntu)

What you did

Authors hasMany SpecialTags Authors belongsToMany Tags SpecialTags belongsTo Tags

    $authorsTable = TableRegistry::get('Authors');
    $query = $authorsTable->find();
    $result = $query->matching('Tags', function ($q) {
            return $q->where(['Tags.id IN ' => [2]]);
        });

    //calling a second matching or innerJoin
    $result = $query->matching('SpecialTags.Tags', function ($q) {
            return $q->where(['Tags.id IN ' => [1, 3]]);
        });

Changing the order of matching calls won't help, same result, the conditions in the dot notated call is dropped.

What happened

The where condition of the second matching call is dropped. It is missing from the generated SQL. Even if you put a non-existent field name in where - there is no error.

What you expected to happen

The second matching's where conditions should be added to the generated SQL query.

[markstory]

What SQL query are you expecting to be generated? You have 2 joins to the tags table, the ORM is obviously preferring the first/shortest path to the association which is reasonable to me.

[rrd108]

I am expecting to have an inner join inside the inner join of specialtags. Or, (I guess would give the same results) if both where conditions whould go together to tag's inner join.

Anyway dropping a condition without any warning is hard to debug.

[lorenzo]

This is the expected behavior, although i understand it is surprising. Currently there is no detection of conflicting calls, and I agree that could be a good improvement

[rrd108]

@lorenzo if you think it is possible and give me some direction I would try to fix this.

[lorenzo]

Let try and see if I can come up with a plan for this

[rrd108]

@lorenzo any update on this? If you are waiting for me I would need some direction about where to dig into the code.

[lorenzo]

@rrd108 sorry, I was out during the week for a conference. If you feel brave, the place to look at is EagerLoader, probably around the normalize method, which is the one calculating the path to follow for loading the associations

[rrd108]

@lorenzo Today I spent a few hours on this. For me it seems that noramilze keep what we need here. _resolveJoins still have them, but I was not able to figure out what happens in that functions as it is a recursive and I guess my debugger run out of memory. So I am not closer at all, and do not really have an idea how to move forward. :-(

I think valueBinder is responsible for this behavior.

[lorenzo]

I'll try to work on this tomorrow

[lorenzo]

I'm not sure there will be a fix though, maybe the best we can do is throw an exception or a warning

[kyleweishaupt]

I believe I'm having a similar issue as well with the ORM dropping associations. All my code is on the StackOverflow question. Let me know if a separate issue should be opened.

http://stackoverflow.com/questions/40978554/cakephp-3-matching-and-contain-not-returning-association

[dereuromark]

@lorenzo What do you think we should do based on the open PR?

[lorenzo]

@dereuromark what open PR?

[dereuromark]

@lorenzo https://github.com/cakephp/cakephp/pull/9712

[lorenzo]

Yeah, as insad before, this is the expected behavior, although it is entirely surprising. You cannot easily have 2 inner joins to the same table with the same alias, there is pretty much no workaround.

The only thing we could eventually do is throw an error

[rrd108]

It seems I found a workaround by putting the second matching into the first one. However did not test it yet...

$result = $query->matching('Tags', function ($q) {
    return $q->where(['Tags.id IN ' => [2]])
        ->matching('SpecialTags', function ($q) {
            return $q->where(['Tags.id IN ' => [1, 3]]);
        });
    });