The query builds a materialized path field (path) which later will allow us to load the data into the object. GetDogKennelSlug(m.kennel_id) AS kennel_slug, GetDogKennelSlug(f.kennel_id) AS kennel_slug,ĬONCAT_WS('_', `path`, 'mother') AS `path`,

GetDogKennelSlug(kennel_id) AS kennel_slug,ĬONCAT_WS('_', `path`, 'father') AS `path`, A simplified query would be: WITH RECURSIVE ped_cte ( With the above solution, I ran into MySQL 61 joins limit for 5+ generations pedigrees, an I found that MySQL version 8+ now supports recursive CTE queries.

That could be a followup question I guess. In my own project, I am still trying to decide if I will port the code from the example of the link above, or use a JS library to generate either HTML5 or SVG graphics. Now for printing the pedigree, having the data in Django ORM objects should help. Sql = 'SELECT a.Sire, a.Dam, s.'Registered Name', d.'Registered Name' FROM dogs a INNER JOIN dogs s ON a.Sire = s.did INNER JOIN dogs d ON a.Dam = d.did WHERE a.did = 'ĭog.append(.id''' I've created a few different recursive functions that do correctly determine the ancestors of a child, but I am having difficulty outputting in a meaningful manner.Ĭursor = con.execute('SELECT 'Call Name', ROUND(((JulianDay('now') - JulianDay('Date of Birth'))/365.25),1), 'Sex' FROM dogs where did IS %s ' % did).fetchall() Sire = models.TextField(db_column='Sire', blank=true, null=true)ĭam = models.TextField(db_column='Dam', blank=true, null=true) Name = models.TextField(db_column='name', blank=true, null=true) My model looks like such (simplified for the question): class Dogs(models.Model):ĭid = models.IntegerField(blank=true, null=true)

I am wanting to dynamically create a table like this: I'm up for tackling this in any direction! I've seen many projects in php but none in python. I am in the process of creating a site with Django for my wife and her dog club.

Blank dog pedigree chart