I have 4 table as following: 1- proposals (belongs to projects) 2- projects (has many proposals, and belongs to business) 3- businesses (has many projects, belongs to user) 4- users (has one business)
In users I've set email as hidden field, What I want is to take count of today proposals for each project, and group them based on business, then I should mail count of proposal for each projects of business. Suppose business A has posted two project and received 5 proposal for both projects (3 for one, and 2 for the other). My current query not group data based on business, it only group data based on project. My question is:
1- How can I achieve this to get count of data based on business, and project
2- How can I make visible email field of users model.
$data = Proposal::toDay()
->selectRaw('count(*) AS cnt, project_id')
->with([
'project:id,title,business_id',
'project.business' => function($query) {
return $query->select(['id', 'name', 'owner_id']);
},
'project.business.owner' => function($query) {
return $query->select(['id','first_name','last_name','email']);
}
])
->groupBy('project_id')
->get();
Current output is like this:
array:3 [
0 => array:4 [
"cnt" => 1
"project_id" => 1
"submission_date" => ""
"project" => array:5 [
"id" => 1
"title" => "HRMIS System"
"business_id" => 2
"liked" => false
"business" => array:4 [
"id" => 2
"name" => "Company A"
"owner_id" => 5
"owner" => array:3 [
"id" => 5
"first_name" => "Mobasher"
"last_name" => "Fasihi"
]
]
]
]
1 => array:4 [
"cnt" => 1
"project_id" => 2
"submission_date" => ""
"project" => array:5 [
"id" => 2
"title" => "Senior Software Developer"
"business_id" => 1
"liked" => false
"business" => array:4 [
"id" => 1
"name" => "Company B"
"owner_id" => 2
"owner" => array:3 [
"id" => 2
"first_name" => "John"
"last_name" => "Richard"
]
]
]
]
2 => array:4 [
"cnt" => 1
"project_id" => 4
"submission_date" => ""
"project" => array:5 [
"id" => 4
"title" => "100 Mac-Book Pro"
"business_id" => 1
"liked" => false
"business" => array:4 [
"id" => 1
"name" => "Company B"
"owner_id" => 2
"owner" => array:3 [
"id" => 2
"first_name" => "John"
"last_name" => "Richard"
]
]
]
]
]
What I expect is:
[
0 => [
"cnt" => 1
"submission_date" => "",
"project" => [
"id" => 1,
"title" => "HRMIS System",
"business_id" => 2,
"liked" => false,
"business" => [
"id" => 2
"name" => "Company A"
"owner_id" => 5,
"owner" => [
"id" => 5
"first_name" => "Mobasher",
"last_name" => "Fasihi",
"email" => "mobasher@test.com"
]
]
]
],
1 => [
"cnt" => 2
"submission_date" => "",
"project" => [
"id" => 2
"title" => "Senior Software Developer"
"business_id" => 1,
"liked" => false,
"business" => [
"id" => 1,
"name" => "Company B",
"owner_id" => 2,
"owner" => [
"id" => 2,
"first_name" => "John",
"last_name" => "Richard",
"email" => "john@test.com"
]
]
],
"project" => [
"id" => 4
"title" => "100 Mac-Book Pro"
"business_id" => 1
"liked" => false
"business" => [
"id" => 1,
"name" => "Company B",
"owner_id" => 2,
"owner" => [
"id" => 2
"first_name" => "John"
"last_name" => "Richard",
"email" => "john@test.com"
]
]
]
]
]
