Support the ongoing development of Laravel.io →
Database Eloquent

Hi!

Introduction

I'm currently in the making of a sales report system. Customers will be able to build their own hierarchies using projects, groups and users. You should be able to add groups and users and groups within groups (within groups... etc) inside projects. Projects will always be the top of the hierarchy. Then you have sales, which is made by a user, but also belong to a parent (either a group or project ). I'm using a polymorphic relationship where sales and users (and optionally groups) morphs to a parentable (projects and groups). Basically, stripped-down, I have the following tables:

Table structure

projects

id
name
groups

id
name
parentable_id
parentable_type
users

id
name
parentable_id
parentable_type
sales

id
user_id
parentable_id
parentable_type

Question

The hierarchy works as expected, the problem occours when I want to create a query to select all sales within a given project or group. Sales that are directly connected to the given parent are easy to catch, but I somehow need to create a query to traverse the hierarchy for potential children groups. Basically, what I currently have to find all sales connected to a project:

$items = Project::selectRaw( '*, count( sales.user_id ) as salesCount' )
            ->join( 'users' )->on( 'projects.id', 'users.parentable_id' )->where( 'users.parentable_type', 'App\Project' )
            ->join( 'sales' )->on( 'users.id', 'sales.user_id' )
            ->groupBy( 'projects.id' )->orderBy( 'salesCount', 'desc' )->get();

How would I extend this to also include sales done by child groups and grandchild groups.. etc?

Last updated 3 years ago.
0

Sign in to participate in this thread!

Eventy

Your banner here too?

Flugger flugger Joined 8 Apr 2014

Moderators

We'd like to thank these amazing companies for supporting us

Your logo here?

Laravel.io

The Laravel portal for problem solving, knowledge sharing and community building.

© 2025 Laravel.io - All rights reserved.