create report from an SQL query?

[jamie]

HI,

I need to create a report or something I can use inside a layout from an SQL query and maybe PHP

At this point, I want to just list all records linked to a primary_account_id, but in the future, I'd love to greatly expand on this

This, for example, is the current query I want to use this time, though the option to use more advanced queries in the future would be a great help to me

select * FROM work_record wr where wr.account_id IN (SELECT id FROM account WHERE primary_account_id = '64ad54e22412eccc8');

[item]

Hi,
have you advancedPack ?
if yes, there are some sample like LeadsByLastActivity .... but you need "some level skill" because it's not easy to replicate/understand (for me i say).

[jamie]

Hi,
have you advancedPack ?
if yes, there are some sample like LeadsByLastActivity .... but you need "some level skill" because it's not easy to replicate/understand (for me i say).

Hi item

Yes, we do have the advancedPack, and I have been working as a web developer for years.

Any got-ya's you'd warn me of before my adventure?

[rabii]

This is how i would do that

$workRecords = $this->entityManager
                ->getRDBRepositoryByClass(WorkRecord::class)
                ->where([
                    'accountId' => '64ad54e22412eccc8',
                ])
                ->find(); 

Espocrm ORM and SelectBuilder are your friends here

https://docs.espocrm.com/development/orm/

https://docs.espocrm.com/development/select-builder/

[jamie]

This is how i would do that

$workRecords = $this->entityManager
->getRDBRepositoryByClass(WorkRecord::class)
->where([
'accountId' => '64ad54e22412eccc8',
])
->find(); 

rabii that looks so clean and integrated! Just where would you do it so i can put it into my report so i can put that at the bottom of a layout?

[rabii]

can you explain more what you want to achieve ? i am not sure i fully understand. what do you mean by bottom of a layout ?

[jamie]

rabii i am essentially just wanting to use some custom SQL in a report

[rabii]

what do you mean by report mate ? is it report from advanced pack ?

[jamie]

rabii yes, at the end of the day, i want to use SQL to determine which list is used for the display will be so much more powerful and simple than making up relationships and pivot tables

[rabii]

in this case best way to do it is to create you own custom complex express which are mysql that you can use in report (group by field etc)

see

https://docs.espocrm.com/development/metadata/app-complex-expression/

https://docs.espocrm.com/user-guide/reports/#group-by

https://docs.espocrm.com/user-guide/complex-expressions/

[jamie]

yes indeed, that looks like exactly what i am looking for

[jamie]

rabii thanks

[rabii]

you are welcome