How to make multiple subquery in Laravel 6?

Question

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!

Answer 1

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