We have moved our forum to GitHub Discussions. For questions about Phalcon v3/v4/v5 you can visit here and for Phalcon v6 here.
  1. Home
  2. ORM

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.



98.9k

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 }}


13.3k
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.