I am working with PHP and Laravel 6 and I need to query multiple subqueries. The subquerys are on the same table and I can't do it with a join (or I can't think of a way to do it).
I need to do this query:
select t.nombre, t.anio, (select v.value form Valor v where v.id=t.tipo) as tipo, (select v.value form Valor v where v.id=t.centro) as centro from Titulo
I have tried to do something like that but it doesn't work:
$query = trim($request->get('searchText'));
$titulos = DB::table('titulo as t')
->select('t.nombre','t.anio')
->DB::raw('(select v.value from Valor v where t.tipo = v.id) as tipo'))
->paginate(7);
Could you help me?
Thank you very much, I read you!
You could make use of the selectSub
function
$query = trim($request->get('searchText'));
$titulos = DB::table('titulo as t')
->select('t.nombre','t.anio')
->selectSub(function ($query) {
$query->from('Valor')
->whereColumn('Valor.id', 'titulo.tipo')
->select('Valor.value')
->latest()
->take(1);
}, 'tipo')
->selectSub(function ($query) {
$query->from('Valor')
->whereColumn('Valor.id', 'titulo.centro')
->select('Valor.value')
->latest()
->take(1);
}, 'centro')
->paginate(7);