Query Ideas

[mlantz]

// User::tree()->get();
Builder::macro('tree', function (string $attribute, string $searchTerm) { //field (parent_id)
// select id,
// parent_id
// from (select * from builder_websites
// order by parent_id, id) builder_websites_sorted,
// (select @pv := 1) initialisation
// where find_in_set(parent_id, @pv)
// and length(@pv := concat(@pv, ',', id))
});

    // User::topParent()->get();
    Builder::macro('topParent', function (string $attribute, string $searchTerm) { //field (parent_id), depth = 10
//     SELECT c.id
//    FROM builder_websites c
//         INNER JOIN (SELECT coalesce(c7.id, c6.id, c5.id, c4.id, c3.id, c2.id, c1.id) id
//                            FROM builder_websites c1
//                                 LEFT JOIN builder_websites c2
//                                           ON c2.id = c1.parent_id
//                                 LEFT JOIN builder_websites c3
//                                           ON c3.id = c2.parent_id
//                                 LEFT JOIN builder_websites c4
//                                           ON c4.id = c3.parent_id
//                                 LEFT JOIN builder_websites c5
//                                           ON c5.id = c4.parent_id
//                                 LEFT JOIN builder_websites c6
//                                           ON c6.id = c5.parent_id
//                                 LEFT JOIN builder_websites c7
//                                           ON c7.id = c6.parent_id
//                            WHERE c1.id = 5) t
//                    ON t.id = c.id;
    });