CakePHP query returning different results then running directly in sql

Question

GOAL

I am attempting to return all features and all associated (if any) features_user_types where the user_type_id = ?.

So for example, I have 2 features. I want both to be returned along with all associated features_user_types as long as the user_type_id = 2. If there is no matching feature_user_type then it should return feature anyway.

EXPECTED RESULTS

Example Output: WHERE user_type_id = 2

"features": [
{
    "id": 1,
    "features_user_types": [
      {
        "id": 79,
        "feature_id": 1,
        "user_type_id": 2,
        "position": 3
      }
    ]
  },
  {
    "id": 2,
    "features_user_types": []
  }
]

ACTUAL RESULTS

However, currently it is returning all associated features_user_types despite their id not equaling 2.

$query->toArray() Output:

"features": [
  {
    "id": 1,
    "features_user_types": [
      {
        "id": 79,
        "feature_id": 1,
        "user_type_id": 2,
        "position": 3
       }
    ]
  },
  {
    "id": 2,
    "features_user_types": [
      {
        "id": 72,
        "feature_id": 2,
        "user_type_id": 1,
        "position": 9
      }
     ]
  }
]

DATA STRUCTURE

Table Structure:

features
-id

features_user_types
-id
-feature_id
-user_type_id
-position

user_types
-id

CakePHP Association Definitions:

FeaturesTable:

$this->belongsToMany('UserTypes', [
        'foreignKey' => 'feature_id',
        'targetForeignKey' => 'user_type_id',
        'joinTable' => 'features_user_types'
]);
$this->hasMany('FeaturesUserTypes', [
        'foreignKey' => 'feature_id'
]);

UserTypesTable:

$this->belongsToMany('Features', [
    'foreignKey' => 'user_type_id',
    'targetForeignKey' => 'feature_id',
    'joinTable' => 'features_user_types'
]);
$this->hasMany('FeaturesUserTypes', [
    'className' => 'FeaturesUserTypes',
    'foreignKey' => 'user_type_id'
]);

FeaturesUserTypesTable:

$this->belongsTo('Features', [
    'foreignKey' => 'feature_id',
    'joinType' => 'INNER'
]);
$this->belongsTo('UserTypes', [
    'foreignKey' => 'user_type_id',
    'joinType' => 'INNER'
]);

QUERY OBJECT

I have a query builder in my cakephp app that is creating the following sql according to the $query->sql():

SELECT DISTINCT 
   Features.id AS `Features__id`, 
   Features.created AS `Features__created`, 
   Features.modified AS `Features__modified`, 
   Features.name AS `Features__name`, 
   Features.description AS `Features__description` 
FROM features Features 
LEFT JOIN features_user_types FeaturesUserTypes 
   ON (FeaturesUserTypes.user_type_id = 2 
      AND Features.id = (FeaturesUserTypes.feature_id))

MySQL

However, if I copy and paste this directly into MySQL I get the results that I expect, all features with only featurs_user_types matching the id are returned.

Actual Query:

SELECT DISTINCT *
FROM features Features 
LEFT JOIN features_user_types FeaturesUserTypes 
  ON (FeaturesUserTypes.user_type_id = 2 
    AND Features.id = (FeaturesUserTypes.feature_id))

MySQL Output:

----------------------------------------------------------------------------
|ID (feature id)|ID (feature_user_type_id)|feature_id|user_type_id|position|
| 1             | 79                      | 1        | 2          | 3      |
| 2             | NULL                    | NULL     | NULL       | NULL   |
----------------------------------------------------------------------------

CODE

AppController:

My AppController is very generic but built to take it paramters from URLs to generate and execute sql queries. It is a rather large file so instead I went through it with a debugger and recorded any lines that $query was altered and filled in the variables to make it more obvious.

$key = 'FeaturesUserTypes.user_type_id';
$value = 2;

$model = $this->loadModel();
$query = $model->find('all', ['fields' => $this->getFields()]);
$query->contain(['FeaturesUserTypes']);
$query->leftJoinWith('FeaturesUserTypes', function($q) use ($key, $value) {
   return $q->where([$key => $value]);
});
$query->distinct();
$results = $query->toArray();

Any idea on what could be happening? I am running CakePHP 3 and PHP 5.6.10. Thanks!

The query will return all records from the features table, not just the matching ones. It is unlikely that this query pasted into MySQL would return the matching records only. If you wanted the matching records only, then change left join to inner join. — Shadow
– Shadow, Commented Apr 25, 2017 at 15:10
The query has a check for id in the join, it absolutely works as expected in MySQL. And the results in no way match CakePHP's results. This type of query works in CakePHP for other models just fine which is why I think it may have something to do with the relationship type. — Jeremy
– Jeremy, Commented Apr 25, 2017 at 15:16
Nope, it returns all results in MySQL as well. See the answer I provided below. — Shadow
– Shadow, Commented Apr 25, 2017 at 15:18
I just ran it and it returned 1 feature, the one with the features_user_types matching the id. I need it to return all 10 features including the one matching features_user_types' user_type_id. — Jeremy
– Jeremy, Commented Apr 25, 2017 at 15:24
Then I do not understand your question at all. Pls reword it to make it more clear. Also, provide the way you perform the query. The same query is not going to return different results, unless the underlying data is different. — Shadow
– Shadow, Commented Apr 25, 2017 at 15:33

ndm · Accepted Answer · 2017-04-28 20:16:34Z

3

+100

Unlike hasOne and belongsTo associations, which are using joins in the main query unless explicitly configured otherwise, hasMany and belongsToMany associated data is always being retrieved in a separate query.

Note that sql() will always only return the main query, not the possible additional queries used for eager loading associations! If you need to know about the additional queries, check DebugKits SQL log panel. So what this means, is that the query that you're testing manually, is not what the ORM will actually use to retrieve the associated data.

What you're looking for is passing conditions to contain, ie remove the leftJoinWith() (unless you need the associated data in the main query too for further SQL level operations), and attach the condition callback to the FeaturesUserTypes containment instead:

$query->contain([
    'FeaturesUserTypes' => function($q) use ($key, $value) {
        return $q->where([$key => $value]);
    }
]);

And for anyone who reads this, make sure that $key does not hold user input, otherwhise this would be an SQL injection vulnerability!

2 Comments

Jeremy Over a year ago

This appears to be the solution (running automation now). However, I do have to leave the leftJoinWith(), otherwise I get an error that 'FeaturesUserTypes.position' on does not exist (used in the order()) when I call toArray(). So ultimately the line changes from $query->contain([$function]); to $query->contain([$join => $function]); where $function = function($q) use ($key, $value) { return $q->where([$key => $value]); });. Thank you mucho!

ndm Over a year ago

@jrquick I see... order() isn't in your example, but I'll add a hint in the answer.

Rick James · Accepted Answer · 2017-04-28 21:03:35Z

0

It is usually wrong to do filtering in the ON clause:

FROM a
JOIN b ON b.x = 2 AND a.z=b.z

Move the filtering to the WHERE:

FROM a
JOIN b ON a.z=b.z
WHERE b.x = 2

Also, don't use LEFT unless you want the nulls for "missing" rows in the 'right' table.

I don't know your data, but it seems like DISTINCT is not appropriate.

answered Apr 28, 2017 at 21:03

Rick James

144k15 gold badges144 silver badges255 bronze badges

Collectives™ on Stack Overflow

CakePHP query returning different results then running directly in sql

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related