0

I have table 'pe' with columns id, name, and lcltyid I have table 'wp_exrz_locality_localities' with id, name, and parent

the locality table is a tree and the parent contains an id of another locality row. pe.lcltyid is a key wp_exrz_locality_localities.id

basically what I want to do is retrieve all 'pe' entries sorted by their "tree depth"

However the total depth of the tree can be any amount at any time. And I need the depth in a way that allows me to use it in a sub query for sorting.

Originally I thought I needed a stored proceedure/function to get the depth of a lclty entry. After i made the proceedure I found out that proceedures cant be used in expressions. Then I tried to make a function but binary logging is enabled by me host and "log_bin_trust_function_creators = 0", so no stored functions for me.

Lastly I am trying to understand recursion but can 't seem to make it work. I am just trying to create a recursive statement that will retrieve the "depth" meaning the number of parents for an individual node up until the top node, or when the parent = 0 I just get an error "syntax to use near 'RECURSIVE node_ancestors..."

WITH RECURSIVE node_ancestors(id, parent) AS (
    SELECT id, id FROM `wp_exrz_locality_localities` WHERE id IN (1, 2, 3)
UNION ALL
    SELECT na.id, wp_exrz_locality_localities.parent
        FROM node_ancestors AS na, wp_exrz_locality_localities
        WHERE wp_exrz_locality_localities.id = na.parent AND wp_exrz_locality_localities.parent != 0
)
SELECT id, COUNT(parent) AS depth FROM node_ancestors GROUP BY id;

Any help is greatly appreciated

an example: EDIT table pe:

id---name---lcltyid
2---first---4
3---second---3

table wp_exrz_locality_localities:

id---name---parent
1---USA---0
3---SanFran---1
4---California---3


SELECT * FROM 'pe' ORDER BY ([lcltydepth]) ASC;

desired output:

id---name---lcltyid
3---second---3
2---first---4

where lclctydepth is 3 for the "first" pe and 2 for "second" because the second one is attached to a state with only the US above it and the first one is attached to a city, with state and US above it. So it would order them by the number of parents required to get the the last parent with parentid = 0;

I hope this helps?

noone392
  • 1,624
  • 3
  • 18
  • 30
  • MySql does not support the `WITH` syntax, and by consequence not the `RECURSIVE` clause either. – trincot Sep 04 '16 at 17:48
  • I am certainly confused what planet this code came from also :p – Drew Sep 04 '16 at 17:51
  • I could probably point you to a few of my tree depth stored proc answers. I am not sure why you cant use stored procs. They aren't speed demons but they will deliver the goods. – Drew Sep 04 '16 at 17:53
  • I thought I was looking at examples from mysql haha appearantly not? – noone392 Sep 04 '16 at 17:53
  • @Drew but I thought stored procs cant be used as expressions? – noone392 Sep 04 '16 at 17:53
  • Possible duplicate of [How to create a MySQL hierarchical recursive query](http://stackoverflow.com/questions/20215744/how-to-create-a-mysql-hierarchical-recursive-query) – trincot Sep 04 '16 at 17:53
  • I just have no background on your need. Trincot has a nice answer on that one. You would have to tailor your stacked `left joins` – Drew Sep 04 '16 at 17:54
  • You can use stored functions in expressions, but I would suggest to look at the link I posted above where lots of different methods are proposed. – trincot Sep 04 '16 at 17:56
  • I want to sort the table 'pe' by its locality depth. so 'pe' has a lcltyid that points to s specific node. I want to calculate the node heirarchy and use that to sort the 'pe' table – noone392 Sep 04 '16 at 17:56
  • @trincot if you read above i cant use stored functions because my host has binary logging enabled and i dont have the permissions – noone392 Sep 04 '16 at 17:57
  • I think trincot has decent stuff to look at in the reference provided and without stored functions. Maybe native functions at the most. – Drew Sep 04 '16 at 17:59
  • @trincot This is really nice, am I understanding correctly that this only works for a specific tree depth (in the example is 4)? – noone392 Sep 04 '16 at 18:06
  • Also I don't see how I could use the example provided as a way to Sort By?? – noone392 Sep 04 '16 at 18:07
  • Which solution are you referring to? There are many good solutions that have been posted there... – trincot Sep 04 '16 at 18:15
  • To understand your requirements, it would be good if you would add some sample data in your question, and the outcome you desire for that data. – trincot Sep 04 '16 at 18:18

0 Answers0