Doctrine join query to get all record satisfies count greater than 1

Question

I tried with normal sql query

SELECT activity_shares.id FROM `activity_shares` 
INNER JOIN (SELECT `activity_id` FROM `activity_shares`
GROUP BY `activity_id`
HAVING  COUNT(`activity_id`) > 1 ) dup ON activity_shares.activity_id = dup.activity_id

Which gives me record id say 10 and 11

But same query I tried to do in Doctrine query builder,

 $qb3=$this->getEntityManager()->createQueryBuilder('c')
           ->add('select','c.id')
           ->add('from','MyBundleDataBundle:ActivityShare c')
           ->innerJoin('c.activity', 'ca')
         //  ->andWhere('ca.id = c.activity')
           ->groupBy('ca.id')
          ->having('count(ca.id)>1');

Edited:

        $query3=$qb3->getQuery();
        $query3->getResult();

Generated SQL is:

SELECT a0_.id AS id0 FROM activity_shares a0_ 
INNER JOIN activities a1_ ON a0_.activity_id = a1_.id 
GROUP BY a1_.id HAVING count(a1_.id) > 1

Gives only 1 record that is 10.I want to get both.I'm not getting idea where I went wrong.Any idea?

My tables structure is:

ActivityShare
    +-----+---------+-----+---
    | Id  |activity |Share| etc...
    +-----+---------+-----+----
    | 1   | 1       |1    |
    +-----+---------+-----+---
    | 2   | 1       | 2   |
    +-----+---------+-----+---

Activity is foreign key to Activity table. I want to get Id's 1 and 2

You must have a line like `$query->getResult()` to retrieve the entities. If you have something `$query->getSingleResult()` you'll only get one row. — Bram Gerritsen, Mar 04 '13 at 09:21
That is looking good. Try `$query3->getSql()` to get the SQL string and execute that directly to your database using a MySql client. See if you are getting the same result. — Bram Gerritsen, Mar 04 '13 at 09:30
ya.It is giving only on record.Result I'm getting proper Id of one record .But I want both records Id.The above normal sql query I tried in MySql client.It was giving me two records Id. — stefun, Mar 04 '13 at 09:37
SQL string I'm getting is `SELECT a0_.id AS id0 FROM activity_shares a0_ INNER JOIN activities a1_ ON a0_.activity_id = a1_.id GROUP BY a1_.id HAVING count(a1_.id) > 1` — stefun, Mar 04 '13 at 09:39
You mean you are getting different results executing the SQL string generated by Doctrine? If so spot the differences in the SQL or put the generated SQL in your question. — Bram Gerritsen, Mar 04 '13 at 09:40
Clearly your generated SQL doesn't contain the subquery which is in your manually typed SQL. I don't know how to do this with the queryBuilder. I'd suggest you to google `doctrine queryBuilder subquery`, or wait for someone who nows the queryBuilder by heart. — Bram Gerritsen, Mar 04 '13 at 09:49

score 0 · Answer 1 · answered Mar 05 '13 at 04:01

Simplified SQL

first of all let me simplify that query so it gives the same result :

SELECT id FROM `activity_shares` 
GROUP BY `id`
HAVING  COUNT(`activity_id`) > 1

Docrtrine QueryBuilder

If you store the id of the activty in the table like you sql suggests:

You can use the simplified SQL to build a query:

$results =$this->getEntityManager()->createQueryBuilder('c')
           ->add('select','c.id')
           ->add('from','MyBundleDataBundle:ActivityShare c')
           ->groupBy('c.id')
           ->having('count(c.activity)>1');
           ->getResult();

If you are using association tables ( Doctrine logic)

here you will have to use join but the count may be tricky

Solution 1

use the associative table like an entitiy ( as i see it you only need the id) Let's say the table name is activityshare_activity

it will have two fields activity_id and activityshare_id, if you find a way to add a new column id to that table and make it Autoincrement + Primary the rest is easy :

the new entity being called ActivityShareActivity

$results =$this->getEntityManager()->createQueryBuilder('c')
           ->add('select','c.activityshare_id')
           ->add('from','MyBundleDataBundle:ActivityShareActivity c')
           ->groupBy('c.activityshare_id')
           ->having('count(c.activity_id)>1');
           ->getResult();

the steps to add the new identification column to make it compatible with doctrine (you need to do this once):

add the column (INT , NOT NULL) don' t put the autoincrement yet

ALTER TABLE tableName ADD id INT NOT NULL
Populate the column using a php loop like for
Modify the column to be autoincrement

ALTER TABLE tableName MODIFY id INT NOT NULL AUTO_INCREMENT

Solution2

The correction to your query

$result=$this->getEntityManager()->createQueryBuilder()
           ->select('c.id')
           ->from('MyBundleDataBundle:ActivityShare', 'c')
           ->innerJoin('c.activity', 'ca')
           ->groupBy('c.id') //note: it's c.id not ca.id
           ->having('count(ca.id)>1')
           ->getResult();

I posted this one last because i am not 100% sure of the output of having+ count but it should word just fine :)

Thanks for your answer.Your simplified query will give only one record Id.Say there are two records with Id 1 and 2 which satisfies condition count>1.It will give me only first record Id 1.I want both.And I guess `groupBy` should be on activity_id not activity_share tables Id. — stefun, Mar 05 '13 at 05:50

score 0 · Accepted Answer · answered Mar 05 '13 at 09:56

Thanks for your answers.I finally managed to get answer

My Doctrine query is:

$subquery=$this->getEntityManager()->createQueryBuilder('as')
      ->add('select','a.id')
      ->add('from','MyBundleDataBundle:ActivityShare as')
      ->innerJoin('as.activity', 'a')
      ->groupBy('a.id')
      ->having('count(a.id)>1');



 $query=$this->getEntityManager()->createQueryBuilder('c')
      ->add('select','c.id')
      ->add('from','ChowzterDataBundle:ActivityShare c')
      ->innerJoin('c.activity', 'ca');

    $query->andWhere($query->expr()->in('ca.id', $subquery->getDql()))
      ;

    $result = $query->getQuery();
    print_r($result->getResult());

And SQL looks like:

SELECT a0_.id AS id0 FROM activity_shares a0_ INNER JOIN activities a1_ ON a0_.activity_id = a1_.id WHERE a1_.id IN (SELECT a2_.id FROM activity_shares a3_ INNER JOIN activities a2_ ON a3_.activity_id = a2_.id GROUP BY a2_.id HAVING count(a2_.id) > 1

Doctrine join query to get all record satisfies count greater than 1

2 Answers2

Simplified SQL

Docrtrine QueryBuilder

If you store the id of the activty in the table like you sql suggests:

If you are using association tables ( Doctrine logic)