How to Recursively Obtain the "Parent Id" of Rows in This MySQL Table

How can I recursively obtain the parent ID of rows in this MySQL table?

This website has a really nice overview of the different methods for storing hierarchical data in mysql and PHP. To answer your question, the easiest way is to use php and recursion. There are other methods you could use such as the modified preorder transversal, which don't require multiple database queries. But this method can be more complex to implement when dealing with a lot of insertions and updates.

Another really cool method and my personal favorite is the so called "closure table" / "adjacency relation" mentioned in What is the most efficient/elegant way to parse a flat table into a tree?

Regarding your comment you basically have to make a loop or a recursive function that selects the parent of chicago, then the parent of the parent and so forth.

$stack = array();
$parent = 3;
while($parent != 0){
    $data = (put your mysql to get the row with parentID = $parent)
    $parent = data['parentID'];
    $stack[] = $data;
}

$stack = array_reverse($stack);

Stack will then contain the parents of Chicago, (ie. location, USA)

MySql Recursive - get all children and parents from a given id

I would use two separate recursive queries: one to bring the children, the other for the parents, and then union the results. You can keep track of the level of each node to properly order the records int he resultset:

with recursive 
    children as (
        select 1 as lvl, d.* from department d where id = 6
        union all
        select c.lvl, d.* from department d inner join children c on c.id = d.father
    ),
    parents as (
        select 1 as lvl, d.* from department d where id = 6
        union all
        select p.lvl - 1, d.* from department d inner join parents p on d.id = p.father
    )
select * from parents
union   -- on purpose, to remove the duplicate on id 6
select * from children
order by lvl;

This is safer than having multiple union all members in the same query. MySQL does not guarantee the order of evaluation of the members in the recursion, so using this technique could lead to unexpected behavior.

Demo on DB Fiddle

---

Unrelated to your question, but: the following can be seen in your code:

SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
SET SESSION sql_mode = '';

Just don't. ONLY_FULL_GROUP_BY is there for a good reason, that is to have MySQL behave consistenly with the SQL standard as regard to aggregation query. Disabling this SQL mode is never a good idea.

Get parents recursive in right order for deletion from MySQL table

Assuming you don't have access to TRUNCATE, SET (so you could do SET FOREIGN_KEY_CHECKS=0;), ALTER, etc. etc., and absolutely must use a script:

Since the question is tagged with php, this should do the trick:

function reversetree($src_arr, $currentid = 0)
{
    $cats = array();
    foreach($src_arr as $id => $parent)
    {   
        if($parent == $currentid)
        {
            $cats[] = $id;
            $cats = array_merge($cats, reversetree($src_arr, $id));
        }
    }
    return !$currentid ? array_reverse($cats) : $cats;
}

$rs = array();
foreach($pdo->query('SELECT id, parent_id FROM categories') as $row)
    $rs[$row['id']] = $row['parent_id'];

$stmt = $pdo->prepare('DELETE FROM categories WHERE id = ?');

$pdo->beginTransaction();
foreach(reversetree($rs) as $v)
    $stmt->execute(array($v));
$pdo->commit();

How to create a SQL call that will recursively get DISTINCT ids and parent ids combined into a single column?

In MySQL 8.0 and above you can use a recursive CTE to traverse the hierarchy. But as your customers already have some parent companies of the companies they have assigned (which seems a little odd to me), that will result in duplicates. So you need to get the distinct set. For convenience I use an other CTE for that, but you could also skip that and do the DISTINCT directly in your query. Then you can simply query for the companies of a customer from the CTE.

WITH RECURSIVE
cte1 AS
(
SELECT cu.customer_id,
       cu.company_id
       FROM customer cu
UNION ALL
SELECT ct.customer_id,
       co.parent_id company_id
       FROM cte1 ct
            INNER JOIN company co
                       ON ct.company_id = co.company_id
       WHERE co.parent_id IS NOT NULL
),
cte2 AS
(
SELECT DISTINCT
       ct.customer_id,
       ct.company_id
       FROM cte1 ct
)
SELECT company_id
       FROM cte2
       WHERE customer_id = 2;

DB Fiddle

Of course you could also shift the condition on the customer ID into the anchor of the recursive CTE. That may reduce the costs for the query if you ever only need this for exactly one customer.

Recursively grab all data based on a parent id

This can be done in SQL Server 2005 and above using Common Table Expressions (CTEs). Here is a great link from MSDN describing recursive queries: Recursive Queries Using Common Table Expressions

Here is an example:

If you imagine a hierarchical line of people, this query will let you see the complete line of any person AND calculates their place in the hierarchy. It can be modified to find any child relationship.

Instead of the ID of the person, you swap in the ID of the row you are using as your parent.

--Create table of dummy data
create table #person (
personID integer IDENTITY(1,1) NOT NULL,
name      varchar(255) not null,
dob       date,
father    integer
);

INSERT INTO #person(name,dob,father)Values('Pops','1900/1/1',NULL);  
INSERT INTO #person(name,dob,father)Values('Grandma','1903/2/4',null);
INSERT INTO #person(name,dob,father)Values('Dad','1925/4/2',1);
INSERT INTO #person(name,dob,father)Values('Uncle Kev','1927/3/3',1);
INSERT INTO #person(name,dob,father)Values('Cuz Dave','1953/7/8',4);
INSERT INTO #person(name,dob,father)Values('Billy','1954/8/1',3);

DECLARE @OldestPerson INT; 
SET @OldestPerson = 1; -- Set this value to the ID of the oldest person in the family

WITH PersonHierarchy (personID,Name,dob,father, HierarchyLevel) AS
(
   SELECT
      personID
      ,Name
      ,dob
      ,father,
      1 as HierarchyLevel
   FROM #person
   WHERE personID = @OldestPerson

   UNION ALL

   SELECT
    e.personID,
      e.Name,
      e.dob,
      e.father,
      eh.HierarchyLevel + 1 AS HierarchyLevel
   FROM #person e
      INNER JOIN PersonHierarchy eh ON
         e.father = eh.personID
)

SELECT *
FROM PersonHierarchy
ORDER BY HierarchyLevel, father;

DROP TABLE #person;

How do I recursively find all matching rows in a table?

NOTE: This solution will work in MariaDB 10.2.2 and above.

Try Common Table Expression:

with recursive cte(child_id, parent_id) as (
    select child_id, parent_id from MyTable
    where parent_id = 7    
       union all 
    select mt.child_id, mt.parent_id from MyTable mt
         inner join cte t on mt.parent_id = t.child_id
)
select * from cte;

MySQL - Recursively list all parents and ancestors of all items in table

If you are running MySQL 8.0, this is best solved with a recursive query:

with recursive cte as (
    select id, parent_id, 1 lvl from mytable
    union all
    select c.id, t.parent_id, lvl + 1
    from cte c
    inner join mytable t on t.id = c.parent_id
)
select id, group_concat(parent_id order by lvl) all_parents
from cte
group by id

Demo on DB Fiddle:

id | all_parents
-: | :----------
 1 | 0          
 2 | 0          
 3 | 0          
17 | 3,0        
31 | 17,3,0     

---

Related Topics