Skip to content
Advertisement

Laravel multiple foreign keys get specific value using querybuilder

I have a table of contracts and here is the example data

id     spec1        spec2          spec3
1        2            4              3

And i got another table of spec and the values are

id       text
1        hello
2        hi
3        hola
4        yow

how can i get the text from spec table? do i need to multiple join them? what I want is something like

{
 contract.id: 1,
 spec1: hello,
 spec2: yow,
 spec3: hola,
}
{
 contract.id: 2,
 spec1: hello,
 spec2: yow,
 spec3: hola,
}

im selecting from contracts table

I tried

      DB::table('contracts as c')
            ->join('spec','spec.id','c.spec1')
            ->join('spec','spec.id','c.spec2')
            ->join('spec','spec.id','c.spec3')
            ->get();

but its wrong and got some errors. Thanks in advance for the help.

Advertisement

Answer

try below code:

 DB::table('contracts')->select('contracts.id','a.text AS spec1','b.text AS spec2','c.text AS spec3')
    ->leftJoin('specs AS a', 'a.id', 'contracts.spec1')
    ->leftJoin('specs AS b', 'b.id', 'contracts.spec2')
    ->leftJoin('specs AS c', 'c.id', 'contracts.spec3')
    ->get();

MySQL documation

Read this Question

User contributions licensed under: CC BY-SA
8 People found this is helpful
Advertisement