Does anyone know why this would be happening? I feel like its something obvious that I’m missing, I’ve been trying to figure it out for a few hours now…
A link to all the links I was trying to post (code, and screenshots of database structure):
https://justpaste.it/3w784
I’m guessing it might be because of the database returning multiple rows on join. To make sure you could check the SQL statement that is producing both results (i.e. in Yii’s Debug panel) and run it in your DB console.
Thanks! I looked into it and it looks like when its calling the department its fine:
SELECT * FROM department WHERE id IN (24, 29, 48, 30, 10)
but then in the next statement its only calling the unique faculties
SELECT * FROM faculty WHERE id IN (3, 1, 5)
Is there a way to have it select the faculty for each department regardless of uniqueness?
Here’s how Im doing my calls:
public function getDepartment()
{
return $this->hasOne(Department::className(), [‘id’ => ‘department_id’])
->via(‘applicant’);
}
public function getFaculty()
{
return $this->hasOne(Faculty::className(), [‘id’ =>‘faculty_id’])
->via(‘department’);
}
the actual query itself is:
$ query = ApplicationShift::find()->joinWith([‘application’,‘applicant’,‘department’,‘faculty’, ‘floor’,‘building’,‘shift’]);
Could you paste here raw SQL statements made for full grid view and the filtered one?
It’s doing multiple statements I guess based on the fact that there is a page limit of 20 for the grid:
First it gets the count:
SELECT COUNT(*) FROM application_shift LEFT JOIN application ON application_shift.application_id = application.id LEFT JOIN applicant ON application.applicant_id = applicant.id LEFT JOIN department ON applicant.department_id = department.id LEFT JOIN faculty ON department.faculty_id = faculty.id LEFT JOIN application_floor ON application_shift.application_id = application_floor.application_id LEFT JOIN floor ON application_floor.floor_id = floor.id LEFT JOIN building ON floor.building_id = building.id LEFT JOIN shift ON application_shift.shift_id = shift.id
this is correct, then it gets the data but only from the application_shift table (I tried changing it so it grabbed just the data I wanted from all the tables by adding ->select(‘faculty.name’) but this didnt work):
SELECT application_shift.* FROM application_shift LEFT JOIN application ON application_shift.application_id = application.id LEFT JOIN applicant ON application.applicant_id = applicant.id LEFT JOIN department ON applicant.department_id = department.id LEFT JOIN faculty ON department.faculty_id = faculty.id LEFT JOIN application_floor ON application_shift.application_id = application_floor.application_id LEFT JOIN floor ON application_floor.floor_id = floor.id LEFT JOIN building ON floor.building_id = building.id LEFT JOIN shift ON application_shift.shift_id = shift.id LIMIT 20
Then it gets the application IDs:
SELECT * FROM application WHERE id IN (1, 4, 6, 7, 12, 13)
SHOW FULL COLUMNS FROM application
Then it does this, Im not sure what it is (what is this?):
SELECT
kcu.constraint_name,
kcu.column_name,
kcu.referenced_table_name,
kcu.referenced_column_name
FROM information_schema.referential_constraints AS rc
JOIN information_schema.key_column_usage AS kcu ON
(
kcu.constraint_catalog = rc.constraint_catalog OR
(kcu.constraint_catalog IS NULL AND rc.constraint_catalog IS NULL)
) AND
kcu.constraint_schema = rc.constraint_schema AND
kcu.constraint_name = rc.constraint_name
WHERE rc.constraint_schema = database() AND kcu.table_schema = database()
AND rc.table_name = ‘application’ AND kcu.table_name = ‘application’
Then it selects application again, and a similar thing it did with application:
SELECT * FROM application WHERE id IN (1, 4, 6, 7, 12, 13)
SELECT * FROM applicant WHERE id IN (1, 4, 6, 7, 12, 13)
SHOW FULL COLUMNS FROM applicant
SELECT
kcu.constraint_name,
kcu.column_name,
kcu.referenced_table_name,
kcu.referenced_column_name
FROM information_schema.referential_constraints AS rc
JOIN information_schema.key_column_usage AS kcu ON
(
kcu.constraint_catalog = rc.constraint_catalog OR
(kcu.constraint_catalog IS NULL AND rc.constraint_catalog IS NULL)
) AND
kcu.constraint_schema = rc.constraint_schema AND
kcu.constraint_name = rc.constraint_name
WHERE rc.constraint_schema = database() AND kcu.table_schema = database()
AND rc.table_name = ‘applicant’ AND kcu.table_name = ‘applicant’
SELECT * FROM application WHERE id IN (1, 4, 6, 7, 12, 13)
SELECT * FROM applicant WHERE id IN (1, 4, 6, 7, 12, 13)
SELECT * FROM department WHERE id IN (68, 22, 26, 63, 8, 67)
SHOW FULL COLUMNS FROM department
SELECT
kcu.constraint_name,
kcu.column_name,
kcu.referenced_table_name,
kcu.referenced_column_name
FROM information_schema.referential_constraints AS rc
JOIN information_schema.key_column_usage AS kcu ON
(
kcu.constraint_catalog = rc.constraint_catalog OR
(kcu.constraint_catalog IS NULL AND rc.constraint_catalog IS NULL)
) AND
kcu.constraint_schema = rc.constraint_schema AND
kcu.constraint_name = rc.constraint_name
WHERE rc.constraint_schema = database() AND kcu.table_schema = database()
AND rc.table_name = ‘department’ AND kcu.table_name = ‘department’
SELECT * FROM application WHERE id IN (1, 4, 6, 7, 12, 13)
SELECT * FROM applicant WHERE id IN (1, 4, 6, 7, 12, 13)
SELECT * FROM department WHERE id IN (68, 22, 26, 63, 8, 67)
SELECT * FROM faculty WHERE id IN (3, 1, 2)
SHOW FULL COLUMNS FROM faculty
…
Sorry, it’s hard to read like that. Remember to always format your code.
The “show full columns” and constraints are to fetch DB schema. When you add cache to your DB component it will be called only when cache is empty or expired.
By looking at the ApplicationSearch code the SQL should be with something like SELECT * FROM application ... with multiple joins and for the second screen almost the same but with additional WHEREs for filtering.
I’m working with @paperbagperson on this. Let me see if I can shine any light on this… 'cause the bug is a pain. Here’s a bit of our data model ERD:
The issue we’re seeing is when we want the faculty for an applicant to show up in the applications list…
So, application has applicant_id, applicant then supplies department_id (which we successfully use to grab department.name) and then department supplies faculty_id. However, getting faculty name is hit and miss in the results. I’ve also tested that I can get department.faculty_id consistently while faculty.id will fail at the same time as faculty.name (which makes sense - when I dump the dataProvider, it shows ‘faculty’ => null for those rows… others show ‘faculty’ => app/model/Faculty)
We’ve seen this behaviour in another part of the webapp, and in both cases it seems that if we’re trying to get relations of relations (single table in between) we’re fine but if we want relations^3 (two tables in between) it sometimes falls down.
Additional picture showing the extra columns for debugging (department.faculty_id and faculty.id):
SELECT application.* FROM application
LEFT JOIN applicant ON application.applicant_id = applicant.id
LEFT JOIN department ON applicant.department_id = department.id
LEFT JOIN faculty ON department.faculty_id = faculty.id
WHERE faculty.id IN (‘8’, ‘3’) LIMIT 10
Ok, so this is the first SQL (I’m guessing for $role_id == '2'). Second one would be the same but with AND applicant.name = "Jaden" if I’m seeing this right. It’s kinda hard to help you without DB data.
Run this in PhpMyAdmin, see what it returns. Then remove the limit. Then replace application.* with * and run it again. Compare expected number of rows with what you got.
According to the ERD, the relations are as simple as the following:
Application has one Applicant
Applicant has one Department
Department has one Faculty
It’s OK?
While you have defined 2 shortcut relations in Application model like the following:
public function getDepartment()
{
return $this->hasOne(Department::className(), ['id' => 'department_id'])
->viaTable('applicant', ['id' => 'applicant_id']);
}
public function getFaculty()
{
return $this->hasOne(Faculty::className(), ['id' =>'faculty_id'])
->via('department');
}
I guess these definitions could be the cause of the strange problem.
The faculty of the application could be (and probably should be) accessed by a lengthy but straight notation of $application->applicant->department->faculty, not by the shortcut of $application->faculty.
So the index.php view could be:
...
[
'attribute' => 'applicant',
'value' => 'applicant.name',
],
[
'attribute' => 'faculty_name', // or something like that
'value' => 'applicant.department.faculty.name',
...
],
[
'attribute' => 'department_name', // or something like that
'value' => 'applicant.department.name',
...
],
...
And ApplicaitonSearch::search() could be:
...
if ($role_id == 4 || $role_id == 3){
$query = Application::find()->joinWith([
'applicant',
'applicant.department',
'applicant.department.faculty',
]);
}
...
I believe it’s worth trying.
I changed the query and that worked… is that documented behaviour? Asking because I haven’t seen it in the docs.
Thanks!
No, it’s not documented.
I haven’t experienced this behavior myself so far, probably because I haven’t used ‘via’ or ‘viaTable’ except for a simple many-to-many relation with a junction table in between.
It could be a bug that arises when you try to define a relation using ‘via’ with another relation that is not a direct one. I’m not sure.
Keeping things simple and straightforward is one of the best practices. 