Why is my query not returning what I expect?

@Sinnbeck I am trying to find all working hour control records that meet the given dates. I set it up that way because then I have a method that expects an instance of Illuminate.

which is this:

$all_checks_to_paginate = $signings->flatMap(fn($value, $key) => WorkingHourControlHelper::loadWorkingHourControls($value->working_hour_control));

        return CollectionPaginatorHelper::paginate(collect($all_checks_to_paginate), $attributes['options']['perPage'] ?? 50, $attributes['options']['page'] ?? 1);

@Alejo Ah you are paginating everything in memory? Thats quite inefficient. But did my queyr example work ?

@Sinnbeck Yes! I'm applying the page layout in this way, at the moment I don't know how else I can optimise the page layout. The query you tell me about doesn't let me execute it, it must be because I'm not using laravel 9. I'm with laravel 8

@Alejo Then you can do this

$signings = Employee::with(['working_hour_control' => function($query) use ($attributes) {
    $query->whereBetween('start_date_time', [$attributes['data']['from'], $attributes['data']['to']]  );
}])
                ->whereHas('working_hour_control', function($query) use ($attributes) {
                    $query->whereBetween('start_date_time', [$attributes['data']['from'], $attributes['data']['to']]  );
                })
                ->get();

@Sinnbeck okay, I understand that you apply the filter to both tables?

Great, if I do a return of the query you gave me it returns the records in the working_hour_controls table that meet that condition. But I don't understand why when I pass them to my WorkingHourControlHelper then the result is different, it's showing me records from other dates :s

@Alejo Yes your own query ONLY limits the employees table.. Not the working_hour_controls table

But if you are still getting some sort of wrong records, I can recommend you use laravel debugbar to see what queries are actually being run

@Sinnbeck Great thanks for the correction of my query :)

Sorry @sinnbeck does the debugbar work by making requests from postman?

ok, I'll have a look at the debugbar but right now I did a dd on my helper and it shows me the dates I mentioned before that are wrong :S I'm freaking out

@Alejo No it does not work with postman. But calling the url in a browser should be pretty easy, and just return some dummy view. And now look in the debugbar on the page to see the queries being run.

@Sinnbeck perfect!!! there are several queries that are executed, but the one that interests me is this one:

select * from emp_employees where exists (select * from emp_working_hour_controls where emp_employees.id = emp_working_hour_controls.employee and start_date_time between '2022-12-01' and '2022-12-02' and emp_working_hour_controls.deleted_at is null) and emp_employees.deleted_at is nul

and then this one:

select * from emp_working_hour_controls where emp_working_hour_controls.employee in (1, 2, 10, 11) and start_date_time between '2022-12-01' and '2022-12-02' and emp_working_hour_controls.deleted_at is null

@Alejo Looks correct. If you run those in your database manager, do you get any wrong records ?

@Sinnbeck great!!! the first query looks for the employees and the second one makes the filter and brings me the records of the database, but the strange thing is that it brings me only those of the date 2022-12-01 it does not bring me those that I have in the database of the day 2022-12-02

@Alejo Yeah. Change to this then

$signings = Employee::with(['working_hour_control' => function($query) use ($attributes) {
    $query->whereDate('start_date_time',  '>=', $attributes['data']['from'])
                  ->whereDate('start_date_time', '<=', $attributes['data']['to']] );
}])
                ->whereHas('working_hour_control', function($query) use ($attributes) {
                        $query->whereDate('start_date_time',  '>=', $attributes['data']['from'])
                                     ->whereDate('start_date_time', '<=', $attributes['data']['to']] );
                })
                ->get();

Sorry if the >= and <= are the wrong way around. If you get 0 results, just reverse them :)

@Sinnbeck yeah!!! Thank you! now I do get the records I am sending to filter, when I run the query on datagrid

but i'm still freaking out that postman shows me records from other dates..... unbelievable :S

I don't understand what's going on I'm going to clear cache and everything else I can.

@Alejo Yeah. I suggest just testing it out in a browser without auth: There are several browser plugins to format json pretty, in chrome or firefox. Then once it works, try with postman

@Sinnbeck okay I'm going to do it thank you very much!!

@Alejo Happy to help. If any of my answers helped you solve the issue, please mark it as best answer to close the thread :)