Repository which loading all account activities

I need to load all Activities of Account in SQL (especially date start of activity), how can I do it? I tried joins, but relations in database are in table with generated key, so it won’t work everywhere.
Activities which I want to load: calls, emails, events

Topic

[Yevhen ShyshkinModerator]

Hello, Dawid.

The easiest way to do that is to call ActivityList repository and request an appropriate query builder:

PHP

/** @var ActivityListRepository $activityListRepository */ $activityListRepository = $this->get('oro_entity.doctrine_helper')->getEntityRepository(ActivityList::class); $callsQueryBuilder = $activityListRepository->getActivityListQueryBuilder(Account::class, 57, [Call::class]); $sql = $callsQueryBuilder->getQuery()->getSQL();

1 2 3 4

/** @var ActivityListRepository $activityListRepository */ $activityListRepository = $this->get('oro_entity.doctrine_helper')->getEntityRepository(ActivityList::class); $callsQueryBuilder = $activityListRepository->getActivityListQueryBuilder(Account::class, 57, [Call::class]); $sql = $callsQueryBuilder->getQuery()->getSQL();

As you can see you may pass multiple activity classes. In the end application will generate an SQL like that:

MySQL

SELECT o0_.serialized_data AS serialized_data_0, o0_.id AS id_1, o0_.verb AS verb_2, o0_.subject AS subject_3, o0_.description AS description_4, o0_.related_activity_class AS related_activity_class_5, o0_.related_activity_id AS related_activity_id_6, o0_.created_at AS created_at_7, o0_.updated_at AS updated_at_8, o0_.user_owner_id AS user_owner_id_9, o0_.user_editor_id AS user_editor_id_10, o0_.organization_id AS organization_id_11 FROM oro_activity_list o0_ LEFT JOIN oro_rel_c3990ba6b28b6f38e2d624 o2_ ON o0_.id = o2_.activitylist_id LEFT JOIN orocrm_account o1_ ON o1_.id = o2_.account_id LEFT JOIN oro_activity_owner o3_ ON o0_.id = o3_.activity_id WHERE o1_.id = 57 AND o0_.related_activity_class IN ('Oro\Bundle\CallBundle\Entity\Call')

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

SELECT     o0_.serialized_data AS serialized_data_0,     o0_.id AS id_1,     o0_.verb AS verb_2,     o0_.subject AS subject_3,     o0_.description AS description_4,     o0_.related_activity_class AS related_activity_class_5,     o0_.related_activity_id AS related_activity_id_6,     o0_.created_at AS created_at_7,     o0_.updated_at AS updated_at_8,     o0_.user_owner_id AS user_owner_id_9,     o0_.user_editor_id AS user_editor_id_10,     o0_.organization_id AS organization_id_11 FROM oro_activity_list o0_ LEFT JOIN oro_rel_c3990ba6b28b6f38e2d624 o2_ ON o0_.id = o2_.activitylist_id LEFT JOIN orocrm_account o1_ ON o1_.id = o2_.account_id LEFT JOIN oro_activity_owner o3_ ON o0_.id = o3_.activity_id WHERE o1_.id = 57 AND o0_.related_activity_class IN ('Oro\Bundle\CallBundle\Entity\Call')

There is created_at field at the selected data – I guess this is what you need.

[dawid.zarodaParticipant]

Hello Yevhen,

thank you for your response, I will check that and maybe it will be enough :)

Also I have another question: what if I want to load this date for all accounts in database and filter results by my conditions? Performance is not very important, because this code will run only at night, one time.

[Yevhen ShyshkinModerator]

There are two solutions.

1) Pass all account IDs there – looks like method can work with array of IDs as well.

2) Create your own Query Builder inspired by the code at ActivityListRepository, but replace condition ‘accountId = X’ with ‘accountId IS NOT NULL’ – I guess it should be enough to get all required activities.

[dawid.zarodaParticipant]

Thank you for your help – I will try this solution.

Regards

[Author]

Replies