1

I have follow database structure:


List item

trips (ManyToMany to tags over table trips_tags)
+----+----------------+
| id |      name      |
+----+----------------+
|  1 | Czech Republic |
|  2 | Germany        |
|  3 | Poland         |
+----+----------------+

tags
+----+-----------+
| id |   name    |
+----+-----------+
|  1 | Mountains |
|  2 | Beach     |
|  3 | City      |
+----+-----------+

trips_tags
+----------+---------+
| trips_id | tags_id |
+----------+---------+
|        1 |       1 |
|        1 |       2 |
|        3 |       1 |
+----------+---------+

I need to select trips which has all tags I specify.

  • Need trips for tags Mountains as Beach I get only Czech Republic.
  • Need trips only for tag Mountains, get Czech Republic and Poland

I wrote a simple SQL 

SELECT trip.name, trip.id
FROM trips AS trip
WHERE (
    SELECT COUNT(trip_tag.tags_id) 
    FROM trips_tags AS trip_tag 
    WHERE trip_tag.tags_id IN (1, 2) 
      AND trip_tag.trips_id = trip.id
) = numberOfTags`

Now I have a problem to write this SQL in DQL. Can anyone help?

Thank you

Mickaël Leger
  • 3,426
  • 2
  • 17
  • 36
iwory
  • 23
  • 4
  • In what framework are you working? Normally if you work with Doctrine you make Entity Classes and you use Doctrine PHP Comments and make relationships in these classes. You should never write a naked query like that. Everything should be handled in Doctrine itself. – Kerel Jun 04 '18 at 14:39
  • I'm working in Nette FW. I have no problem with design of entities, but I didn't know how to create a query in DQL :) – iwory Jun 04 '18 at 15:54
  • Fair enough, it wasn't clear to me whether you got that part or not. – Kerel Jun 04 '18 at 15:58

1 Answers1

1

It looks like you have many to many relationship between trips and tags, Its better to go with doctrine way and define your entites and relate them as many to many like 

class Trip
{
    // ...

    /**
     * @ManyToMany(targetEntity="Tag", inversedBy="trips")
     * @JoinTable(name="trip_tag")
     */
    private $tags;

    public function __construct() {
        $this->tag s= new \Doctrine\Common\Collections\ArrayCollection();
    }

    // ...
}

/** @Entity */
class Tag
{
    // ...
    /**
     * @ManyToMany(targetEntity="Trip", mappedBy="tags")
     */
    private $trips;

    public function __construct() {
        $this->trips = new \Doctrine\Common\Collections\ArrayCollection();
    }

    // ...
}

And then build your DQL with some aggregation

$tagIds = [1,2];
$qb = $this->createQueryBuilder('trip');
$qb ->addSelect('COUNT(tags.id) AS total_tags')
    ->leftJoin('trip.tags', 'tags')
    ->add('where', $qb->expr()->in('tags', $tagIds))
    ->groupBy('trip.id')
    ->having('total_tags = @numberOfTags')
    ->getQuery()
    ->getResult();

Many-To-Many, Bidirectional

Doctrine2 get object without relations

Symfony2 - Doctrine2 QueryBuilder WHERE IN ManyToMany field

M Khalid Junaid
  • 63,861
  • 10
  • 90
  • 118
  • Yes, it's looks good and works it but I need to get array of Trips entities? Now I get only array of total_tags values. It is possible? – iwory Jun 04 '18 at 16:00
  • @iwory you can add `->select('trip')` in your query to get collection of trips – M Khalid Junaid Jun 05 '18 at 06:14
  • Yes, we are approaching :) Now I get array where every item has two keys -> Trip entity and total_tags value. It's possible to remove total_tags key and get only clear array of Trips? Thx a lot – iwory Jun 05 '18 at 08:12
  • @iwory I guess you need to transform it manually , not sure about this. – M Khalid Junaid Jun 05 '18 at 08:42