phplaravelsubquerymultiple-select

How to make multiple subquery in Laravel 6?


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!


Solution

  • 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);