laravel eloquent subquery same table different columns

Question

i am trying to re-construct a working mysql query (or something better with the same result) in laravel eloquent. My db looks like this:

I have a words table with columns id and name. I have a pivot table called synonyms with columns id, word_id and synonym_id.

The synonyms table links words together that are synonyms. It links for example a bike and cycle together. But also bike to cycle etc.

I did manage to build this query in my phpstorm editor and it works:

select * from words
where id in (
    select synonym_id from synonyms
    where word_id in (select id from words where name = 'bike')
)
or id in (
    select word_id from synonyms
    where synonym_id in (select id from words where name = 'bike')
)

It returns a result set containing cycle, tandem, velocipede etc.

I did create a "word" eloquent model that has these methods:

 /**
 * Synonyms for this word
 *
 * @return \Illuminate\Database\Eloquent\Relations\BelongsToMany
 */
public function synonyms() {
    return $this->belongsToMany(Word::class, 'synonyms', 'synonym_id', 'word_id');
}

/**
 * Words that have this word as their synonym
 *
 * @return \Illuminate\Database\Eloquent\Relations\BelongsToMany
 */
public function isSynonymFor() {
    return $this->belongsToMany(Word::class, 'synonyms', 'word_id', 'synonym_id');
}

And now I am trying to rebuild that query in eloquent like this:

    $word = 'bike'; //Just for debugging purposes I hardcoded it.
    $query = Word::whereHas('isSynonymFor', function(Builder $query) use($word) {
        $query->where('name', '=', $word);
    })->orWhereHas('synonyms', function(Builder $query) use($word) {
        $query->where('name', '=', $word);
    })

    return $query->get();

This does not work like I want it too. It just returns a collection with "bike" in it and not the rest.

How can I fix it?

Answer 1

If you only want to reproduce that exact query (no eloquent relationships used), you should be able to do it just fine with whereIn(column, Closure) for the subqueries.

$query = DB::table('words') 
    ->whereIn('id', function ($sub) {
        $sub->select('synonym_id')
            ->from('synonyms')
            ->whereIn('word_id', function ($sub2) {
                $sub2->select('id')
                     ->from('words')
                     ->where('name', 'bike');
            });
    })
    ->orWhereIn('id', function ($sub) {
        $sub->select('word_id')
            ->from('synonyms')
            ->whereIn('synonym_id', function ($sub2) {
                $sub2->select('id')
                     ->from('words')
                     ->where('name', 'bike');
            });
    })
    // ->toSql();
    ->get();