Need help with big query for family tree

I am trying to build a family tree from a single table persons. I have this SQL query that works fine when I use it directly in phpmyadmin:

SELECT DISTINCT persons.name, father.name, mother.name, fathersfather.name, fathersmother.name, mothersfather.name, mothersmother.name, fathersfathersfather.name, 
fathersfathersmother.name, fathersmothersfather.name, fathersmothersmother.name, mothersfathersfather.name, mothersfathersmother.name, mothersmothersfather.name, 
mothersmothersmother.name FROM (((((((((((((persons 
LEFT JOIN persons AS father ON persons.father_id = father.id) 
LEFT JOIN persons AS mother ON persons.mother_id = mother.id) 
LEFT JOIN persons AS fathersfather ON father.father_id = fathersfather.id) 
LEFT JOIN persons AS fathersmother ON father.mother_id = fathersmother.id) 
LEFT JOIN persons AS mothersfather ON mother.father_id = mothersfather.id) 
LEFT JOIN persons AS mothersmother ON mother.mother_id = mothersmother.id) 
LEFT JOIN persons AS fathersfathersfather ON fathersfather.father_id = fathersfathersfather.id) 
LEFT JOIN persons AS fathersfathersmother ON fathersfather.mother_id = fathersfathersmother.id) 
LEFT JOIN persons AS fathersmothersfather ON fathersmother.father_id = fathersmothersfather.id) 
LEFT JOIN persons AS fathersmothersmother ON fathersmother.mother_id = fathersmothersmother.id) 
LEFT JOIN persons AS mothersfathersfather ON mothersfather.father_id = mothersfathersfather.id) 
LEFT JOIN persons AS mothersfathersmother ON mothersfather.mother_id = mothersfathersmother.id) 
LEFT JOIN persons AS mothersmothersfather ON mothersmother.father_id = mothersmothersfather.id) 
LEFT JOIN persons AS mothersmothersmother ON mothersmother.mother_id = mothersmothersmother.id ORDER BY persons.name;

Now I am trying to transform that working query into a query that Laravel accepts:

 $tree = \DB::table('persons')
      ->distinct(['persons.name, father.name, mother.name, fathersfather.name, fathersmother.name, mothersfather.name, mothersmother.name, fathersfathersfather.name, fathersfathersmother.name, fathersmothersfather.name, fathersmothersmother.name, mothersfathersfather.name, mothersfathersmother.name, mothersmothersfather.name, mothersmothersmother.name'])
      ->leftjoin('persons as father','persons.father_id', '=', 'father.id')
      ->leftjoin('persons as mother','persons.mother_id', '=', 'mother.id')
      ->leftjoin('persons as fathersfather','father.father_id', '=', 'fathersfather.id')
      ->leftjoin('persons as fathersmother','father.mother_id', '=', 'fathersmother.id')
      ->leftjoin('persons as mothersfather','mother.father_id', '=', 'mothersfather.id')
      ->leftjoin('persons as mothersmother','mother.mother_id', '=', 'mothersmother.id')
      ->leftjoin('persons as fathersfathersfather','fathersfather.father_id', '=', 'fathersfathersfather.id')
      ->leftjoin('persons as fathersfathersmother','fathersfather.mother_id', '=', 'fathersfathersmother.id')
      ->leftjoin('persons as fathersmothersfather','fathersmother.father_id', '=', 'fathersmothersfather.id')
      ->leftjoin('persons as fathersmothersmother','fathersmother.mother_id', '=', 'fathersmothersmother.id')
      ->leftjoin('persons as mothersfathersfather','mothersfather.father_id', '=', 'mothersfathersfather.id')
      ->leftjoin('persons as mothersfathersmother','mothersfather.mother_id', '=', 'mothersfathersmother.id')
      ->leftjoin('persons as mothersmothersfather','mothersmother.father_id', '=', 'mothersmothersfather.id')
      ->leftjoin('persons as mothersmothersmother','mothersmother.mother_id', '=', 'mothersmothersmother.id')
      ->where('persons.family_id' ,'=', $family_id)
      ->get();

But all I get are null values (in all 15 arrays, this is only the first one):

Collection {#876 ▼
  #items: array:15 [▼
    0 => {#860 ▼
      +"id": null
      +"father_id": null
      +"mother_id": null
      +"user_id": null
      +"family_id": null
      +"name": null
      +"born": null
      +"died": null
      +"image": null
      +"story": null
      +"created_at": null
      +"updated_at": null
      +"gender": null

Can someone please see what I am doing wrong?

lostdreamer_nl

7 years ago

Level 53

Why are you trying to do all this in MySQL?

if you simply get the whole family in 1 simple get query, you can put the whole tree together in 1 loop, maybe a second loop for some cleanup....

Here's an example as I use it to create a tree of categories with a parent_id :

public static function buildTree()
{
    if(!$collection) $collection = Category::get();
    
    $collection = $collection->sortBy(function($row) {
        if(is_null($row->parent_id)) return 0 .'_'. $row->id;
        return $row->parent_id .'_'. $row->id;
    });
    
    $tree = [];
    foreach($collection as $category) {
        $tree[$category->id] = $category;
        if($category->parent_id) {
            $tree[$category->parent_id]->children[] = $category;
        }
    }
    
    foreach($tree as $id => $cat) {
        if($cat->parent_id) {
            unset($tree[$id]);
        }
    }
    
    return $tree;
}

The reason this works is because in PHP, Objects are always passed by reference, unless you explicitly clone them.

So when you add $category to $someOtherCategory->children[] and $someOtherCategory is also a child of $aTopCategory , $category will be added to all of those objects (because they are actually just 1 object, with links between the 'copies')

It's very handy if you get used to it, this way you can create trees of uncertain depth by simply passing it the whole collection and it's very memory efficient.

I havent got the time right now to change this function into your specific usecase, but if you're trying it out and get stuck, just let me know and I'll try and help some more.

Tommy001

7 years ago

Level 1

@LOSTDREAMER_NL - That sounds like a better way to do it. I will try to figure out how to adapt your example to my use case. Thanks!

I still would like to know what I am doing wrong in my query though, if there is a query builder expert out there,

kobear

7 years ago

Level 4

@TOMMY001 - try using artisan tinker to load that query, but instead of running it with ->get(), use ->toSql(). That will output the query that Laravel uses. Then try running that query in phpmyadmin to see what it is doing wrong.

1 like

Tommy001

7 years ago

Level 1

@KOBEAR - Thanks, I will give that a try. I didn't know that was possible.

Corbin

7 years ago

Level 9

Something about the way that you're implementing this from one table seems strange. I'd recommend that you check out this slide show on structuring nested data. I'd specifically recommend looking at closure tables, starts on slide 40, and seeing if that works for you.

If you feel closure tables are the way to go you can try using the franzose closure table package.

This is just food for thought here. You might have specific reasons for this implementation.

lostdreamer_nl

7 years ago

Level 53

@corbin nothing wrong with that..... lookinto "self referencing table"

This is exactly the same as having a parentID on your categories table, but now you have 2 of them (father_id / mother_id)

They're all still 'Persons' so they should only have 1 table.

The thing with closure tables is, they give possibility for having multiple parents, while father_id and mother_id do not.

Please or to participate in this conversation.