ravennmasterr's avatar

migrate pdo to eloquent

im new in laravel and tring to create eloquent builder from query string using laravel 12 i have this query, it works fine and get the data i need

SELECT cComPPresentacion, cComProveedor, cComPPrecio 
FROM tComPartidas AS Par JOIN tCompras ON cComId = cComPCompra
WHERE DATE_FORMAT(cComRecibida, '%Y-%m-%d') BETWEEN '20201201' AND '20211231' 
AND cComPRecibido = cComPRequerido
AND cComPPrecio = 
	(SELECT MIN(cComPPrecio) 
	FROM tComPartidas AS Min JOIN tCompras ON cComId = cComPCompra
	WHERE Min.cComPPresentacion = Par.cComPPresentacion 
	AND cComPRecibido = cComPRequerido
	AND DATE_FORMAT(cComRecibida, '%Y-%m-%d') BETWEEN '20201201' AND '20211231'

so far i got this

PurchaseItemsModel->whereColumn("needed", "recieved")
->whereHas("purchase", function($query){
        $query->whereRaw("DATE_FORMAT('recievedDate', '%Y-%m-%d') BETWEEN '2020-01-01' AND '2021-12-31'");
->where("precio", function($query){
	//something
});

but dont know how to do this

AND cComPPrecio = 
	(SELECT MIN(cComPPrecio) 
	FROM tComPartidas AS Min JOIN tCompras ON cComId = cComPCompra
	WHERE Min.cComPPresentacion = Par.cComPPresentacion 
	AND cComPRecibido = cComPRequerido
	AND DATE_FORMAT(cComRecibida, '%Y-%m-%d') BETWEEN '20201201' AND '20211231')

into this

->where("cComPPrecio ", function($query){
	//how call this comparision from new select with parent
	WHERE Min.cComPPresentacion = Par.cComPPresentacion
})

this function works fine using DB::select but want to do it with query builder

also have a similar question on eloquent relations like this

public function Parent(){
	 return $this->hasOne("App\Models\ClientsModel", "cCliSecuencia", "cCliSecuencia")
    	->where("cCliSecuencia", $this->cCliSecuencia)
        ->where("cCliSecuenciaEncadenado", 0)
        ->where("cCliID", "!=", $this->cCliID);
}

when call like

$model->with("parent")->get();

, i cant get the attributes from $this but calling like this way, it get the data propertly.

$data = $model->get();
foreach($data as $key => $value)
	$value->parent;

thanks in advance for your help, i really apreciate it

0 likes
6 replies
Tray2's avatar

Please format you code and place it between three backticks `

Much easier to read.

SELECT * 
FROM table 
WHERE col1 = 'Value';
JussiMannisto's avatar

You have 'recievedDate' in quotes instead of backticks. That means it's treated as a string in SQL, not a column name, and the DATE_FORMAT() call won't work. Either remove the quotes or replace them with backticks.

The correct spelling is received, not recieved.

Anyway, this is bad for performance:

$query->whereRaw("DATE_FORMAT(recievedDate, '%Y-%m-%d') BETWEEN '2020-01-01' AND '2021-12-31'");

If you call a function (DATE_FORMAT in this case) on a column and use its result in a condition, it means the database engine can't utilize any indexes on that column. The engine doesn't know in advance what the function will return, so it has to scan the entire table and run the function on every date value to evaluate the BETWEEN condition.

Normally, when you run a BETWEEN query on an indexed column, the database can perform a range scan by first traversing the index to find the first item, then walking through the ordered list of leaf nodes to find the last item. That's a lot faster than scanning the entire table.

Assuming that recievedDate is some type of time column, you can use a normal BETWEEN call:

$query->whereBetween('recievedDate', ['2020-01-01 00:00:00', '2021-12-31 23:59:59']);

It doesn't matter if recievedDate is a timestamp, datetime or date column: they're all just integers under the hood, and the DBMS will handle data type conversions automatically.

1 like
krisi_gjika's avatar
->where("precio", function($query) {
	$query->from('tComPartidas as min')
		->join('tCompras', 'cComId', 'cComPCompra')
		->selectRaw('MIN(cComPPrecio)')
		->whereColumn('Min.cComPPresentacion', 'Par.cComPPresentacion')
		...
		->limit(1)
});
1 like
kevinbui's avatar

I would still prefer to convert that raw database query to an Eloquent one, for readability and further refactor.

The part that you are struggling with:

AND cComPPrecio = 
	(SELECT MIN(cComPPrecio) 
	FROM tComPartidas AS Min JOIN tCompras ON cComId = cComPCompra
	WHERE Min.cComPPresentacion = Par.cComPPresentacion 
	AND cComPRecibido = cComPRequerido
	AND DATE_FORMAT(cComRecibida, '%Y-%m-%d') BETWEEN '20201201' AND '20211231')

That is a sub where clause, which can be handled by whereSub:

$subQuery = ComPartidas::selectRaw('MIN(cComPPrecio) ')
    ->join('tCompras', 'cComId', '=', 'cComPCompra')
    ->whereColumn('tComPartidas.cComPPresentacion', 'tComPartidas.cComPPresentacion')
    ->whreColumn('cComPRecibido', 'cComPRequerido')
	->whereRaw(DATE_FORMAT(cComRecibida, '%Y-%m-%d') BETWEEN '20201201' AND '20211231');

$mainQuery = ComPartidas::where(...)
    ->join(...)
    ->where(...)
    ->whereSub('cComPPrecio', '=', $subQuery);

I hope you got the idea.

Please or to participate in this conversation.