closure table queries

<?php

namespace App\Repositories\Backend;

use App\Models\Member;
use Illuminate\Support\Facades\DB;
use App\Exceptions\GeneralException;
use App\Repositories\BaseRepository;
use Illuminate\Pagination\LengthAwarePaginator;

/**
 * Class MemberRepository.
 */
class MemberRepository extends BaseRepository
{
    /**
     * @return string
     */
    public function model()
    {
        return Member::class;
    }

    /**
     * @param int    $paged
     * @param string $orderBy
     * @param string $sort
     *
     * @return mixed
     */
    public function getActivePaginated($paged = 25, $orderBy = 'created_at', $sort = 'desc') : LengthAwarePaginator
    {
        return $this->model
            ->orderBy($orderBy, $sort)
            ->paginate($paged);
    }

    /**
     * @param int    $paged
     * @param string $orderBy
     * @param string $sort
     *
     * @return LengthAwarePaginator
     */
    public function getDeletedPaginated($paged = 25, $orderBy = 'created_at', $sort = 'desc') : LengthAwarePaginator
    {
        return $this->model
            ->onlyTrashed()
            ->orderBy($orderBy, $sort)
            ->paginate($paged);
    }

    /**
     * @param array $data
     *
     * @return Member
     * @throws \Exception
     * @throws \Throwable
     */
    public function create(array $data) : Member
    {
        return DB::transaction(function () use ($data) {
            $member = parent::create([
                'username' => $data['username'],
            ]);

            if ($member) {
                return $member;
            }

            throw new GeneralException(__('backend_members.exceptions.create_error'));
        });
    }

    public function addToBinaryTree($newId, $parentId)
    {
        /*        DB::insert('INSERT INTO binary_plan_tree_paths (ancestor, descendant)
                        SELECT t.ancestor, :newid
                        FROM binary_plan_tree_paths AS t
                        WHERE t.descendant = :parentid
                        UNION ALL
                        SELECT :newid, :newid;', ['newid'=>$newId,'parentid'=>$parentId]);
        */
       
        DB::insert('INSERT INTO binary_plan_tree_paths (ancestor, descendant,depth)
                SELECT t.ancestor, ? , depth+1 
                FROM binary_plan_tree_paths AS t
                WHERE t.descendant = ?
                UNION ALL
                SELECT ?, ?,0', [$newId,$parentId,$newId,$newId]);
    }
    /**
     * @param Member  $member
     * @param array     $data
     *
     * @return Member
     * @throws GeneralException
     * @throws \Exception
     * @throws \Throwable
     */
    public function update(Member $member, array $data) : Member
    {
        return DB::transaction(function () use ($member, $data) {
            if ($member->update([
                'username' => $data['username'],
            ])) {
                return $member;
            }

            throw new GeneralException(__('backend_members.exceptions.update_error'));
        });
    }

    /**
     * @param Member $member
     *
     * @return Member
     * @throws GeneralException
     * @throws \Exception
     * @throws \Throwable
     */
    public function forceDelete(Member $member) : Member
    {
        if (is_null($member->deleted_at)) {
            throw new GeneralException(__('backend_members.exceptions.delete_first'));
        }

        return DB::transaction(function () use ($member) {
            if ($member->forceDelete()) {
                return $member;
            }

            throw new GeneralException(__('backend_members.exceptions.delete_error'));
        });
    }

    /**
     * Restore the specified soft deleted resource.
     *
     * @param Member $member
     *
     * @return Member
     * @throws GeneralException
     */
    public function restore(Member $member) : Member
    {
        if (is_null($member->deleted_at)) {
            throw new GeneralException(__('backend_members.exceptions.cant_restore'));
        }

        if ($member->restore()) {
            return $member;
        }

        throw new GeneralException(__('backend_members.exceptions.restore_error'));
    }

    public function getFullTree($root)
    {
        $nodes =   DB::select(
            'SELECT
                        members.id,
                        members.first_name,
                        members.leg_side,
                        members.parent_id,
                        binary_plan_tree_paths.ancestor AS parent_id
                    FROM members
                        JOIN binary_plan_tree_paths ON members.id=binary_plan_tree_paths.ancestor
                    WHERE binary_plan_tree_paths.descendant=?

                    UNION
                    SELECT
                        members.id,
                        members.first_name,
                        members.leg_side,
                        members.parent_id,
                        binary_plan_tree_paths.ancestor AS parent_id
                    FROM members
                        JOIN binary_plan_tree_paths ON members.id=binary_plan_tree_paths.descendant
                    WHERE binary_plan_tree_paths.ancestor=?',
            [$root,$root]
        );

        return $nodes;
    }
    public function getAllChildWithPath($root)
    {
        $nodes =   DB::select(
            'SELECT group_concat(m.first_name ORDER BY m.id SEPARATOR  " -> ") as path
            FROM binary_plan_tree_paths d
            JOIN binary_plan_tree_paths a ON (a.descendant = d.descendant)
            JOIN members m ON (m.id = a.ancestor)
            WHERE d.ancestor = ? AND d.descendant != d.ancestor
            GROUP BY d.descendant;',
            [$root]
        );
        return $nodes;
    }

    public function getAllChildWithPathAndLegSide($root)
    {
        $nodes =   DB::select(
            'SELECT m.id,group_concat(m.leg_side ORDER BY m.id SEPARATOR  " -> ") as path
            FROM binary_plan_tree_paths d
            JOIN binary_plan_tree_paths a ON (a.descendant = d.descendant)
            JOIN members m ON (m.id = a.ancestor)
            WHERE d.ancestor = ? AND d.descendant != d.ancestor
            GROUP BY d.descendant,m.id',
            [$root]
        );
        return $nodes;
    }

    public function getAllChildWithPathAndLegSide2($root)
    {
        $nodes =   DB::select(
            '
            SELECT m.id
            FROM binary_plan_tree_paths d
            JOIN binary_plan_tree_paths a ON (a.descendant = d.descendant)
            JOIN members m ON (m.id = a.ancestor)
            WHERE d.ancestor = ? AND d.descendant != d.ancestor
            ',
            [$root]
        );
        
        /*
        // usefull query to under stand
        SELECT a.ancestor , a.descendant
        FROM binary_plan_tree_paths d
        JOIN binary_plan_tree_paths a ON (a.descendant = d.descendant)
        WHERE d.ancestor = 2 AND d.descendant != d.ancestor
        */
       
        /*
        //query to fetch all encestor of given node
         SELECT  distinct a.ancestor , a.descendant
         FROM binary_plan_tree_paths d
         JOIN binary_plan_tree_paths a ON (a.descendant = d.descendant)
         WHERE a.descendant = 8 AND d.descendant != d.ancestor
         */
        
        /*
        // display all child with count off all ancestor including self.

        SELECT
            a.descendant,
            COUNT(a.ancestor)
        FROM
            binary_plan_tree_paths d
        JOIN
            binary_plan_tree_paths a
        ON
            (a.descendant = d.descendant)
        WHERE
            d.ancestor = 1 AND d.descendant != d.ancestor
        GROUP BY
            a.descendant

         */
        
        /*
        FETCH ALL CHILDERN WITH THEIR CHILD NOT INCLUDE LEAF
    SELECT a.ancestor , a.descendant
        FROM binary_plan_tree_paths d
        JOIN binary_plan_tree_paths a ON (a.descendant = d.descendant)
        WHERE d.ancestor = 1 AND a.descendant != a.ancestor
ORDER BY `a`.`ancestor` ASC

         */
        
        /*

        create view for use in query
         create view binary_closure as SELECT b.ancestor ,ma.leg_side as ancestor_leg_side, b.descendant,md.leg_side as descendant_leg_side,depth
         FROM binary_plan_tree_paths b
         join members ma on (b.ancestor = ma.id )
         join members md on (b.descendant = md.id )
         */
       
        /*

        // query to display ances leg sied and child  from tree of given node
        SELECT a.ancestor ,a.ancestor_leg_side , a.descendant
                FROM binary_closure d
                JOIN binary_closure a ON (a.descendant = d.descendant)
                WHERE d.ancestor = 1 AND d.descendant != d.ancestor
        ORDER BY `a`.`ancestor` ASC


         */
        
        /*
        in following query
        a.descendant = d.descendant and a.descendant != a.ancestor
        is added in on() to remove  self reference path (the extra path).

        SELECT a.ancestor ,a.ancestor_leg_side , a.descendant
        FROM binary_closure d
        JOIN binary_closure a ON (a.descendant = d.descendant and a.descendant != a.ancestor)
        WHERE d.ancestor = 1 AND d.descendant != d.ancestor
        ORDER BY `a`.`descendant`  ASC

        */
        
        /*

        // query to find all leftmost nodes
        --faulty --
        SELECT  a.descendant, count(if(a.ancestor_leg_side='right',1,null)) as rcount
        FROM binary_closure d
        JOIN binary_closure a ON (a.descendant = d.descendant AND d.descendant != d.ancestor )
        WHERE d.ancestor = 6
        group by a.descendant
        having( rcount = 0 )

         */
        
        /*

        // query to find all leftmost nodes ( tree is sub tree from start node )

        SELECT a.descendant, COUNT( IF( a.ancestor_leg_side = 'right', 1, NULL ) ) AS rcount
        FROM binary_closure d JOIN binary_closure a ON (a.descendant = d.descendant)
        WHERE d.ancestor = 10 AND a.ancestor > 10
        GROUP BY a.descendant HAVING (rcount = 0)

       // helps to understand above
        >1 so paths from up level not included
       SELECT  a.descendant,a.ancestor , a.ancestor_leg_side
                FROM binary_closure d
                JOIN binary_closure a ON ( a.descendant = d.descendant)
                WHERE d.ancestor = 1  and a.ancestor > 1
         */
        

        /* select the left most node of given node ( from subtree built considering givrn node as root)
                select max(a.descendant) from (SELECT a.descendant, COUNT( IF( a.ancestor_leg_side = 'right', 1, NULL ) ) AS rcount
        FROM binary_closure d JOIN binary_closure a ON (a.descendant = d.descendant)
        WHERE d.ancestor = 1 AND a.ancestor > 1
        GROUP BY a.descendant HAVING (rcount = 0)) as a

         */
        
        /* select the right most node of given node ( from subtree built considering givrn node as root)
              select max(a.descendant) from (SELECT a.descendant, COUNT( IF( a.ancestor_leg_side = 'left', 1, NULL ) ) AS rcount
        FROM binary_closure d JOIN binary_closure a ON (a.descendant = d.descendant)
        WHERE d.ancestor = 1 AND a.ancestor > 1
        GROUP BY a.descendant HAVING (rcount = 0)) as a

         */
        

        return $nodes;
    }
}