ManyToMany values as columns

[stephanvierkant]

Let's assume these are my tables:

Product

id	description
1	Small car
2	Big car

Property

id	property
1	color
2	size

ProductProperty:

id	product_id	property_id	value
1	1	1	red
2	1	2	S
3	2	2	XL

Expected output:

id	description	color	size
1	Small car	red	S
2	Big car		XL

The idea behind this is I have Products with many different Product Types (for example: Cars, Phones, etc.). Instead of having multiple tables with different columns, I've got one Product table and users can add extra properties to each ProductType. A DataTable instance will have products with a specific Product Type, since a table with both cars and phones isn't very useful.

What's the best way to achieve a product list with those extra properties as a column? Sorting and searching should be possible.

A quick 'n dirty solution would be something like this:

foreach ($properties as $property) {
    $this->columnBuilder->add(
        $property->id,
        Column::class,
        [
            'dql' => "(SELECT {p}.value FROM ProductProperty {p} WHERE {p}.product = product.id AND {p}.property = " . $property->id . ")",
            'title' => $property->description)
        ]
    );
}

But that would not allow searching and sorting.

Any ideas how to solve this problem?

[Seb33300]

I encountered the same issue when I wanted to display enabled products of a category which can be associated to enabled and disabled products...

It is not possible to do this at this time.

I think we should work to add a join_conditions option to the Column similar to the join_type option (https://github.com/stwe/DatatablesBundle/blob/master/Resources/doc/columns.md#options).
In this option we could restrict the join clause using the WITH clause.

Doctrine2 doc for details, the resulting query should look like:

SELECT c FROM Category c LEFT JOIN c.products p WITH p.enabled = 1