Magento 2: Get all bundle products

Question

So I have to create a report (SQL Script) which has all the bundle products and all its customizable options. I can get list of all the bundle products using this query.

select entity_id, type_id, sku, has_options from catalog_product_entity where type_id='bundle';

In catalog_product_entity, I assume that entity_id is the primary key.

Now I want all the options associated with bundle product.

which tables should I refer to? I know catalog_product_bundle_option and catalog_product_bundle_option_value can give me option title but when I try to join using parent_id with entity_id it doesn't return anything.

Can anyone explain table structure to me or which tables should I refer to then that will be great.

Answer 1

Technically, you should be able to join catalog_product_entity and catalog_product_bundle_option_value on:

catalog_product_entity.entity_id = catalog_product_bundle_option_value.parent_product_id

However, if you look at the table definition for catalog_product_bundle_option_value, parent_product_id is not a key and no database-defined constraint binds it to catalog_product_entity.entity_id. The relationship is maintained outside of the database, meaning that you have to know and trust Magento's logic.

As an alternative, you should also be able to join catalog_product_entity and catalog_product_bundle_option on:

catalog_product_entity.entity_id = catalog_product_bundle_option.parent_id

And additionally be able to join catalog_product_bundle_option and catalog_product_bundle_option_value on:

catlog_product_bundle_option.option_id = catalog_product_bundle_option_value.option_id

Nesting these two will allow you to create a query that effectively joins catalog_product_entity and catalog_product_bundle_option_value on actual keys.