How to write complex inner join on Query builder?

I am trying to write this on query builder but I have no idea, someone know if this is possible ?

SELECT
           *
        FROM
            attendances a
            INNER JOIN remunerations rr ON rr.id = (
                    SELECT
                        id
                    FROM
                       temporaryTable
                LIMIT 1)

The whole problematic is after the ON

kalemdzievski

5 years ago

Level 6

Why are u writing a join, when u are not joining the table attendances a with remunerations rr ?

But anyways, you can write a complex join like this:

->join('remunerations', function ($join) {
     $join->on('remunerations.id', '=', function ($query) {
         $query->select('id')
             ->from('temporaryTable')
             ->limit(1);
     });
})

Lonw

5 years ago

Level 2

@kalemdzievski thanx for the answer.

In fact it's a join from another table that I didn't show it here, in order to keep simple.

I tried it doesn't works: stripos() expects parameter 1 to be string, object given Illuminate\Database\Query\Grammars\Grammar::Illuminate\Database\Query\Grammars\{closure}(["Column", "remunerations.id", "=", Object(Closure), "and"])

kalemdzievski

5 years ago

Best Answer

Level 6

@lonw my bad. Can u try this:

->join('remunerations', function ($join) {
     $join->where('remunerations.id', '=', function ($query) {
         $query->select('id')
             ->from('temporaryTable')
             ->limit(1);
     });
})

Lonw

5 years ago

Level 2

It works like a charm. Thank you :D

1 like

Please or to participate in this conversation.