laravel array inside query results

Question

I have query of data which some of it's data needs to be array but not sure how to get those arrays inside my query.

Screenshot

I have 2 students in 2 semester and 4 different classes what I try to achieve is to get each student semester and classes in array.

So I would have results such as this:

code

$students = DB::table('schools')
            ->where('schools.id', $id)
            ->join('school_semesters', 'school_semesters.school_id', '=', 'schools.id')
            ->join('semester_classes', 'semester_classes.semester_id', '=', 'school_semesters.id')
            ->join('class_students', 'class_students.class_id', '=', 'semester_classes.id')
            ->join('users', 'users.id', '=', 'class_students.user_id')
            ->select(
                'school_semesters.name as semester',
                'semester_classes.name as class',
                'users.name as students',
                'users.id as id'
            )
            ->groupBy('users.id') // return results as screenshot #2
            ->get();

Any idea?

Update

expected result would be something like this:

semester: ["Winter semester", "Our Spring semester"]
class: ["A1", "B1"]
students: "Student Two"
id: 5
DT_RowId: 5

so semester and class will be array.

so your question is how to convert your query result into an array? — Gamopo
– Gamopo, Commented Mar 11, 2020 at 8:52
@Gamopo only semester and classes otherwise it is array already, (each user will have 2 arrays inside it's data) — mafortis
– mafortis, Commented Mar 11, 2020 at 8:53
I'm not sure I understand what your question is, can you add (in code style) what output you expect from the DB query? — user5446912
– user5446912, Commented Mar 11, 2020 at 9:02

Dilip Hirapara · Accepted Answer · 2020-03-11 09:00:04Z

1

You're doing a group by user.id. by using Group concat you'll get the expected result.

->select(
    \DB::raw('group_concat(DISTINCT school_semesters.name) as semester'),        
    \DB::raw('group_concat(DISTINCT semester_classes.name) as class'),
    'users.name as students',
    'users.id as id'
)

answered Mar 11, 2020 at 9:00

Dilip Hirapara

15.4k3 gold badges34 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

mafortis Over a year ago

Column not found: 1054 Unknown column 'GROUP_CONCAT(school_semesters.name)' in 'field list'

Dilip Hirapara Over a year ago

Check I've updated with DB::raw query. check last updated query.

Collectives™ on Stack Overflow

laravel array inside query results

Screenshot

code

Update

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

Screenshot

code

Update

1 Answer 1

2 Comments

Related