Tommy001
9 months ago

Need help with big query for family tree

Posted 9 months ago by Tommy001

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?

Please sign in or create an account to participate in this conversation.