0

I'm trying to do a select query for multiple infinite levels in MYSQL 5.7 I know I'm limited somehow, but I'm bound to the version and user hashes right now. I am working with Codeigniter 3.

My table looks like this:

Id user_hash sponsor_hash
1 sdf320380sdjnsd0wq0sdfuwe038we08d8sca v90349dlksdfgbjdfjksdjhsdzadisxb34237sda
2 jsdfhsdsdfh42333489dsdhsd9wehsd80shsd sdf320380sdjnsd0wq0sdfuwe038we08d8sca
3 sjhsad78w3s78sdag3e97scjhww8a7sd8df0d v90349dlksdfgbjdfjksdjhsdzadisxb34237sda
3 vusdfjsdiflsdjldfudf90df0h76sd79w34ns4334 sjhsad78w3s78sdag3e97scjhww8a7sd8df0d

I need to get all levels deep from a certain sponsor_hash.

Root level is so to say one sponsor_hash.

I tried to implement solutions from below but it didn't work for me yet. I'm not sure how to create a recursive MYSQL query (or sth similar to that).

My result should be like this:

array[0] = all level 1 children
array[1] = all level 2 children
array[2] = all level 3 children

etc..

in this case it would be at root level = 'v90349dlksdfgbjdfjksdjhsdzadisxb34237sda' (sponsor_hash from Id=1) :

array[0] = array(
   'sdf320380sdjnsd0wq0sdfuwe038we08d8sca',
   'sjhsad78w3s78sdag3e97scjhww8a7sd8df0d'
)
array[1] = array(
   'jsdfhsdsdfh42333489dsdhsd9wehsd80shsd',
   'vusdfjsdiflsdjldfudf90df0h76sd79w34ns4334'
)

My first try is this but it returns only FIRST level:

select  id,
        user_hash,
        sponsor_hash
from    (select * from table
         order by sponsor_hash, id) table_sorted,
        (select @pv := 'v90349dlksdfgbjdfjksdjhsdzadisxb34237sda') initialisation
where   find_in_set(sponsor_hash, @pv)
and     length(@pv := concat(@pv, ',', user_hash))

My second try is a little different, but is not really efficient, because it calls Queries in a recursive function (from third link above). it generates 'sub' objects which is not the way I need it

$generations = $this->db->select('user_hash')->where('sponsor_hash', $user_hash)->get('generations')->result();

foreach ($generations as $k=>$v) {
            $generations[$k]->sub = $this->generations($v->user_hash);
        }

function generations($ids) {
    $generations = $this->db->select('u.ids')->where('sponsor_hash', $ids)->get('generations')->result();

    foreach ($generations as $k=>$v) {
        $generations[$k]->sub = $this->generations($v->user_hash);
    }
    return $generations;
}

Can anybody give me some hints on how to realise that? Maybe it can be solved in PHP as well.

Would highly appreciate it. Best regards

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
Frechdachs
  • 61
  • 2
  • 7
  • 2
    The how to create a recursive query is answered in the questions you linked. If you cannot implement the solution, then show us what you tried and what error message / unexpected results you are getting! – Shadow Dec 05 '21 at 11:39
  • Your MySql / Mariadb version matters to the answer. – O. Jones Dec 05 '21 at 11:42
  • @O.Jones MariaDB 5.7.36 PHP-Version: 7.4.25 libmysql - mysqlnd 7.4.25 – Frechdachs Dec 05 '21 at 11:47
  • @NicoHaase ive edited my first try above. it returns only first level. – Frechdachs Dec 05 '21 at 11:51
  • @Shadow ive edited my first try above. it returns only first level – Frechdachs Dec 05 '21 at 11:55
  • 1
    your first attempt requires parent > child or other way round for all records. your sample data is not so. – ProDec Dec 05 '21 at 12:00
  • @ProGu ah ok. yes so is there any other solution to solve this with the hashes? I didn't find any so far. hmm.. – Frechdachs Dec 05 '21 at 12:01
  • MySql 5.7 lacks Common Table Expressions (CTEs). So to solve your problem with a query you'll need a hard limit to the depth of your hierarchy, and you'll need a verbose query. – O. Jones Dec 05 '21 at 13:32
  • @O.Jones ok, or would it be possible to select all entries and do the rest in a while loop somehow? – Frechdachs Dec 05 '21 at 13:37
  • For MySQL earlier than 8.0, you may find it easier to use one of the workarounds that involve storing extra data to represent the hierarchy. See https://stackoverflow.com/a/192462/20860 or https://stackoverflow.com/q/4048151/20860 – Bill Karwin Dec 05 '21 at 13:58

0 Answers0