Custom Union Queries in Subpanel List View

👨‍💻 Development Help
1

Hi,

What is the proper way to write the UNION query for custom subpanel query?

Currently I have the following code from my custom function:

function get_custom_payments_list($params)
{
$args = func_get_args();
$subscription_id = $args[0][‘subscription_id’];
$return_array[‘select’] = " fp_payments.* ";
$return_array[‘from’] = " FROM fp_payments ";
$return_array[‘where’] = " WHERE fp_payments.payment_stage = ‘Completed’ AND fp_payments.deleted = 0 ";
$return_array[‘join’] = " INNER JOIN fp_subscriptions_fp_payments_1_c
ON fp_payments.id=fp_subscriptions_fp_payments_1_c.fp_subscriptions_fp_payments_1fp_payments_idb
AND fp_subscriptions_fp_payments_1_c.fp_subscriptions_fp_payments_1fp_subscriptions_ida=‘{$subscription_id}’
AND fp_subscriptions_fp_payments_1_c.payment_stage = ‘PartiallyPaid’
";
$return_array[‘join_tables’][0] = " UNION
SELECT fp_payments.*
FROM fp_payments
INNER JOIN fp_subscriptions_fp_payments_1_c
ON fp_payments.id=fp_subscriptions_fp_payments_1_c.fp_subscriptions_fp_payments_1fp_payments_idb
AND fp_subscriptions_fp_payments_1_c.fp_subscriptions_fp_payments_1fp_subscriptions_ida=‘{$subscription_id}’
AND fp_subscriptions_fp_payments_1_c.deleted=0
where fp_payments.payment_stage = ‘Completed’ AND fp_payments.excess_payment_c > 0
AND fp_payments.deleted=0 ";
$return_array[‘groupBy’] = " GROUP BY fp_payments.id ";
return $return_array;
}

The main query worked pretty well. However the union query doesn’t seem to work at all.

Best regards,

2

Hi, welcome to the Community! :tada:

There are a couple of things you can try, to diagnose.

  1. Check the second part of the query (and then, all of it) in phpMyAdmin, until you are sure your SQL is correct.

  2. Check how your SQL is being rendered into the final query that SuiteCRM builds. Set your log level to DEBUG in Admin / System Settings, run this part of the code, and look at the query in your logs.