laravel query building with a lot of joins

Question

my sql query is

$q = "SELECT 
                file,
                roleId,
                page,
                type,
                userType,
                COUNT(DISTINCT($a)) as 'a',
                COUNT(DISTINCT($b)) as 'b'
            FROM table_name
            WHERE
                course IN ($type)
                AND date BETWEEN '$startDate' AND '$endDate'
                AND (course_1 is NULL OR course_1 NOT IN ('ABCD'))
                AND deleted IS NULL
                AND type LIKE '%Professor%'
                AND action = 'submit'
                GROUP BY file, roleId";

I want to convert that query into laravel query builder like

$orders = DB::table('table_name')
                ->select('file', 'roleId', 'page', 'type', userType DB::raw('COUNT(DISTINCT(($a)) as 'a'))
                ->groupBy('file', 'roleId')
                ->get();

Do understand how to add the other COUNT into it and how and where to chain the ->where() ?

Your `GROUP BY` is wrong, it's mandatory grouping result set by all columns that exists at your `SELECT` statment but those product of an aggregation function — , Dec 31 '19 at 22:21

score 0 · Accepted Answer · answered Feb 04 '19 at 19:31

0

<?php 

$orders = DB::table('table_name')
->whereIn('course', $courseTypes)
->whereBetween('date', [$startDate, $endDate])
->where(function($q){
    return $q->whereNull('course_1')
    ->orWhereNotIn('course_1', $courseArrayToFilter);
})
->whereNull('deleted')
->where('type', 'like', '%Professor%')
->where('action', 'submit')
->groupBy('file', 'roleId')
->select(
    'file',
    'roleId',
    'page',
    'type',
    'userType',
    \DB::raw("COUNT(DISTINCT($a)) as a"),
    \DB::raw("COUNT(DISTINCT($b) as b")
)
->get();

answered Feb 04 '19 at 19:31

Mihir Bhende

8,677
1
30
37

Error . Argument 1 passed to Illuminate\Database\Query\Building::cleanBuildings() must be an array, string given – codecodecode Feb 04 '19 at 20:00
Wherever you have whereIn or WhereNotIt etc, you need to pass a php array and not comma separated string. – Mihir Bhende Feb 04 '19 at 20:01
Still Error ... access violation:1055 select list is not in GROUP BY clause and contains nonaggregated column file which is not functionally dependent on group by clause – codecodecode Feb 04 '19 at 20:16
For that you need to work on your basic query. What essentially it means, in MySQL if you are using group by, then you can not directly select non-aggregated columns. To disable this only_full_group_by setting and that error should go away – Mihir Bhende Feb 04 '19 at 20:18
how to disable?? – codecodecode Feb 04 '19 at 20:22
https://stackoverflow.com/questions/23921117/disable-only-full-group-by – Mihir Bhende Feb 04 '19 at 20:23

score 0 · Answer 2 · 2019-12-31T22:22:37.207

Maybe in a cleanest way doing this:

Use the Model instance in inspite of invoke the DB facade
Use whereRaw to pass complex or pure SQL queries
Use selectRaw() to process pure SQL queries for instance functions like COUNT
Use Multiple WHERE to emulate AND operator
Use whereIn to filter ranges of data
You need to GROUP BY all columns in your SELECT you have an error at this point, I mean your query is wrong

Code

$data = Model::select('file', 'roleId', 'page', 'type', 'userType')
                ->selectRaw('COUNT(DISTINCT($a)) as a')
                ->selectRaw('COUNT(DISTINCT($b)) as b')
                ->whereRaw('type IN $type')
                ->whereIn('date', [$startDate, $endDate])
                ->whereRaw("course_1 IS NULL OR course_1 NOT IN('ABCD')")
                ->whereNull('deleted')
                ->where('type', 'LIKE', "%".'Professor'."%")
                ->where('action', '=', 'submit')
                ->groupBy('file', 'roleId', 'page', 'type', 'userType')
                ->get();

laravel query building with a lot of joins

2 Answers2