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,,,,,,,,,,,,,, FROM (((((((((((((persons 
LEFT JOIN persons AS father ON persons.father_id = 
LEFT JOIN persons AS mother ON persons.mother_id = 
LEFT JOIN persons AS fathersfather ON father.father_id = 
LEFT JOIN persons AS fathersmother ON father.mother_id = 
LEFT JOIN persons AS mothersfather ON mother.father_id = 
LEFT JOIN persons AS mothersmother ON mother.mother_id = 
LEFT JOIN persons AS fathersfathersfather ON fathersfather.father_id = 
LEFT JOIN persons AS fathersfathersmother ON fathersfather.mother_id = 
LEFT JOIN persons AS fathersmothersfather ON fathersmother.father_id = 
LEFT JOIN persons AS fathersmothersmother ON fathersmother.mother_id = 
LEFT JOIN persons AS mothersfathersfather ON mothersfather.father_id = 
LEFT JOIN persons AS mothersfathersmother ON mothersfather.mother_id = 
LEFT JOIN persons AS mothersmothersfather ON mothersmother.father_id = 
LEFT JOIN persons AS mothersmothersmother ON mothersmother.mother_id = ORDER BY;

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

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

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.