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,