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.

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

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

Method 2

DB::table('contracts as c')
        ->leftjoin('spec as s1', 's1.id', '=', 'c.spec1')
        ->leftjoin('spec as s2', 's2.id', '=', 'c.spec2')
        ->leftjoin('spec as s3', 's3.id', '=', 'c.spec3')
        ->select('c.id as contract.id', 's1.text as spec1', 's2.text as spec2', 's3.text as spec3')
        ->get();

Let me know the results.

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0