mardi 24 avril 2018

How to create nested subquery with foreach loop in eloquent?

I need to implement a filter product system for my website, so I need to created nested sub queries using eloquent. But these nested sub queries should generated with a loop and I don't know how to that because eloquent using closures to generate sub queries. My query sth like:

SELECT *
FROM `products`
INNER JOIN `product_specifications` ON `product_specifications`.`product_id` = `products`.`id`
WHERE `category_id` = 2
        AND specification_value_id IN (8)
        AND product_id IN 
        (SELECT 
            product_id
        FROM
            product_specifications
        WHERE
                specification_value_id IN (16, 17, 18, 19, 20)
                AND product_id IN 
                (SELECT 
                    product_id
                FROM
                    product_specifications
                WHERE
                    specification_value_id IN (5)
                )
        );

It should be nested with a loop until my array ends, so I also tried to do it with recursive function but no chance! I don't know how to pass a function like closure to eloquent to make a subquery. This my try:

function subQuery($query, $filterArray)
{
    $specValueIds = array_shift($filterArray);
    $query->select('product_id')
        ->from('product_specifications')
        ->whereIn('specification_value_id', $specValueIds);
    if (count($filterArray))
        $query->whereIn('product_id', subQuery($query, $filterArray));
}

$query->whereIn('specification_value_id', array_shift($filterArray[$this->searchKeySpec]))
    ->whereIn('product_id', subQuery($query, $filterArray[$this->searchKeySpec]));



via Chebli Mohamed

Aucun commentaire:

Enregistrer un commentaire