Level 63
->whereNotIn('description', 'LIKE', '%search term%') <-- count()
->whereNotIn() can be used only with an array.
}) <---does not exclude
I real don't understand anything what you need.
I suggest you to learn Laravel and Eloquent ;).
Mar 25, 2025
13
Level 6
report summary with conditions & total hourly time
Hello, I have a table with thousands of rows inside, I should create a report, based on certain conditions, search for values in the columns to group them into specific groups and know the total, I'm trying to understand how I can get this, I should generate a total and add a date filter from to, and receive the results based on groups. If you then need to see the details open the details of that group, I'm at sea I still don't know where to start to start filtering and receive the data, if I try to use in the query whereNotIn it doesn't work if I add
->whereNotIn('description', 'LIKE', '%search term%') <-- error count(): Argument #1 ($value) must be of type Countable|array, string given
->whereNotIn('description', function($query) {
$query->select('description')->from('tickets')->where('description','LIKE','%search term%');
}) <--- this does not exclude
Level 6
@vincent15000 thanks for your reply, maybe I'm not clear in the request for help I wrote, I have to count how many records exist in the table excluding some with certain values, from the description column I have to exclude the rows that contain a word, and include those that contain another, example if the description column contains opening I have to exclude, if it contains closing I have to include in the count
1 like
Level 63
Just like this.
$summariesCount = Summary::
where('description', 'LIKE', '%closing%')
->where('description', 'NOT LIKE', '%opening%')
->count();
Level 6
I'm trying to get the total hourly time, based on a condition, I have a timestamp column and a state column, I need to know how much time was spent in a certain type of state, I wrote this and I get this, but I would like to have the result in hours and minutes
$start2 = '2023-06-01';
$end2 = '2024-05-31';
$duration = DB::table('tickets')
->selectRaw('SUM(time_stamp) as total')
->whereIn('status', ['Closed'])
->whereBetween('time_stamp', [$start2, $end2])
->distinct('tickets_id')->get();
dd($duration );
Illuminate\Support\Collection {#3163 ▼ // app/Http/Controllers/TicketsController.php:1912
#items: array:1 [▼
0 => {#3164 ▼
+"totale": "40461322269856"
}
]
1 like
Level 63
@marcoplus This is another subject, you should create a new post for it.
2 likes
Level 6
@ghabe thanks for your reply, but it gives me an error
I tried to add the timezone but it's the same
Expected type 'object'. Found 'string'.intelephense(P1006)
Create a Carbon instance from a timestamp and set the timezone (use default one if not specified)
Carbon::createFromTimestamp($duration->first()->totale)->format('H:i')->toTimeString();
Carbon::createFromTimestamp($duration->first()->totale)->format('H:i')->timezone(config('app.timezone'))->toTimeString();
1 like
Level 6
@vincent15000 thanks for the report, since it is part of the same work that I am trying to do I thought I would write it here, I will keep it in mind for future questions. I am facing something that I have never done and therefore I am a bit lost, I have never worked on reports and I have never generated counts before.
1 like
Level 6
@ghabe thanks for your answers, nothing I still can't do it, I don't understand where the error is,
Carbon::createFromTimestamp($duration->first()->totale)->format('H:i');
Unexpected data found. Trailing data
the column format is like datetime and the values are like this in the database: 2025-03-29 14:33:52 I have to count the time based on the status, if it is closed, insert the time count if it is open insert in the time count but divided. I need to know how much total time has passed between open and closed on a given date. I thought I had written the code well but it's not like that, there are some errors and I can't find them
Carbon::createFromTimestamp('Y-m-d H:i:s',$duration->first()->totale)->timezone(config('app.timezone'))->format('H:i');
Absolute timezone offset cannot be greater than 100.
1 like
Level 75
@marcoplus not your data here but just an example:
$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
I suggest study some basics here: https://www.mysqltutorial.org/
1 like
Level 63
Have you seen my suggestion of code for the query ?
$summariesCount = Summary::
where('description', 'LIKE', '%closing%')
->where('description', 'NOT LIKE', '%opening%')
->count();
1 like
Level 6
I don't understand anything, it's complex what I have to do but I think the solution can be simple. Two tables are connected by id. I have to create a report where I can see the total counts, the time spent. In one table I have to search for certain values and then filter based on established conditions. In the other table based on this I have to take only what is filtered and then perform the total counts, if I create a pivot table, how can I automatically add the data to the table? Could it be the right solution? if in the pivot table I can add the relationships automatically I receive the counts, I tried and I'm trying but it takes too long to get the answer, I don't know how you can create automatic reports based on specific conditions and that's what I need to do. I have to filter in thousands of data ninety-two specific values and receive the count of six columns, I wrote the code but replicating it for 92 x 6 becomes infinite and impossible
$test = Model::join('table', 'table2.tickets_id', '=', 'table.ticket_id')
->whereBetween('table2.time_stamp',[$start, $end])
->where(function($query) {
$query->where('table.description', '=', 'Search Term')
->orwhere('table.resource', '=', Search Term')
->orwhere('table.resource_description', '=', 'Search Term')
->orwhere('table.code', '=', 'Search Term');
})
->with('filert','filter2')
->distinct('table2.tickets_id')
->count();
1 like
Level 63
@marcoplus A pivot table is only useful if you have a many to many relationship. It doesn't seem to be your case.
Have you tried the code I suggested you in my previous comment ?
Level 6
@vincent15000 thanks for your answers
the problem is that I have to search for 92 specific values, the code becomes immense and too much thought as a calculation. I have two tables and they are connected with the same id which is a unique number. in a table I have to see if there is a specific value and there are 92 in total and then once found I have to count
value 1 column 1 total, column 2 total, column 3 total, column 4 total, column 5 total, column 6 total,
value 2 column 1 total, column 2 total, column 3 total, column 4 total, column 5 total, column 6 total,
...
...
I have to create a report to count the quantity and time spent. there are 92 and I have to return the result of 6 columns with the totals of each single value of the 92
Please or to participate in this conversation.