API v4 returning incorrect count of contributions under certain WHERE conditions
The database has three contributions with the contribution_recur_id set to 19.
When I query for these via apiv4 using just the contribution_recur_id, I get three results from the database (and the first() function works properly).
When I query and limit to those with is_template set to 1 (there should be no matching results), I get a count of 1, but NULL when I call first().
Here's the data:
MariaDB [ptp]> SELECT * FROM civicrm_contribution WHERE contribution_recur_id =19;
+------+------------+-------------------+----------------------+-----------------------+---------------------+-----------------------+--------------+------------+------------+-----------------------------+----------------------------------+----------+-------------+---------------+---------------------+---------------+---------------------------------------------------------------------------------+--------------+-----------------------+---------+--------------+------------------------+------------+--------------+-------------+------------+---------------+--------------------------+----------------+-------------+
| id | contact_id | financial_type_id | contribution_page_id | payment_instrument_id | receive_date | non_deductible_amount | total_amount | fee_amount | net_amount | trxn_id | invoice_id | currency | cancel_date | cancel_reason | receipt_date | thankyou_date | source | amount_level | contribution_recur_id | is_test | is_pay_later | contribution_status_id | address_id | check_number | campaign_id | tax_amount | creditnote_id | revenue_recognition_date | invoice_number | is_template |
+------+------------+-------------------+----------------------+-----------------------+---------------------+-----------------------+--------------+------------+------------+-----------------------------+----------------------------------+----------+-------------+---------------+---------------------+---------------+---------------------------------------------------------------------------------+--------------+-----------------------+---------+--------------+------------------------+------------+--------------+-------------+------------+---------------+--------------------------+----------------+-------------+
| 3928 | 10459 | 2 | 14 | 1 | 2019-12-02 13:07:59 | 0.00 | 296.00 | 8.88 | 287.12 | in_1FlKFWAwDzuDdbFCuWUlA4EZ | 0f3dd1eda0400becb9ecd33c58d28ae8 | USD | NULL | 0 | 2019-12-02 13:07:56 | NULL | Online Contribution: PowerBase Subscription Payment with Monthly Payment Option | NULL | 19 | 0 | 0 | 1 | 24745 | NULL | NULL | NULL | NULL | NULL | NULL | 0 |
| 3951 | 10459 | 2 | 14 | 1 | 2020-01-02 13:08:03 | 0.00 | 296.00 | 8.88 | 287.12 | in_1FwZ1bAwDzuDdbFCXoBfw8UM | NULL | USD | NULL | NULL | 2020-01-02 14:13:42 | NULL | Online Contribution: PowerBase Subscription Payment with Monthly Payment Option | NULL | 19 | 0 | 0 | 1 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 0 |
| 3999 | 10459 | 2 | 14 | 1 | 2020-02-02 13:08:03 | 0.00 | 296.00 | 8.88 | 287.12 | in_1G7nnbAwDzuDdbFCvncHVNPH | NULL | USD | NULL | NULL | 2020-02-07 15:03:30 | NULL | Online Contribution: PowerBase Subscription Payment with Monthly Payment Option | NULL | 19 | 0 | 0 | 1 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 0 |
+------+------------+-------------------+----------------------+-----------------------+---------------------+-----------------------+--------------+------------+------------+-----------------------------+----------------------------------+----------+-------------+---------------+---------------------+---------------+---------------------------------------------------------------------------------+--------------+-----------------------+---------+--------------+------------------------+------------+--------------+-------------+------------+---------------+--------------------------+----------------+-------------+
3 rows in set (0.001 sec)
MariaDB [ptp]>Here is the test code:
<?php
$res = \Civi\Api4\Contribution::get()
->setCheckPermissions(FALSE)
->addWhere('contribution_recur_id', '=', 19)
->execute();
echo "=== NOTE Results are correct ===\n\n";
echo "count is: " . $res->count() . "\n";
echo "First...\n";
print_r($res->first());
foreach ($res as $r) {
print_r($r);
}
$contribution_recur_id = 19;
$res = \Civi\Api4\Contribution::get()
->setCheckPermissions(FALSE)
->addWhere('contribution_recur_id', '=', 19)
->addWhere('is_template', '=', 1)
->execute();
echo "\n\n";
echo "=== NOTE: count is one, but no results from first() === \n";
echo "count is: " . $res->count() . "\n";
echo "First...\n";
print_r($res->first());
foreach ($res as $r) {
// No output from this call.
print_r($r);
}And here is the output when running that code via cv:
=== NOTE Results are correct ===
count is: 4
First...
Array
(
[id] => 3928
[contact_id] => 10459
[financial_type_id] => 2
[contribution_page_id] => 14
[payment_instrument_id] => 1
[receive_date] => 2019-12-02 13:07:59
[non_deductible_amount] => 0.00
[total_amount] => 296.00
[fee_amount] => 8.88
[net_amount] => 287.12
[trxn_id] => in_1FlKFWAwDzuDdbFCuWUlA4EZ
[invoice_id] => 0f3dd1eda0400becb9ecd33c58d28ae8
[invoice_number] =>
[currency] => USD
[cancel_date] =>
[cancel_reason] => 0
[receipt_date] => 2019-12-02 13:07:56
[thankyou_date] =>
[source] => Online Contribution: PowerBase Subscription Payment with Monthly Payment Option
[amount_level] =>
[contribution_recur_id] => 19
[is_test] => 0
[is_pay_later] => 0
[contribution_status_id] => 1
[address_id] => 24745
[check_number] =>
[campaign_id] =>
[creditnote_id] =>
[tax_amount] =>
[revenue_recognition_date] =>
[is_template] => 0
)
Array
(
[id] => 3928
[contact_id] => 10459
[financial_type_id] => 2
[contribution_page_id] => 14
[payment_instrument_id] => 1
[receive_date] => 2019-12-02 13:07:59
[non_deductible_amount] => 0.00
[total_amount] => 296.00
[fee_amount] => 8.88
[net_amount] => 287.12
[trxn_id] => in_1FlKFWAwDzuDdbFCuWUlA4EZ
[invoice_id] => 0f3dd1eda0400becb9ecd33c58d28ae8
[invoice_number] =>
[currency] => USD
[cancel_date] =>
[cancel_reason] => 0
[receipt_date] => 2019-12-02 13:07:56
[thankyou_date] =>
[source] => Online Contribution: PowerBase Subscription Payment with Monthly Payment Option
[amount_level] =>
[contribution_recur_id] => 19
[is_test] => 0
[is_pay_later] => 0
[contribution_status_id] => 1
[address_id] => 24745
[check_number] =>
[campaign_id] =>
[creditnote_id] =>
[tax_amount] =>
[revenue_recognition_date] =>
[is_template] => 0
)
Array
(
[id] => 3951
[contact_id] => 10459
[financial_type_id] => 2
[contribution_page_id] => 14
[payment_instrument_id] => 1
[receive_date] => 2020-01-02 13:08:03
[non_deductible_amount] => 0.00
[total_amount] => 296.00
[fee_amount] => 8.88
[net_amount] => 287.12
[trxn_id] => in_1FwZ1bAwDzuDdbFCXoBfw8UM
[invoice_id] =>
[invoice_number] =>
[currency] => USD
[cancel_date] =>
[cancel_reason] =>
[receipt_date] => 2020-01-02 14:13:42
[thankyou_date] =>
[source] => Online Contribution: PowerBase Subscription Payment with Monthly Payment Option
[amount_level] =>
[contribution_recur_id] => 19
[is_test] => 0
[is_pay_later] => 0
[contribution_status_id] => 1
[address_id] =>
[check_number] =>
[campaign_id] =>
[creditnote_id] =>
[tax_amount] =>
[revenue_recognition_date] =>
[is_template] => 0
)
Array
(
[id] => 3999
[contact_id] => 10459
[financial_type_id] => 2
[contribution_page_id] => 14
[payment_instrument_id] => 1
[receive_date] => 2020-02-02 13:08:03
[non_deductible_amount] => 0.00
[total_amount] => 296.00
[fee_amount] => 8.88
[net_amount] => 287.12
[trxn_id] => in_1G7nnbAwDzuDdbFCvncHVNPH
[invoice_id] =>
[invoice_number] =>
[currency] => USD
[cancel_date] =>
[cancel_reason] =>
[receipt_date] => 2020-02-07 15:03:30
[thankyou_date] =>
[source] => Online Contribution: PowerBase Subscription Payment with Monthly Payment Option
[amount_level] =>
[contribution_recur_id] => 19
[is_test] => 0
[is_pay_later] => 0
[contribution_status_id] => 1
[address_id] =>
[check_number] =>
[campaign_id] =>
[creditnote_id] =>
[tax_amount] =>
[revenue_recognition_date] =>
[is_template] => 0
)
=== NOTE: count is one, but no results from first() ===
count is: 1
First...
www-data@fecbf38eb0bb:~/powerbase$