magento-ellison-preorder-products.sql

set @lifecycle_attr_id               = 144;
set @isorderable_attr_id             = 151;
set @ispreorderable_attr_id          = 186;
set @lifecycle_attr_value_active     = 23;
set @lifecycle_attr_value_prerelease = 155;


select
    cpe.entity_id product_id,
    cpe.sku
from catalog_product_entity cpe
inner join catalog_product_entity_int attr_lifecycle
    on cpe.entity_id = attr_lifecycle.entity_id
    and attr_lifecycle.attribute_id = @lifecycle_attr_id
inner join catalog_product_entity_int attr_isorderable
    on cpe.entity_id = attr_isorderable.entity_id
    and attr_isorderable.attribute_id = @isorderable_attr_id
inner join catalog_product_entity_int attr_ispreorderable
    on cpe.entity_id = attr_ispreorderable.entity_id
    and attr_ispreorderable.attribute_id = @ispreorderable_attr_id
where
    (
        attr_lifecycle.value = @lifecycle_attr_value_prerelease
        and attr_isorderable.value = 1
    ) OR (
        attr_lifecycle.value = @lifecycle_attr_value_active
        and attr_isorderable.value = 1
        and attr_ispreorderable.value = 1
    )
;