I designed a model for let's a list of products, this model provides pre-computed fields on prices.
The model is resulting from a raw table of products and there is another relationship table made of tags (such as product_id, tag_id, tag_name so I do not have to join another table to get the name of the tags).
I'd like to add a filter which allows me to filter product based on their tags.
For example "retrieve all products which are tagged "electronic".
For now, my question in the dashboard is nothing more than the raw model because everything is pre-aggregated / computed.
If I try to join the tags table within my question, I get as many rows as I've product tags which results in products showing multiple times (if I've 5 tags for a single product, I get 5 rows), which the expected "SQL" way, however I'm wondering how I could "reduce" the result to a single product.
I do not need to display the tag in the table visualisation I just need to filter.
I've been stuck with this but it seems to me a very common problem which may applies to any tagged / categorized resources.
Use group concat to have the list of tags for each product (one comma separated list for each product). Then have a hidden question (nor results, then hide when no data) that includes all the tags. Then set the filter to have an include.
There's probably some fiddling about to do, but that's how I'd start.
@JohnLecointe if I'm understanding correctly, I believe what you could do is join the tags to the model in the GUI question builder, deselect the fields in the "tags" table that you don't want in the table and then group on the product ID to reduce your result set back to a single row. You'd have to do a min() or max() on the other fields in your product table.
This will result in the table visualization returning the unique results but it retains the joined relationship to the tags table. because of this, when you place the resulting question on a dashboard you can then filter on the tag.