Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

hcastillo's avatar

Convert MySQL to Laravel query builder

Hi, i wonder if anyone could helpme to transform this query to laravel. I try but dont have any idea about the from part

SELECT
out_info.Id_funcionario, out_info.Primer_nombre, out_info.Primer_apellido,  out_info.Date,
m.Id_Marcacion,
h.Hora_inicial , h.Hora_final,
 cm.Descripcion as Permiso_Descripcion,
sr.vacacion,
re.permiso
from 
(Select DISTINCT  info.Id_funcionario, d.Date, info.Primer_nombre,info.Primer_apellido  from accionespersonaldb.Informacion_funcionario info, 
	  ( WITH recursive Date_Ranges AS (
    	select '2022-11-22' as Date
   		union all
   		select Date + interval 1 day
   		from Date_Ranges
   		where Date < '2022-11-25')
		select * from Date_Ranges ) as d) as out_info
Left join accionespersonaldb.Marcaciones m ON (m.Id_funcionario = out_info.Id_funcionario AND out_info.Date = Date(m.Fecha_hora))
LEFT JOIN  accionespersonaldb.Datos_laborales dl ON (dl.Fdl_Id_funcionario = out_info.Id_funcionario)
	INNER JOIN accionespersonaldb.Horario h 		  ON (h.Horario_codigo = dl.Fdl_Horario_codigo)
	LEFT JOIN 	(SELECT sr2.Fsvr_Id_funcionario, 1 as vacacion FROM accionespersonaldb.SV_registro sr2  WHERE sr2.Fecha_inicio <=  "2022-11-25" AND sr2.Fecha_hasta >= "2022-11-22") 
		as sr ON (sr.Fsvr_Id_funcionario = out_info.Id_funcionario) 
	LEFT JOIN ( SELECT registro.Fsp_Id_funcionario, 1 as permiso, registro.Resumen as Permiso_Resumen, registro.Justificacion as Permiso_Justificacion, registro.Fecha_inicio as Permiso_Fecha_inicio, registro.Fecha_fin as Permiso_Fecha_fin, registro.Fsp_Id_motivo FROM accionespersonaldb.Sp_Registro registro WHERE registro.Fecha_inicio >=  "2022-11-30 00:00:00" AND registro.Fecha_fin <= "2022-11-30 23:59:59")
		as re ON (re.Fsp_Id_funcionario = out_info.Id_funcionario) 
	LEFT JOIN accionespersonaldb.C_Motivo cm ON (re.Fsp_Id_motivo = cm.Id_motivo)
Where dl.Fdl_Id_estatus = 'A'
and m.Id_Marcacion is null
0 likes
4 replies
Tray2's avatar

@hcastillo In a database view no, but you can query it like you would any other table.

Take this view for example which is quite complex.

https://github.com/Tray2/mediabase/blob/main/database/migrations/2022_09_25_223034_create_book_index_views.php

Due to the way that view is written I can use eloquent on it to order by data and limit it as needed.

As you can see here

https://github.com/Tray2/mediabase/blob/main/app/Http/Controllers/Books/BooksIndexController.php

Please or to participate in this conversation.