Wrong results using query builder

[dop3]

Hi,
I'm facing a wrong behaviour while using the query builder.
I have to retrieve the actions made by a given user.
I know I can do:

$actionManager   = $this->get('spy_timeline.action_manager');
$subject         = $actionManager->findOrCreateComponent($user);
$timeline = $actionManager->getSubjectActions($subject, array(
    'page' => $page, 
    'max_per_page' => $limit, 
    'paginate' => true));

But, for several reasons and since my app offer the possibility to filter by verb, i want to do i like this:

$qb = $this->get('spy_timeline.query_builder');
$criterias = $qb->logicalAnd(
            $qb->field('type')->equals('subject'),
            $qb->field('model')->equals('It1000\CoreBundle\Entity\User'),
            $qb->field('identifier')->equals($user->getId()));
$qb->setCriterias($criterias);
$qb->setPage($page);
$qb->setMaxPerPage($limit);
$timeline = $qb->execute(array('paginate' => true, 'filter' => true));

The two options described should return the same results, but it's not true. The second one returns more rows (and theese more rows doesn't belong to the given user).

Using the profiler i've found the query that is produced:

SELECT COUNT(*) AS dctrn_count FROM (
    SELECT DISTINCT id6 FROM (
        SELECT s0_.verb AS verb0, s0_.status_current AS status_current1, s0_.status_wanted AS status_wanted2, s0_.duplicate_key AS duplicate_key3, s0_.duplicate_priority AS duplicate_priority4, s0_.created_at AS created_at5, s0_.id AS id6 
            FROM spy_timeline_action s0_ 
            LEFT JOIN spy_timeline_action_component s1_ ON s0_.id = s1_.action_id AND (s1_.type = 'subject') 
            LEFT JOIN spy_timeline_action_component s2_ ON s0_.id = s2_.action_id LEFT JOIN spy_timeline_component s3_ ON s2_.component_id = s3_.id AND (s3_.model = 'It1000\\CoreBundle\\Entity\\User') 
            LEFT JOIN spy_timeline_action_component s4_ ON s0_.id = s4_.action_id LEFT JOIN spy_timeline_component s5_ ON s4_.component_id = s5_.id AND (s5_.identifier = 's:2:\"39\";') 
           WHERE (s1_.type = 'subject' AND s3_.model = 'It1000\\CoreBundle\\Entity\\User' AND s5_.identifier = 's:2:\"39\";') 
            ORDER BY s0_.created_at DESC
    ) dctrn_result
) dctrn_table

I've found that i have different "components" that have identifier with value: 's:2:"39";' but different model. So since the where clause does the where on the different joins it matches also the rows that have the given identifiers but for another model; and, of course, it returns that rows in the results.

Am i missing something? Maybe the problem is in the "same identifier" been created?
Any kind of help will be appreciated.
Thanks and forgive me for my written english.

[stephpy]

Hi,

Indeed, the sql query built is wrong.
It should not create a new join for assertion of identifier. Results cannot be the same :\

This is a tricky bug. :\

I guess there is many some refactoring to do to fix this bug, i cannot assure you i'll fix it soon :. Sorry.

[stephpy]

Issue come from here

IMO we have to make a better system of repartition of criterias, to be able to optimize joins and make better assertions.

[dop3]

Hi stephpy,
thanks for the reply.
Yes, I agree... the repartition of criterias is quite (let me say) ambiguos.
I will try to do some to fix this and, if lucky, i will let you know.
Waiting for yours...
Ciao

[stephpy]

Thanks.

[dop3]

Hi all...
Nothing... I ended up bypassing the critirias stuff... doing some dirty query.
Honestly i don't think I can help...

Sorry about that...

[axzx]

@dop3 @stephpy
Tell you how to get around this problem?

[stephpy]

No, sorry.

TimelineBundle->QueryBuilder looks a tricky solution if you want to do a complex query.
IMO, you should create your own queryBuilder via doctrine ORM (or your provider).

[axzx]

I have querybuilder:

$qb = $this->getDoctrine()->getRepository('TimelineBundle:Action')
          ->createQueryBuilder('a');
$qb
      ->leftJoin('a.actionComponents', 'ac')
      ->leftJoin('ac.component', 'c')
      ->where('c.model = :model')
      ->andWhere('c.identifier = :identifier')
      ->setParameter('model', $objectModel)
      ->setParameter('identifier', serialize((string) $objectId))
;

But the problem is with data hydrator. In view, I don't see components. How join all components?

[stephpy]

You can use the same behaviour than this: https://github.com/stephpy/timeline-bundle/blob/master/Driver/ORM/TimelineManager.php#L45

[axzx]

@stephpy thanks:)

$timeline = $this->get('spy_timeline.result_builder')
          ->fetchResults($qb, $request->query->getInt('page', 1), 5, true, true);

$pagination = $timeline->getIterator();

[stephpy]

You're welcome.