How to do this query in Laravel Query Builder

DB::table('application_forms')->join('users', 'application_forms.user_id', '=', 'users.id')->select('users.*', 'application_forms.*')->selectRaw(DB::raw("AVG(application_forms.score) AS avg_score"))->get();

@Qlic Score is not on the application_forms its on different table (scores) and it can be returned null.

I have 3 different tables. users, application_forms, scores

Foreign Keys: scores.application_id = application_forms.id application_forms.user_id = users.id

Also I dont want to have duplicate score data.

I just want to see averages of scores. (If no score it should return 0)

Ahh oke, than this should be it.

DB::table('application_forms')->join('users', 'application_forms.user_id', '=', 'users.id')
->leftJoin('scores', 'application_forms.id', '=', 'scores.application_id')
->select('users.*', 'application_forms.*')
->selectRaw(DB::raw("COALESCE(AVG(scores.score), 0) AS avg_score"))
->get();

You can add additional groupBy clause to prevent possible duplicates.

@Qlic

This is the error that I am getting now :)

SQLSTATE[42000]: Syntax error or access violation: 1140 In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'finansbank.users.id'; this is incompatible with sql_mode=only_full_group_by (SQL: select users., application_forms., COALESCE(AVG(scores.score), 0) AS avg_score from application_forms inner join users on application_forms.user_id = users.id left join scores on application_forms.id = scores.application_id)

Well, that basically comes through strict, simplest way to fix it is to go to config/database and set strict to false.

See: https://stackoverflow.com/questions/43481869/aggregated-query-without-group-by

is it safe?

Is it save to drive a car, no, still people do it. Personally i see no harm in doing so, i've done it in my own projects aswell.

But if you don't, you'll have to use a groupBy, apparently on the users.id field.

Why do you guys use >selectRaw(DB::raw(.. when you could just use getPdo() and be done.