while viewing the tickets in customer login portal api call taking much time to load tickets

[vinayodela]

the internall query using while loading api call is

mysql> SELECT COUNT(d0_.id) AS sclr_0 FROM diamante_ticket d1, diamante_ticket d0 LEFT JOIN diamante_watcherlist d2 ON d0.id = d2.ticketid WHERE d0.reporter_id = 'diamante90' OR d2.user_type = 'diamante_90' -> ;+--------+| sclr_0 |+--------+| 74200 |+--------+1 row in set (7 min 5.71 sec) "

this query is hitting while loading customer portal tickets.

can any one please help us in optimizing this issue in coding level.

[vinayodela]

Hi , we were able to fix this issue by modifying the following piece of code..

Basically we replaced the 'Left-Join' with a 'Join '

Now the ticket listing is loading much faster in the customer portal.

We want to know if this will affect in any other modules or functionalities .... ?!

File : vendor/diamante/desk-bundle/Diamante/DeskBundle/Infrastructure/Ticket/Paging/PortalStrategy.php

Original Function :

public function count(QueryBuilder $qb, $entityName) { $qb->select($qb->expr()->count(DoctrineGenericRepository::SELECT_ALIAS)) ->from($entityName, DoctrineGenericRepository::SELECT_ALIAS) ->leftJoin(DoctrineGenericRepository::SELECT_ALIAS . '.watcherList', 'w') ->orWhere('e.reporter = :user') ->orWhere('w.userType = :user') ->setParameter('user', (string)$this->user); }

Modified Code :

public function count(QueryBuilder $qb, $entityName) { $qb->select($qb->expr()->count(DoctrineGenericRepository::SELECT_ALIAS)) ->from($entityName, DoctrineGenericRepository::SELECT_ALIAS) ->join(DoctrineGenericRepository::SELECT_ALIAS . '.watcherList', 'w') ->orWhere('e.reporter = :user') ->orWhere('w.userType = :user') ->setParameter('user', (string)$this->user); }