Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.
I don't know how to group a query by date with datetime field, so I create two fields: $dia = date('Y-m-d'); $hora = date('H:i'); tbpedido::insert(['dia'=>$dia,'hora'=>$hora]);
In tbpedido Model, I have a relatioship like this: function details(){return $this->hasMany(tbpedido::class,'dia','dia');}
Instead to work with two fields, I think to work with just one, where $dia = date('Y-m-d H:i'), but I don't know how create a relatioship that group by date and not by date-time.
You can for example filter the relationship like this.
public function details()
{
return $this->hasMany(tbpedido::class,'dia')->whereDate(...);
}
Furthermore you should name all your variables, classes, ... with respect to the Laravel naming convention.
Well, i tried to work with table test (id,dia)(1,"2023-07-30 17:28:11")(2,"2023-07-30 17:28:11")(3,"2023-07-30 17:28:11")
In test Model, i tried this
function details(){return $this->hasMany(test::class,'dia')->whereDate('dia','=','date(dia)');}
In controller, I tried this
$teste=test::with('details')->get(); dd($teste[0]->details);
but I got nothing. What did I do wrong?
@frankhosaka Without having more details about the tables, it's difficult to help you.
$this->hasMany(test::class,'dia'); // having dia here seems strange
Remember keys should be unique.
Is that true for your date field?
I'm confused what you are trying to do and why you want to use date as a relationship key?
If I've read this correctly it is a similar ask as that in the world of datawarehousing where you would create a relationship with a date dimension or something like that. For speed, typically, you would collapse the date to an integer like 20230720, you'd do the same for time.
In sumary, what I'm suggesting is, add a new interger column for date and populate that with the date part of dateTime. You can always extrapolate the date somewhere else.
@robj What's the interest to do that ?
I have many invoices in same day, but i want show each date with respective invoices. It is possible to show this if i have two fields, one for day e and another for hour.
But, if i work with just one field with datetime, it is very difficult to create a relationship that group many invoices in same date(day), because the table has not a field called date(day). Meanwhile, the better is work with two fields.
@frankhosaka You don't need to create a relationship but a query to retrieve only the invoices for on specific date. And it's not a problem with a datetime field.
@frankhosaka not sure how exactly you want the results, but you can do something like this:
Invoice::whereDate('dia', '2023-07-30')->get();
// Or raw grouping
Invoice::groupByRaw('DATE(dia)')->get();
I tried
public function index(Request $request){
$groups=tbpedido::groupByRaw(date('horavenda'))->get();
dd($groups);}
and I got:
SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Jul 2023 04:52:32 -0300am000America/Sao_Paulo731am' at line 1
I changed config/app.php 'timezone'=>'UTC' and run composer update, and I got this:
SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Jul 2023 08:01:43 +0000am000UTC731am' at line 1
But it isn't important. What I need is to create a relation to tbpedido like this:
function details(){return $this->hasMany(tbpedido::class,'horavenda','horavenda');}
So, I will have $group->detail to list every invoice for each date, but I can't because 'horavenda' is datetime type and not time type.
Even so, nothing is impossible. I have an option: I may make a query inside a Blade and not in Controller, but I don't whish this. So I create two fields in tbpedido: vendido (date field) and horavenda (datetime field), two fields with almost same information.
@frankhosaka Your comment is unreadable, please write all your code inside back ticks.
Do you know what the second and third arguments are for in this function ?
->hasMany(tbpedido::class,'horavenda','horavenda');
Can you show the structure of these tables : tbpedido and details ?
Well, I start with MySQL
CREATE TABLE `tbpedido` (
`ped` int NOT NULL AUTO_INCREMENT,
`total` float DEFAULT NULL,
`dia` date DEFAULT NULL,
`codp` mediumint DEFAULT NULL,
`horavenda` datetime DEFAULT NULL,
`vendido` date DEFAULT NULL,
`dinheiro` float DEFAULT NULL,
`troco` float DEFAULT NULL,
`cartao` tinyint NOT NULL,
`pix` tinyint NOT NULL,
PRIMARY KEY (`ped`)
) ENGINE=InnoDB AUTO_INCREMENT=950 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
The Model tbpedido.php:
<?php
namespace App\Models;
use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;
use Carbon\Carbon;
class tbpedido extends Model {
use HasFactory;
public $timestamps=false;
protected $table="tbpedido";
protected $fillable=['ped','dia','vendido'];
function details(){return $this->hasMany(tbpedido::class,'vendido','vendido');}
function pessoas(){return $this->hasMany(tbpessoa::class,'codp','codp');}
}
TesteController.php
<?php
namespace App\Http\Controllers;
use Illuminate\Http\Request;
use App\Models\tbpedido;
use App\Models\test;
Use DB;
class TesteController extends Controller {
public function index(Request $request){
$groups=tbpedido::whereNotNull('vendido')
->select('vendido',DB::raw("sum(total) as total"))
->groupBy('vendido')->orderBy('vendido','desc')->get();
$vendamensal=tbpedido::whereNotNull('vendido')->
select(DB::raw('date_format(vendido,"%Y %m") as mes, sum(total) as total'))->
groupBy('mes')->orderBy('mes','desc')->get();
return view('teste',compact('groups'),['vendamensal'=>$vendamensal]);}
}
teste.blade.php
@include('menu') // I use this to load Bootstrap CSS
<script>
document.title="Relatório de Vendas"
btmenu.innerHTML="Relatório de Vendas"
function alternar(e){
if(e.className=='table table-striped d-block'){
e.className='table table-striped d-none'} else {
e.className='table table-striped d-block'}}
</script>
<i>Para ver detalhes do dia clique no dia</i><br>
@foreach($vendamensal as $venda)
mês {{$venda->mes}} => R$ <font color=red> {{dec($venda->total)}}</font><br>
@endforeach
<table><tr><th>Dia<th style='text-align:right'>Total
@foreach($groups as $group)
@php $dia=date('Y-m-d',strtotime($group->vendido));$id="t".str_replace('-','',$dia); @endphp
<tr onclick="alternar({{$id}})"><td><b>{{date('d/m/y',strtotime($group->vendido))}}
<td align=right><b>{{dec($group->total)}}
<tr><td><table id={{$id}} class="table table-striped d-none"><th>{{$group->dia}}<th>Pedido<th>Total<th>dinheiro<th>troco<th>cartão<th>pix
@foreach($group->details as $detail)
<tr><td align=right>{{date('H:i',strtotime($detail->horavenda))}}
<td align=right>{{$detail->ped}}
<td align=right>{{dec($detail->total)}}
<td align=right>
@if(abs($detail->dinheiro)>0)
{{dec($detail->dinheiro)}} // dec is a helper function that changes "." to ","
@endif
<td align=right>
@if(abs($detail->troco)>0)
{{dec($detail->troco)}}
@endif
<td align=center>@if($detail->cartao==1) x @endif
<td align=center>@if($detail->pix==1) x @endif
@endforeach
</table>
@endforeach
</table>
The trouble is if is possible to work with one field (horavenda, datetime) instead two fields (vendido, date) and (horavenda, datetime). Thanks.
@frankhosaka Oh I just noticed something.
You are setting a relationship to pbpedido inside the pbpedido model ? So it is a relationship from a model to the same model, isn't it ?
@frankhosaka I think that the declaration of the relationship is false.
return $this->hasMany(Comment::class, 'foreign_key', 'local_key');
The first argument is the related class, the second argument is the foreign_key and the third argument is the local key.
https://laravel.com/docs/10.x/eloquent-relationships#one-to-many
You are trying to define a relationship using a date as foreign / local key ? This has no sense.
I don't think that this is a relationship problem. What are you really trying to do ? You need to retrieve the tbpedido within a start date and an end date without taking into account the time ? Something else ?
details is the relationship of tbpedido, defined in Model. It is a resource to send tbpedido from controller to view, where each date has respective invoice.
@frankhosaka A relationship can't be a resource to send the datas from the controller to the view.
@frankhosaka According to me, you don't need to write any relationship in this case, you only need to write a query to group the tbpedido by date.
Hahahahaha.... how?
I know that is possible to group by date, but how i retrieve the invoices from date without relationship in the Controller and not in View?
Well, you are right. I group by date and send to view. If someone wish details, he click the date, and the controller make another query, and send to view. So, it is very possible to work with only one saled day and hour field. That is simple and great! Thanks a lot, vincent15000!
@frankhosaka You can't have any relationship in the controller or in the view, the relationship is defined in the model.
The problem is that you are creating a relationship from the tbpedido to the tdpedido model, a relationship inside the same model only to retrieve some datas grouped by date. That's not the solution.
Do you have an invoice model ? If yes, you should perhaps create a relationship from the tbpedido model to the invoice model.
Perhaps the problem is about the structure of the database ?
@vincent15000 Sure. My MySQL is a great confusion, so my Laravel is another confusion. I don't know what is "framework", "database", "html", and so on. I don't speak English, but I apreciate the programing multiverse since Basic from DOS, Visual Basic from Office, and now PHP. It's fantastic. It works. It works better if you know English and Logic. And I have none.
@frankhosaka Oh you are a beginner ? It's never too late to learn ;).
Have you had a look at the different series on Laracast to learn MySQL, PHP, Laravel, ... ?
Please or to participate in this conversation.