Solved thread

This post is marked as solved. If you think the information contained on this thread must be part of the official documentation, please contribute submitting a pull request to its repository.

Get Sum of Related Model Column

Hi!

I'm trying to find out how to get the Sum of a field from a related table in Phalcon.

I have two tables in the DB: projects (key id) and project_orders (key id, fkey project_id).

I have set up the relationship within the initialize function and I can retrieve the project orders information. However, what I want to do, in my view is:

{{ projects.project_orders.sumCosts }} where costs is a column in the table project_orders i.e. the SQL would be akin to:

SELECT projects.*, SUM(project_orders.costs) FROM projects inner join project_orders on projects.id = project_orders.project_id

Any help would be greatly appreciated.



83.2k

This way:

$this->view->result = $this->db->fetchAll("SELECT projects.*, SUM(project_orders.costs) AS sumCosts FROM projects inner join project_orders on projects.id = project_orders.project_id");
{{ result.sumCosts }}


4.6k
Accepted
answer
edited Sep '14

Thanks @Phalcon.

After I posted this message, I continued my reading on the principal that I couldn't do what I wanted to do i.e. use the Models and the ::find() functions, and based on your answer thats what you're saying too.

As such I've come up with the following, which completes the query using the querybuilder:

$projects = $this->modelsManager->createBuilder()
            ->columns(array(
                "Project.id",
                "Project.title",
                "IFNULL(SUM(ProjectOrders.design_costs + ProjectOrders.design_expenses + ProjectOrders.design_mileage + ProjectOrders.post_design_costs + ProjectOrders.post_design_expenses + ProjectOrders.post_design_mileage + ProjectOrders.equipment_costs + ProjectOrders.installation_costs), 0) as sumCosts",
                "CONCAT(Director.forename, ' ', Director.surname) as director",
                "CONCAT(Manager.forename, ' ', Manager.surname) as manager",
                "Site.telephone",
                "Site.fax",
                "Address.city",
                "Client.name",
                "CONCAT(Contact.forename, ' ', Contact.surname) as contact",
                "0 as percentage"
                )
            )
            ->from(array("Project" => "\\SPARCS\\PMS\\Models\\PmsProjects"))
            ->join("\\myApp\\PMS\\Models\\PmsProjectOrders", "ProjectOrders.project_id = Project.id", "ProjectOrders", "LEFT")
            ->join("\\myApp\\CRM\\Models\\CrmSites", "Site.id = Project.site_id", "Site")
            ->join("\\myApp\\CRM\\Models\\CrmClients", "Site.client_id = Client.id", "Client")
            ->join("\\myApp\\CRM\\Models\\CrmContacts", "Contact.id = Project.contact_id", "Contact")
            ->join("\\myApp\\common\\Models\\CoreAddresses", "Address.id = Site.address_id", "Address")
            ->join("\\myApp\\common\\Models\\SystemUsers", "Director.id = Project.director_id", "Director", "LEFT")
            ->join("\\myApp\\common\\Models\\SystemUsers", "Manager.id = Project.manager_id", "Manager", "LEFT")
            ->groupBy("Project.id")
            ->orderBy("Project.id DESC")
            ->getQuery()
            ->execute();

The question I now have is this: is there a better way?

The reason I'm asking is: 1. I have to write out each join individually with namespaces 2. I can no longer use: {{ projects.contact.forename }} to get to the contacts forename

At present this is getting me along, but i feel like there should be an easier way. I'm open to suggestions and comments.

Many thanks in advance.

UPDATE: After some further reading and some testing, this appears to be the way to do this.