dynamic filter query with X num of relation attributes - Doctrine Symfony2

Question

I am stuck for 12 hours now. My problem is to build query for filtering based on XY number of parameters.

Background:

Item: An item(s) can have many attributes, each attribute is related to only one item.

Attribute[id, type_id, value_id, item_id]:

type (relation to AttributeType[id, name]) ex.: operating.system (column "name" of AttributeType)
value (relation to AttributeValue[id, value]) ex.: Windows Phone 8 (column "value" of AttributeValue)

Scenario:

At the page, where I list items, there is filter for attributes

Operating System
- {checkbox name="a[type.id][0]" value="[value.id]"} Windows Phone 8
- {checkbox name="a[type.id][1]" value="[value.id]"} Android
Cpu Frequncy
- {select with options name="a[type.id][0]"}{option value="a[type.id][value.id]" }{/select}

When user submits the search form, I loop throug attributes and I dynamically add andWhere(). And query looks like:

select .... from items i left join attributes a on i.id = a.item_id WHERE
a.type_id=typeid and a.value_id=valueid

When i do this with only 1 param its ok but with 2 params

a.type_id=typeid AND a.value_id=valueid AND a.type_id=typeid AND a.value_id=valueid.....

result is 0

    a.type_id=typeid AND a.value_id=valueid OR a.type_id=typeid AND a.value_id=valueid....

returns every item where least 1 param matched

What I need, is to build query that results items that have all parameters matching. Like a subquery loop, match first search attribute than from that result match second, third,... and finally get result of items.

Thank you

----- EDIT ------

I have proceed, i am creating subqueries like

$sAttrTypes = (isset($_GET["a"]) ? $_GET["a"] : array());

        $qb = $this->em()->createQueryBuilder("p");
        $qb->select(array("i", "a"))->from("ErikOfferBundle:Item", "i")->leftJoin("i.attributes", "a");

        $i = 0;
        $subquery_0 = false;
        foreach ($sAttrTypes as $aTypeId => $aValues) {
            if (!$aValues) {
                continue;
            }

            $ids = false;

            foreach ($aValues as $value) {
                $ids[] = $value[0];
            }

            $query = $this->em()->createQueryBuilder("p_" . $i);
            ${"subquery_" . $i} = $query->select(array("i$i", "a$i"))
                    ->from("ErikOfferBundle:Item", "i$i")
                    ->leftJoin("i$i.attributes", "a$i");

            if (count($ids) < 2) {
                ${"subquery_" . $i}->where($qb->expr()->eq("a$i.value", $ids[0]));
            } else {
                ${"subquery_" . $i}->where($qb->expr()->in("a$i.value", $ids));
            }

            if ($i > 0) {
                ${"subquery_" . ($i - 1)}->andWhere($qb->expr()->in("i" . ($i - 1) . ".id", ${"subquery_" . $i}->getDql()));
            }

            echo "subquery_" . $i. ": " .${"subquery_" . $i}."<br/>";
            $i++;
        }

        $qb->where($qb->expr()->in("i.id", ${"subquery_0"}->getDql()));
        echo "Final query: " . $qb->getDql();
        $items = $qb->getQuery()->getResult();

that creates query:

subquery_0: SELECT i0, a0 FROM ErikOfferBundle:Item i0 LEFT JOIN i0.attributes a0 WHERE a0.value = 1
subquery_1: SELECT i1, a1 FROM ErikOfferBundle:Item i1 LEFT JOIN i1.attributes a1 WHERE a1.value = 4
Final query: SELECT i, a FROM ErikOfferBundle:Item i LEFT JOIN i.attributes a WHERE i.id IN(SELECT i0, a0 FROM ErikOfferBundle:Item i0 LEFT JOIN i0.attributes a0 WHERE a0.value = 1 AND i0.id IN(SELECT i1, a1 FROM ErikOfferBundle:Item i1 LEFT JOIN i1.attributes a1 WHERE a1.value = 4))

And it gets exception "[Syntax Error] line 0, col 88: Error: Expected Doctrine\ORM\Query\Lexer::T_FROM, got ','"

col 88 is "i0, a0" in first subquery

E.K. E.K. · Accepted Answer · 2015-03-10T19:36:53

Solved with selecting only 1 table in subqueries

final draft code:

$sAttrTypes = (isset($_GET["a"]) ? $_GET["a"] : array());

$i = 0;
$subquery_0 = false;
    foreach ($sAttrTypes as $aValues) {

        if (!$aValues[1]) {
            continue;
        }

        if (count($aValues) == 1 && ($aValues[1] == 0 || $aValues[1] == "")) {
            continue;
        }

        $query = $this->em()->createQueryBuilder();
        ${"subquery_" . $i} = $query->select(array("i$i"))
                ->from("ErikOfferBundle:Item", "i$i")
                ->leftJoin("i$i.attributes", "a$i")
                ->where((count($aValues) == 1 ?
                        $query->expr()->eq("a$i.value", $aValues[1]) :
                        $query->expr()->in("a$i.value", $aValues)));
        if ($i > 0) {
            ${"subquery_" . (0)}->andWhere($query->expr()->in("i0.id", ${"subquery_" . $i}->getDQL()));
        }
        $i++;
    }


$qb = $this->em()->createQueryBuilder();
$qb->select(array("i", "a"))->from("ErikOfferBundle:Item", "i")->leftJoin("i.attributes", "a");
    if ($subquery_0) {
        $qb->andWhere($qb->expr()->in("i.id", $subquery_0->getDQL()));
    }
$items = $qb->getQuery()->getResult();

dynamic filter query with X num of relation attributes - Doctrine Symfony2

1 Answers