group-by not Working With order by laravel elequent

A select usually comes before groupby and orderby.

@jlrdw yes but no matter the order where ever i use it wont work

Does it throw an error or how to you see the error?

@sinnbeck hi dea sinnbeck :) no it wont give any error it just order them and dont group by the result so i would have duplicate results

Not your data, but example 0f working query. The parent table is dc_powners The child is dc_pets. Order of everything is important, double check yours.

$quy = Powner::query()->leftJoin('dc_pets', 'dc_powners.ownerid', '=', 'dc_pets.ownerid')
                ->select('dc_powners.ownerid', 'dc_powners.oname')
                ->selectRaw('count(dc_pets.petid) as countOfPets')
                ->groupby('dc_powners.ownerid')
                ->orderby('dc_powners.oname')
                ->get();

Results basically give:

ownerid, oname, countOfPets

Like:

5|Bob|3
4|Greg|9
2|Rob|1

Any chance you can use the DB::listen command (or debugbar) to see the actual query it generates?

i double checked that and fixed the orders but yet not working maybe because its combining with others join in spatie laravel query builder huh ?

yes sir here you go

select `dr`.`sales_price`, `accommodations`.* from `accommodations` inner join `accommodation_rooms` as `ar` on `ar`.`id` = `accommodations`.`id` inner join `room_pricing_histories` on `room_pricing_histories`.`id` = `ar`.`id` inner join `accommodation_rooms` as `br` on `br`.`id` = `accommodations`.`id` inner join `room_capacity_histories` on `room_capacity_histories`.`id` = `br`.`id` inner join `accommodation_rooms` as `cr` on `accommodations`.`id` = `cr`.`accommodation_id` inner join `room_pricing_histories` as `dr` on `cr`.`id` = `dr`.`accommodation_room_id` where `room_pricing_histories`.`sales_price` > ? and `room_pricing_histories`.`sales_price` < ? and date(`room_capacity_histories`.`from_date`) > ? and date(`room_capacity_histories`.`to_date`) < ? group by `dr`.`sales_price`, `accommodations`.`id` order by `dr`.`sales_price` desc

If you test the query in your db manager, does it work there then? (dbeaver, heidisql, phpmyadmin)

yet the same result i have duplicate data in it .

Can you show and example of the output? Just like 3 or 4 rows to get a sense of it :)

Hey @farshadf

Use sortBy instead of orderBy

Are all the prices the same value? Otherwise they will be separate rows.

@aurawindsurfing sortBy is a collection method. He is building a query using the query builder

Then he should run his method on results of groupBy it returns completely new set of records, this is why orderBy does not work I think.

@snapey yes exacly

my problem is not sorting i am sorting them my problem is groupby and the duplicate results that i get

@farshadf are you able to write the same query in MySQL IDE and get expected results? Maybe try this first and make sure your query works on MySQL level?

@farshadf could you give a few rows as an example?

Does the query need to be that huge, maybe you can narrow result some.

Out of all seriousness I've written business applications with accounts payable accounts receivable and never needed a query that big.

Just a thought.

yes @jlrdw i have no other option this query filters all the data that user wants to see and involves to many tables in my database

Is your query for a general search where people can paginate through search results.

The reason I ask is normally in these kind of results you don't have group by.

See https://laracasts.com/discuss/channels/guides/getpdo-usage

Scroll down and there's an example picture of a report. To me Group by is mostly for a report.

Seems there should be a way to refactor your code to just return paginated search results.

But you also using external packages , so it's hard to tell exactly what needs to be done.

Perhaps try refactoring but build on to your query just a little at a time until you have it working.

@jlrdw yes you are right but first i have to make it work then i should go for refactor i think . for you and other guys to understand better consider the link below from booking.com i paste below and i want to write all the filters it has in left sidebar :

https://www.booking.com/searchresults.en-us.html?label=gen173nr-1FCAEoggI46AdIM1gEaDuIAQGYATG4ARfIAQzYAQHoAQH4AQKIAgGoAgO4Aomd7O0FwAIB&sid=5ffe0f643656aea38463e107aa753c96&sb=1&src=index&src_elem=sb&error_url=https%3A%2F%2Fwww.booking.com%2Findex.html%3Flabel%3Dgen173nr-1FCAEoggI46AdIM1gEaDuIAQGYATG4ARfIAQzYAQHoAQH4AQKIAgGoAgO4Aomd7O0FwAIB%3Bsid%3D5ffe0f643656aea38463e107aa753c96%3Bsb_price_type%3Dtotal%26%3B&ss=New+York%2C+New+York+State%2C+USA&is_ski_area=&checkin_year=2019&checkin_month=11&checkin_monthday=8&checkout_year=2019&checkout_month=11&checkout_monthday=17&group_adults=2&group_children=0&no_rooms=1&b_h4u_keep_filters=&from_sf=1&ss_raw=ne&ac_position=0&ac_langcode=en&ac_click_type=b&dest_id=20088325&dest_type=city&iata=NYC&place_id_lat=40.768074&place_id_lon=-73.981895&search_pageview_id=3c4675448c6f0113&search_selected=true&search_pageview_id=3c4675448c6f0113&ac_suggestion_list_length=5&ac_suggestion_theme_list_length=0

that makes it a bit large query and really hard i think to implement . because of that i used spatie laravel query builder which is specified for filters i think and it was the best one around

@farshadf take for example this simpler example:

Here go through the request and get the parameters into variables. something like:

        $page = Request::input('page', '1');
        $dogsearch = !empty(Request::input('psch')) ? Request::input('psch') : '';
        $aval = !empty(Request::input('aval')) ? Request::input('aval') : '';
        $dogsch = $dogsearch . "%";

//Build query

$query = Dog::where('dogname', 'like', $dogsch);
        if ($aval == "n") {
            $query->where('adopted', '=', 1);
        } else if ($aval == "y") {
            $query->where('adopted', '=', 0);
        }
        $dogs = $query->orderBy('lastedit', 'DESC')->paginate(5);

        $params = array('psch' => $dogsearch, 'aval' => $aval);
        $title = 'Admin';

.... other code

        return view('dog.index', compact('dogs', 'params'))
                        ->with('title', $title);

Notice the params array

$params = array('psch' => $dogsearch, 'aval' => $aval);

It is passed to view:

In the view foreach your data, and for pagination:

{{ $dogs->appends($params)->links() }}

The route is:

Route::get('dog/indexadmin', 'DogController@indexAdmin');

Notice I do not need to pass parameters in the route, they are not route parameters, they are query string parameters. Taken care of already, it's already a HTTP thing. You just have to "get them" and "pass them" each paginated pass in the $params array.

It's hard to explain all in a forum post, and it's just an example.

thanks for your good example but if i want to do this if you take a look at the example i gave you it would be some endless conditions and i cant sort the parent by the child relation i looked a lot in documentation that is not possible at least untill laravel 6 idk about that

@farshadf I guess you need to go for a long long walk and look at it from distance.

You have quite experienced guys trying to help you here. I think you might be to fix on your way of doing things.

If you are trying to solve something with a monster query then it means that your whole app needs to be redesigned as it does not suit your purpose. Look at putting some of this directly on models that are involved. Then from use those models to simplify your controllers then break you query into smaller queries or query scopes etc etc.

Monster Queries are bad!

Happy Coding!

@aurawindsurfing thanks man i think i have to stop coding for some days and just think about changing the flow or the way of doing this i will update this post when ever i come up with a good solution thanks to all of you guys

Hello,

I just struggled exactly on the same problem as @farshadf... and solved my issue in 5 minutes thanks to the Laravel documentation :-) There I discovered and tried successfully the "reorder()" method which basically erase previous ordering on a given query. My working (ie, ordered by the columns I wanted) was as follow:

return $query->join('trainings', 'steps.training_id', '=', 'trainings.id')
                     ->join('contacts','contacts.training_id','=','trainings.id')
                     ->join('users','users.id','=','trainings.user_id')
                     ->select(['steps.id','subject','trigger_date','trigger_time','trainings.name','users.name as nameUser',DB::raw('count(contacts.id) as nb_contactl')])
                     ->groupBy('steps.id')
                     ->reorder()
                     ->orderBy('trigger_date', 'asc')
                     ->orderBy('trigger_time', 'asc')
                     ->get();

I hope it helps :-)