Laravel multiple foreign keys get specific value using querybuilder-CodePudding

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.

CodePudding user response：

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

CodePudding user response：

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.