Hi!
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:
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
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?
Sign in to participate in this thread!
The Laravel portal for problem solving, knowledge sharing and community building.
The community