Complex SQL Query with JSON Aggregation
This SQL snippet selects data and includes an embedded query to create a JSON array with aggregated counts of child distributors by role. It utilizes functions like array_to_json and json_build_object for JSON manipulation.unknown
sql
10 months ago
3.1 kB
2
Indexable
SELECT SubQ.*, ( SELECT array_to_json(ARRAY ( SELECT json_build_object(child_r.role_code, count(child_d.id)) FROM distributors child_d, users child_u, roles_users child_ru, roles child_r WHERE child_d.personal_sponsor_distributor_id BETWEEN SubQ.distributor_id / 100 * 100 AND SubQ.distributor_id / 100 * 100 + 99 AND child_u.id = child_d.user_id AND child_ru.user_id = child_u.id AND child_r.id = child_ru.role_id AND child_u.status_id NOT IN (2, 3, 6, 8) AND child_d.is_master IS TRUE GROUP BY child_r.role_code))) count_personal_sponsored FROM ( SELECT ui.distributor_id, da.external_distributor_id, ui.user_id, ui.login, ui.email, ui.status_id, ui.entry_date, ui.role_start_date, ui.next_renewal_date, ui.service_renewal_date, s.name AS status_name, ui.phone, cc.code country_code, ui.firstname, ui.lastname, ui.role_code, ui.role_name, ui.is_guest, ui.personal_sponsor_distributor_id AS u_sponsor_id, ui.dualteam_sponsor_distributor_id AS d_sponsor_id, ui.dualteam_current_position AS position, CASE WHEN ui.status_id NOT IN (2, 3, 6, 8) AND ui.next_renewal_date > now() THEN TRUE ELSE FALSE END AS active, CASE WHEN ui.status_id NOT IN (2, 3, 6, 8) AND ui.service_renewal_date > now() THEN TRUE ELSE FALSE END AS service_active, sp.firstname AS sponsor_firstname, sp.lastname AS sponsor_lastname, sp.phone AS sponsor_phone, sp.email AS sponsor_email, ui.lifetime_rank AS rank_id, cr.name AS rank_name, cr_ass.id AS rank_image_id, cr_ass.attachment_file_name AS rank_image_name, cr_ass.type AS rank_image_type, ( SELECT ut.created_at FROM user_tracks ut WHERE ut.user_id = ui.user_id ORDER BY id DESC LIMIT 1) last_login, detail.pqv, detail.gqv, detail.additional_info, detail.active AS commission_active, pr_cr.rank_identity AS curr_rank_id, pr_cr.name AS curr_rank_name, pr_ass.id AS curr_rank_image_id, pr_ass.attachment_file_name AS curr_rank_image_name, pr_ass.type AS curr_rank_image_type FROM user_infos ui INNER JOIN statuses s ON ui.status_id = s.id LEFT JOIN bonus.bonusm20240501_details_unilevel detail ON detail.distributor_id = ui.distributor_id LEFT JOIN bonus.bonusm20240501_ranks rank ON rank.distributor_id = ui.distributor_id LEFT JOIN client_ranks pr_cr ON pr_cr.rank_identity = rank.paid_rank AND pr_cr.rank_identity >= 40 LEFT JOIN assets pr_ass ON pr_ass.viewable_id = pr_cr.id AND pr_ass.viewable_type = 'ClientRank' LEFT JOIN user_infos sp ON ui.personal_sponsor_distributor_id = sp.distributor_id LEFT JOIN client_ranks cr ON cr.rank_identity = ui.lifetime_rank AND cr.rank_identity >= 40 LEFT JOIN assets cr_ass ON cr_ass.viewable_id = cr.id AND cr_ass.viewable_type = 'ClientRank' LEFT JOIN country_codes cc ON cc.country_iso = ui.country_iso LEFT JOIN distributor_addons da ON ui.distributor_id = da.distributor_id WHERE ui.distributor_id = 10401901 AND ui.status_id NOT IN (2, 3, 6, 8)) SubQ;
Editor is loading...
Leave a Comment