Load Entities from / Execute Stored Procedure/Function

[DemoXin]

I'm looking for a way to do a couple of things via my MySQL database's Stored Procedures/Functions.

My needs:

• One of my procedures is a "refresh" type procedure that doesn't need to return anything explicitly, so I would just need to call it and move on.
• I would ideally like to get a SelectBuilder that returns a list of MyEntity that results from a stored procedure as well because

While I'm aware that there I'm able to execute raw SQL (even though the documentation recommends against this), I'd rather have my code be within the recommended code practices. Additionally, using EntityManager->queryExecutor->execute() would allow me to run my stored procedure, but doesn't seem to be an option for setting up a SelectBuilder. I'd like to create a dashlet that displays the list of MyEntity that my other stored procedure returns.

Any advice on solving these problems would be greatly appreciated!

[yuri]

I think it's not possible via SelectBuilder. You can create an Entity list (or collection) from data returned by the 'execute' method.

[DemoXin]

Thanks for the response!

You can create an Entity list (or collection) from data returned by the 'execute' method.

Could you point me to any documentation to help with the either of those options? I've seen collections mentioned in other threads, but only front-end usage and nothing about creating them on the PHP side.

As for the first part (the refresh/rebuild procedure), I think I have figured out a solution to handle the refresh/rebuild procedure purely on the database side.

[yuri]

The simplest method:

$this->entityManager->getRDBRepository($entityType)->findBySql($sql); 

Or:

$sth = $this->entityManager->getQueryExecutor()->execute($sql);
$rows = $sth->fetchAll(PDO::FETCH_ASSOC);
$collection = $this->entityManager->getCollectionFactory()->create($entityType, $rows); 

[DemoXin]

When I'm building my SQL statements/procedures, should my result just be full rows from the relevant table?

For instance, if I want to load Opportunity Entities, would this be loaded correctly?

SELECT * FROM `opportunity` WHERE <condition>; 

Or do I need to do any kind of JOINs to fully realize those Entities?

[yuri]

All columns should have a camalCase alias. opportunity.account_id AS `accountId`