Return all parents of a given child id

PHOTO EMBED

Tue Aug 24 2021 06:36:11 GMT+0000 (UTC)

Saved by @codeplay #mysql

// 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;
        ";
content_copyCOPY

Recursive query or hierarchy

https://explainextended.com/2009/07/20/hierarchical-data-in-mysql-parents-and-children-in-one-query/