CODEWITHSUNDEEP
phpmysqlsymfonydoctrine-orm
allejo
allejo

Reputation: 2203

Using nested queries and many to many relationships in Doctrine's QueryBuilder

So I'm having a bit of trouble thinking of how to approach this using a query builder. Currently, I have three objects that are the following:

HelpRequest
    id
    ...
    status

Filter
    id
    name
    statuses ->  ManyToMany(targetEntity="Status")

Status
    id
    name

A filter can have multiple statuses so there is a table that is keeping track what statuses are part of a specific filter.

Sample Data

help_requests
---
| id | content | status |
| 1  | hello   | 3      |

filters
---
| id | name     |
| 1  | Active   |
| 1  | Inactive |

statuses
---
| id | name                  |
| 1  | Open                  |
| 2  | Closed                |
| 3  | Waiting User Response |

status_filter
---
| status_id | filter_id |
| 1         | 1         |
| 3         | 1         |
| 2         | 2         |

The status_filter table is automatically generated from a ManyToMany relationship in doctrine between a Status object and a Filter object.

Based on the given information, I've written this SQL query but now I'm having troubles writing this with QueryBuilder.

SELECT * FROM help_requests WHERE status IN (SELECT status_id FROM status_filter WHERE filter_id = 1)

If there's any more information I can give, let me know. I've read multiple questions on SO and have tried a number of things but I can't seem to get it right. I'm aware I could just hard coded that query but I'd like the experience using QueryBuilder

Thanks for the help!

Update

In the end, since I couldn't get it to work with QueryBuilder and I didn't want to create a new entity solely to map two other entities together, I decided to use createQuery() instead and this is what I came up with:

SELECT
    hr
FROM
    HelpRequest hr
WHERE
    hr.status
IN (
    SELECT
        s.id
    FROM
        Filter f
    JOIN
        f.statuses s
    WHERE
        f.name = :name
    )

Thank you everyone for the help.

Upvotes: 1

Views: 925

Answers (2)

Cristian Bujoreanu
Cristian Bujoreanu

Reputation: 1167

Try this query, and put is in your HelpRequestsRepository class:

$subquery = $this->->select('st.status_id')
    ->from('/path/to/StatusFilter', 'st')
    ->where('st.filter_id = 1');

$query = $this->createQueryBuilder('hr')
    ->select('*')
    ->where('hr.status IN (' . $subquery->getDQL() . ')')
    ->getQuery();

Upvotes: 2

Matteo
Matteo

Reputation: 39390

Try this approach in the HelpRequestsRepository class:

    $qb = $this->createQueryBuilder('hr');
    $qb->select("hr");
    $qb->join("::Status","s",Expr\Join::INNER_JOIN, "hr.status=s" );
    $qb->join("::Filter","f",Expr\Join::INNER_JOIN, "s.filters=f" );
    $qb->where("f.name = :name");
    $qb->setParameter('name', $nameOfTheFilterToBeFound)

Hope this help

Upvotes: 1

Related Questions