How to generate query details in drop down in yii2

Yii 2.0
1

I am using yii2 basic application template. I have employee CRUD, and Church CRUD. In my application church is master table which stores church details. The scenario is that I have to assign employee as pastors to churches. So I have a table churchassignment which has foll fields:

Table: churchassignment

  1. ChurchAssignmentId
  2. ChurchId
  3. EmployeeId

Here in above table ChurchId is forign key coming from Church master table and EmployeeId is also foreign key coming from Employee table.

The table structure for church master table is as below:

Table : Church

  1. ChurchId
  2. Church
  3. CommunityType
  4. District Id
  5. TalukaId
  6. VillageId
  7. LocationId
  8. AreaId

Basically the above structure shows that if community type is rural then LoctionId and AreaId will be Null and if CommunityType is Urban then DistrictId, TalukaId and VillageId will be set to Null.

I have CRUD for church assignments as well.

Now I want that while creating church assignments, I want to show churches in drop down as follows for example:

LWF-Aurangabad-Paithan-Khadgaon

Here LWF is the name of church, Aurangabad is District, Paithan is Taluka and Khadgaon is village.

So in Church models I have foll function:

public function getChurch()
	{
$churchdetails = Yii::$app->db->createCommand('SELECT church.ChurchName, district.District, taluka.Taluka,village.Village
FROM church, district, taluka, village
where church.DistrictId=district.DistrictId AND
church.TalukaId=taluka.TalukaId AND
church.VillageId=village.VillageId')->queryAll();

foreach($churchdetails as $churchdetails1)
{

return $churchdetails1['ChurchName'] . '-' . $churchdetails1['District'].'- '.$churchdetails1['Taluka'].'-'.$churchdetails1['Village'];		
}

}

And in Churchassignments CRUD, I have foll code for selecting the church:

<?= $form->field($model, 'ChurchId')->dropDownList(ArrayHelper::map(Church::find()->all(),'ChurchId','church'), ['prompt' => 'Select Church']) ?>

But when I run the code, I see only first record from church master table is displayed 3 times.

How to accomplish this?

2

You would be better use explicit JOIN syntax instead of old style comma-separated table joining syntax.

SELECT church.ChurchId, church.ChurchName, district.District, taluka.Taluka,village.Village
FROM church
LEFT OUTER JOIN district ON church.DistrictId=district.DistrictId
LEFT OUTER JOIN taluka ON church.TalukaId=taluka.TalukaId
LEFT OUTER JOIN village ON church.VillageId=village.VillageId

And you are returning the result inside a foreach loop.

$churches = [];
foreach($churchdetails as $row) {
    $churches[$row['ChurchId']] = $row['ChurchName'] . '-' . ...
}
return $churches;

// in view
<?= $form->field($model, 'ChurchId')->dropDownList(Church::getChurch(),  ['prompt' => 'Select Church']) ?>
3

Thanks it is working

4

Hi,

What you suggested worked good for the above case.

Now the process is that after assigning employees as pastors to churches, the employee must able to select those churches assigned to them in the the drop down while creating members for particular church.

I have members CRUD

Table : members

  1. MemberId
  2. EmpId
  3. MemberType
  4. PastorId
  5. ChurchId
  6. FamilyHead
  7. Address

Here PastorId and ChurchId are references(foreign keys) coming from church and pastor table and EmpId is the reference coming from Employee table.

For storing members the process is, if member type is LWF member then PastorId will be set to Null. If member type is PastoralNetworking Member , then ChurchId will be set to null.

Now when employee whose role is assigned as pastor and on assigning churches to him logs into his account. Now while creating members record, he opens the create page of Members details and select member type as LWF member, then PastorId will be null andd while selecting church in the drop down only those churches assigned to him should be displayed with the same structure as I mentioned for previous case. For example he has been assigned two churches then he should only see the foll data in drop down as follows:

LWF-Aurangabad-Paithan-Khadgaon
LWF-Aurangabad-Paithan-Alipur

Here this means, LWF is name of church, Aurangabad is district, taluka is Paithan and villages are Khadgaon and Alipur.

I have Churchassignment CRUD and tried the below function to achieve this:

public function getChurchDetails()
	{
$churchdetails = Yii::$app->db->createCommand('select church.ChurchId, church.ChurchName
from church, employee,churchassignment
where church.ChurchId=churchassignment.ChurchId
and employee.EmpId=churchassignment.EmpId

and churchassignment.EmpId='.\Yii::$app->user->identity->id)->queryAll();


$churches = [];
foreach($churchdetails as $row) {
    $churches[$row['ChurchId']] = $row['ChurchName'];
}

return $churches;


	}

And in form I have below code,

<?=	$form->field($model, 'ChurchId')->dropDownList(Churchassignment::getChurchDetails(), ['prompt' => 'Select Church'])?>

After I execute this code I see only the names of the church LWF two times, I want that he should see the foll result

LWF-Aurangabad-Paithan-Khadgaon
LWF-Aurangabad-Paithan-Alipur

Meaning these two churches are assigned to him for which he can create members details.

How should I achieve this?

5

Once again,

6

Hi,
I am using following SQL code. It gives the result in the drop down but in the last it also shows ----- in the drop down. This should not be displayed. Only two churches has been assigned to Employee whose Id is 29, those two churches get displayed but the third item in the drop down appears -----
What should I do?

SELECT church.ChurchId, church.ChurchName, district.District, taluka.Taluka,village.Village,location.Location, area.Area
from church
LEFT OUTER JOIN district ON church.DistrictId=district.DistrictId
LEFT OUTER JOIN taluka ON church.TalukaId=taluka.TalukaId
LEFT OUTER JOIN village ON church.VillageId=village.VillageId
LEFT OUTER JOIN location ON church.LocationId=location.LocationId
LEFT OUTER JOIN area ON church.AreaId=area.AreaId 
RIGHT OUTER JOIN churchassignment ON church.ChurchId=churchassignment.ChurchId
and churchassignment.EmpId=29
7

I think that churchassignment table should be INNER JOINed.

8

Can you provide me the complete sql query?

9 
SELECT church.ChurchId, church.ChurchName, district.District, taluka.Taluka,village.Village,location.Location, area.Area
from church 
LEFT OUTER JOIN district ON church.DistrictId=district.DistrictId 
LEFT OUTER JOIN taluka ON church.TalukaId=taluka.TalukaId 
LEFT OUTER JOIN village ON church.VillageId=village.VillageId 
LEFT OUTER JOIN location ON church.LocationId=location.LocationId 
LEFT OUTER JOIN area ON church.AreaId=area.AreaId
INNER JOIN churchassignment ON church.ChurchId=churchassignment.ChurchId 
  and churchassignment.EmpId=29
10

I use the foll SQL code

SELECT church.ChurchId, church.ChurchName, district.District, taluka.Taluka,village.Village,location.Location, area.Area
from church
LEFT OUTER JOIN district ON church.DistrictId=district.DistrictId
LEFT OUTER JOIN taluka ON church.TalukaId=taluka.TalukaId
LEFT OUTER JOIN village ON church.VillageId=village.VillageId
LEFT OUTER JOIN location ON church.LocationId=location.LocationId
LEFT OUTER JOIN area ON church.AreaId=area.AreaId 
INNER JOIN churchassignment ON church.ChurchId=churchassignment.ChurchId

and churchassignment.EmpId=29

It works correct but there are two results in drop down as

LWF-Aurangabad-Paithan-Khadgaon–
LWF-Aurangabad-Paithan-Alipur–

Why – is appearing at the end of both the results?

11 
LWF-Aurangabad-Paithan-Alipur-(null)-(null)

It’s because those 2 churches are of type rural and have no location and area.
You can modify the code for generating church information depending on the church type.

12

Yes, Thanks for reference