Displaying wrong data in a column

jryancueto74

I have two tables namely: school_accounts and school_students
school_accounts has id(PK), firstname,lastname,middlename
school_students has id, account_id(FK), nickname, remarks, studentno, is_active

I can display them in DataTable by doing this:
"columns": [
{data: 'id', name: 'school_accounts.id'},
{data: 'lastname', name: 'school_accounts.lastname'},
{data: 'username', name: 'school_accounts.username'},
{data: 'email', name: 'school_accounts.email'},
{data: 'phone', name: 'school_accounts.phone'},
{data: 'firstname', name: 'school_accounts.firstname'},
{data: 'updated_at', name: 'school_accounts.updated_at'},
]

Jack Ryan has a daughter named Leslie Anne who is a student.
The problem is that,
this {data: 'firstname', name: 'school_accounts.firstname'}, displayed the name "Jack Ryan" instead of "Leslie Anne".

What I want is to display is the name "Leslie Anne" in Students column.

This is the output:

I am confused. I will appreciate any help you can extend to me. Thank you very much.

allan

From the data above, it makes sense that "Jack Ryan" is shown in both the Account name and Students columns since school_accounts is the table being accessed.

Does the data being loaded from the server actually include the students information? Can you show a sample of the JSON being loaded from the server?

Thanks,
Allan

jryancueto74

Sorry for the late response. Data being loaded from the server does not include students information, just the student's first name and last name from school_accounts table. JSON sample of school_accounts/school_students table? Sorry I'm new to this also thanks for the response.

jryancueto74

I change my mind about not including students information. I plan to add student no or nickname inside a parenthesis after student's name.

jryancueto74

Here's a sample JSON that is being loaded from the server.

"data": [
    [
      lastname: "Cueto",
      firstname: "Jack Ryan",
      middlename: "H",
      username: "jackryancueto",
      email: "jryancueto74@gmail.com",
      contact: "128391284",
      firstname:"Jack Ryan"
    ]
]

Thank you very much.

allan

It looks like the data for the server doesn't actually contain any information about Jack's students. Is that correct? I'd expect there to be an array of information about the students (assuming that there is more than one student per person).

Allan

jryancueto74

Sorry for the late response. I only have 1 parent account and 1 student account.

Then, here's my update. I added a studentno column where Leslie Anne's student number is 17-000.

The data that is being loaded from the server:

"data": [
    [
      lastname: "Cueto",
      firstname: "Jack Ryan",
      middlename: "H",
      username: "jackryancueto",
      email: "jryancueto74@gmail.com",
      contact: "128391284",
      firstname:"Jack Ryan",
      studentno: "17-000" // Leslie's student no
    ]
]

Leslie's information data. Just in case you want to know her infromation.

"data": [
    [
      studentno: "17-000",
      nickname: "Les",
      account_id: "10", //her id from school_accounts table
      guardian_account_id: "11", // id of her father, Jack, from school_accounts table
      is_active: "1"
    ]
]

I hope this also helps:

$parents = SchoolAccount::withTrashed()
        ->leftJoin('school_students','school_students.guardian_account_id','=','school_accounts.id')
        ->select([
            'school_accounts.*',
            'school_students.studentno'
            ])
        ->where('school_accounts.school_id',$this->subscription->subscription->id)
        ->groupBy('school_accounts.id');

 return Datatables::of($parents)->make(true);

Thank you.

jryancueto74

Sorry for the late response. I only have 1 parent account and 1 student account.

Then, here's my update. I added a studentno column where Leslie's student number is 17-000.

The Data that is being loaded from the server:

"data": [
    [
      lastname: "Cueto",
      firstname: "Jack Ryan",
      middlename: "H",
      username: "jackryancueto",
      email: "jryancueto74@gmail.com",
      contact: "128391284",
      firstname: "Jack Ryan",
      studentno: "17-000" // Leslie's student number
    ]
]

I hope this also helps

$parents = SchoolAccount::withTrashed()
        ->leftJoin('school_students','school_students.account_id','=','school_accounts.id')
        ->select([
            'school_accounts.*',
            'school_students.studentno'
            ])
        ->where('school_accounts.school_id',$this->subscription->subscription->id)
        ->groupBy('school_accounts.id');

Thank you very much.

jryancueto74

Sorry for the late response. I only have 1 parent account and a student account. Then here's my update. I added a studentno column where Leslie's student no is 17-000.

The data that is being loaded from the server

"data": [
    [
      lastname: "Cueto",
      firstname: "Jack Ryan",
      middlename: "H",
      username: "jackryancueto",
      email: "jryancueto74@gmail.com",
      contact: "128391284",
      firstname: "Jack Ryan",
      studentno: "17-000",
    ]
]

Thank you very much.

jryancueto74

Sorry for the late response. I only have 1 parent account and a student account.

I also updated my work by adding another column named studentno where Leslie's student number which is 17-000 would appear.

"data": [
    [
      lastname: "Cueto",
      firstname: "Jack Ryan",
      middlename: "H",
      username: "jackryancueto",
      email: "jryancueto74@gmail.com",
      contact: "128391284",
      firstname: "Jack Ryan",
      studentno: "17-000"
    ]
]

Thank you very much.

jryancueto74

Sorry for the late response. I only have 1 parent account and a student account.

I also updated my work by adding another column named studentno where Leslie's student number which is 17-000 would appear.

"data": [
    [
      lastname: "Cueto",
      firstname: "Jack Ryan",
      middlename: "H",
      username: "jackryancueto",
      email: "jryancueto74@gmail.com",
      contact: "128391284",
      firstname: "Jack Ryan",
      studentno: "17-000"
    ]
]

Thank you very much.