APIv4: If join is specified, then limit is off by one
Overview
When using the APIv4 Get
action, it appears that the limit
is misinterpreted if you have a join. For example, a limit of 5 would return 4 results. A limit of 10 would return 9 results.
Reproduction steps
Run these two commands:
cv api4 Contact.get +s id,display_name +l 5 +o 'id DESC' -v
cv api4 Contact.get +s id,display_name,phones.phone +l 5 +o 'id DESC' -v
Note the commands are identical save one detail: the second command adds an extra select
option (phones.phone
).
Current behavior
The first command returns 5 contacts (203, 202, 201, 200, 199):
$ cv api4 Contact.get +s id,display_name +l 5 +o 'id DESC' -v
Entity: Contact
Action: get
Params: {
"version": 4,
"checkPermissions": false,
"select": [
"id",
"display_name"
],
"limit": 5,
"orderBy": {
"id": "DESC"
}
}
[
{
"id": 203,
"display_name": "demo@example.com"
},
{
"id": 202,
"display_name": "admin@example.com"
},
{
"id": 201,
"display_name": "Mr. Troy Olsen"
},
{
"id": 200,
"display_name": "juliannw@sample.org"
},
{
"id": 199,
"display_name": "Deloit Literacy Collective"
}
]
The second command returns 4 contacts (203, 202, 201, 200):
$ cv api4 Contact.get +s id,display_name,phones.phone +l 5 +o 'id DESC' -v
Entity: Contact
Action: get
Params: {
"version": 4,
"checkPermissions": false,
"select": [
"id",
"display_name",
"phones.phone"
],
"limit": 5,
"orderBy": {
"id": "DESC"
}
}
[
{
"id": 203,
"display_name": "demo@example.com",
"phones": []
},
{
"id": 202,
"display_name": "admin@example.com",
"phones": []
},
{
"id": 201,
"display_name": "Mr. Troy Olsen",
"phones": [
{
"phone": "(799) 831-4695",
"id": 149
},
{
"phone": "(678) 802-4040",
"id": 150
}
]
},
{
"id": 200,
"display_name": "juliannw@sample.org",
"phones": [
{
"phone": "713-8988",
"id": 109
}
]
}
]
Note that you might plausibly expect the results to be different if the implied join was an INNER JOIN
. However, it appears to behave more like a LEFT JOIN
(eg records may be returned with an empty phones
list), and the same problem is observed even if you choose a different part of the contact list (eg +w id<200
).
Expected behaviour
The two queries would return the same 5 contacts (203, 202, 201, 200, 199).
Environment information
- Browser: N/A
- CiviCRM: Master
- PHP: 71.
- CMS: Drupal 7
- Database: MySQL 5.7
- Web Server: N/A