Incompatible with joins

[flipvrijn]

When the query builder is used in combination with joins, the parent_id (and probably others) column is ambiguous:

"SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'parent_id' in where clause is ambiguous (SQL: select count(*) as aggregate from (select '1' as row from products left join shops on shops.id = products.shop_id left join categories on categories.id = products.category_id where parent_id is null) AS count_row_table)"

using the following query:

$products = \Product::whereIsRoot()
        ->leftJoin('shops', 'shops.id', '=', 'products.shop_id')
        ->leftJoin('categories', 'categories.id', '=', 'products.category_id')
        ->select(['products.id', 'shops.name as shop', 'products.image', 'products.brand', 'products.name',
            'products.unit', 'products.similarity', 'categories.name as category'
        ]);

For myself I quickly fixed it in QueryBuilder.php by altering the whereIsRoot() method to:

public function whereIsRoot()
{
    $this->query->whereNull($this->model->getTable().'.'.$this->model->getParentIdName());

    return $this;
}

But I think there are multiple places in the query builder that are incompatible with joins (still have to encounter them).

[lazychaser]

Why products are nodes?

[flipvrijn]

There is more to products than simply being a product, since this table contains products from different shops. By representing it as nodes (or a forest of trees) equivalent products from different shops can be 'merged' in one tree while still retaining all the information from each individual shop. It is used for a comparison app.