// Custom query that will return the ancestors of a given id, it will // return a hierarchical data of parents and children in one query // For detailed explanation of the query, please see link below // https://explainextended.com/2009/07/20/hierarchical-data-in-mysql-parents-and-children-in-one-query/ $sql = " SELECT t2.id, t2.sender_id, t2.invitee_id, t2.deal_admin_id, t2.agent_structure_id, t3.level, t3.sub_level, t3.position_type FROM ( SELECT @r AS _invitee_id, (SELECT @r := sender_id FROM " . $agentInvitationTable . " WHERE invitee_id = _invitee_id AND status = :status LIMIT 1) AS sender_id, @l := @l + 1 AS lvl FROM (SELECT @r := :userId, @l := 0) vars, " . $agentInvitationTable . " t WHERE @r <> 0 ) t1 JOIN " . $agentInvitationTable . " t2 ON t1._invitee_id = t2.invitee_id LEFT JOIN " . $agentStructureTable . " t3 ON t2.agent_structure_id = t3.id WHERE t2.status = :status ORDER BY t1.lvl DESC; ";
Preview:
downloadDownload PNG
downloadDownload JPEG
downloadDownload SVG
Tip: You can change the style, width & colours of the snippet with the inspect tool before clicking Download!
Click to optimize width for Twitter