Laravel 5.3 doing join on pivot table with same models

I posted a previous question seeing if what I'm trying to do could be done with eloquent relationships and it looks like it isn't possible ( Laravel 5.3 Many to many relationship with same model ) .

So now I'm trying to write the query with query builder and have this:

return $relationships = DB::table('landlord_contact_landlord_contact')
            ->where('landlord_contact1_id', $id)
            ->orWhere('landlord_contact2_id', $id)
            ->join('landlord_contacts', function($join){
                $join->on('landlord_contacts.id', '=' , 'landlord_contact_landlord_contact.landlord_contact1_id');
            })
            ->get(['landlord_contact_landlord_contact.id AS relationshipID','landlord_contact_landlord_contact.type' , 'landlord_contacts.*']);

Lets say I have a contact named Jim with an id of 1

And another contact named Tom with an id of 2

What I'm trying to do is have each contact have relationships with other contacts. The table "landlord_contact_landlord_contact" is the pivot table and has "landlord_contact1_id" and "landlord_contact2_id" to join the 2 contacts. Now if I create a record, connecting Jim to Jon and have landlord_contact1_id = 1 and landlord_contact2_id = 2 that's great. But how can I make the query work on both columns so I don't have to create 2 records for each relationship.

I want to search on both columns for the current landlord's id and then do a join either on landlord_contact1_id or landlord_contact2_id depending on which one isn't the current landlords ID.

Note : In my previous post, I used the table name User, but have since renamed everything to Landlord Contacts

from Newest questions tagged laravel-5 - Stack Overflow http://ift.tt/2lPRZ2N
via IFTTT