QueryBuilder: how to join the same table

Eugene commented on Nov 4, 2024

Hi,

what if I need something like this:

SELECT child.*
    FROM side_menu_items AS child
    LEFT JOIN side_menu_items AS parent ON child.parent_id = parent.id
    WHERE child.is_active = 1
      AND (child.parent_id IS NULL OR parent.is_active = 1)

how to wrap it to QueryBuilder? Or better I use something like this:

$menuItems = Database::rawSQL("
    SELECT child.*
    FROM side_menu_items AS child
    LEFT JOIN side_menu_items AS parent ON child.parent_id = parent.id
    WHERE child.is_active = 1
      AND (child.parent_id IS NULL OR parent.is_active = 1)
")->run();

Sebastian Morales commented on Nov 5, 2024

Grammatically, I think you can use both QueryBuilder to join like joining 2 different tables and raw SQL which will always work. Semantically, if you join the same table on the same field the result might be the same without joining if the field is unique like id. Using different joining fields could get different results.

Eugene commented on Nov 5, 2024

As you can see my table has child-parent relationship - it has the parent_id field that points to the id field of the another record of the same table. That is the reason I want to join the same table on child.parent_id = parent.id (it is 2 different fields)

To do that I have to name each table side_menu_items AS child and side_menu_items AS parent but I found no way to do it using QueryBuilder

Sebastian Morales commented on Nov 5, 2024

QueryBuilder supports table alias when you use an array [$alias => $table] instead of string like this:

    (new koolreport\querybuilder\Query())
        ->from(["parent" => "orders"])
        ->join(["child" => "orders"],'parent.parent_id','=','child.id') 
        ...

Pls try it and let us know your result.

KoolReport's Forum

QueryBuilder: how to join the same table #3367

Build Your Excellent Data Report