SELECT
cscart_products_categories.product_id,
GROUP_CONCAT(
IF(
cscart_products_categories.link_type = "M",
CONCAT(
cscart_products_categories.category_id,
"M"
),
cscart_products_categories.category_id
)
) AS category_ids,
product_position_source.position AS position
FROM
cscart_products_categories
INNER JOIN cscart_categories ON cscart_categories.category_id = cscart_products_categories.category_id
AND cscart_categories.storefront_id IN (0, 1)
AND (
cscart_categories.usergroup_ids = ''
OR FIND_IN_SET(
0, cscart_categories.usergroup_ids
)
OR FIND_IN_SET(
1, cscart_categories.usergroup_ids
)
)
AND cscart_categories.status IN ('A', 'H')
LEFT JOIN cscart_products_categories AS product_position_source ON cscart_products_categories.product_id = product_position_source.product_id
AND product_position_source.category_id = 6
WHERE
cscart_products_categories.product_id IN (
64531, 63919, 63727, 64535, 64525, 64524,
64509, 64424, 64100, 64099, 63197,
64508, 64507, 64506, 64505, 64429,
64428, 64427, 64426, 64425, 64393,
64208, 63573, 63572, 61775, 61755,
64145, 64122, 64121, 64120, 61891,
64080, 64079, 63775, 63898, 63897,
63871, 63718, 63301, 63300
)
GROUP BY
cscart_products_categories.product_id