如何将复杂查询“翻译”到Laravel Query Builder？

Question

I have following complex query and would like to use it in Laravel. But until now I had no success. Maybe one of you could show how to "translate" it to the query builder of Laravel.

Here the original query that in Workbench works well:

select * from lemma l 
inner join etymology e on l.id=e.lemma_id_fk 
inner join gloss g on e.id = g.etymology_id 
inner join wold_meanings w on g.gloss=w.meaning
where g.gloss like '%flower%' #paper, ocean, etc.
limit 100;

Than I tried the following:

  $results = DB::table('lemma')
  ->join('etymology', 'lemma.id', '=', 'etymology.lemma_id_fk')
  ->join('gloss', 'etymology.id', '=', 'gloss.etymology_id')
  ->join('wold_meanings', 'gloss.gloss', '=', 'wold_meanings.meaning')
  ->select(DB::raw('lemma.*'))
  ->where('gloss.gloss', 'like', '%flower%')
  ->get();

and also:

  $results = DB::table('lemma')
  ->select(DB::raw("select * from lemma l 
  inner join etymology e on l.id=e.lemma_id_fk 
  inner join gloss g on e.id = g.etymology_id 
  inner join wold_meanings w on g.gloss=w.meaning
  where g.gloss like '%flower%'
  limit 1"));

and this too:

  $results = DB::raw("select * from lemma l 
  inner join etymology e on l.id=e.lemma_id_fk 
  inner join gloss g on e.id = g.etymology_id 
  inner join wold_meanings w on g.gloss=w.meaning
  where g.gloss like '%flower%'
  limit 100");

But I had absolutely no succes... :-(

Anyone to show me the right way? I'm using Laravel 5.4.

== EDIT #1 ==

Here the view where the results should be shown:

        <table class="table table-bordered table-condensed table-hover table-responsive table-striped" id="table">
            <thead>
                <tr>
                    <th>id</th>
                    <th>lemma_id</th>
                    <th>headword</th>
                    <th>lemma</th>
                    <th>pos</th>
                    <th>gender</th>
                    <th>language</th>
                    <th>origin_family</th>
                    <th>origin</th>
                    <th>short_path</th>
                    <th>origin_path</th>
                    <th>etymology_text</th>
                    <th>first_use</th>
                    <th>lang</th>
                    <th>pageid</th>
                    <th>term</th>
                    <th>non_latin_script</th>
                    <th>lang_2</th>
                    <th>gloss</th>
                    <th>sequence</th>
                    <th>lemma_id_fk</th>
                    <th>derivatives_id</th>
                    <th>etymology_id</th>
                    <th>meaning</th>
                    <th>semantic_category</th>
                    <th>semantic_field</th>
                    <th>simplicity_score</th>
                    <th>age_score</th>
                    <th>borrowed_score</th>
                    <th>description</th>
                    <th>typical_context</th>
                    <th>representation</th>
                    <th>sub_code</th>
                </tr>               
            </thead>

            <tbody>
            @foreach($results as $result)
                <tr>
                    <td>{{$result->id}}</td>
                    <td>{{$result->lemma_id}}</td>
                    <td>{{$result->headword}}</td>
                    <td>{{$result->lemma}}</td>
                    <td>{{$result->pos}}</td>
                    <td>{{$result->gender}}</td>
                    <td>{{$result->language}}</td>
                    <td>{{$result->origin_family}}</td>
                    <td>{{$result->origin}}</td>
                    <td>{{$result->short_path}}</td>
                    <td>{{$result->origin_path}}</td>
                    <td>{{$result->etymology_text}}</td>
                    <td>{{$result->first_use}}</td>
                    <td>{{$result->lang}}</td>
                    <td>{{$result->pageid}}</td>
                    <td>{{$result->term}}</td>
                    <td>{{$result->non_latin_script}}</td>
                    <td>{{$result->lang_2}}</td>
                    <td>{{$result->gloss}}</td>
                    <td>{{$result->sequence}}</td>
                    <td>{{$result->lemma_id_fk}}</td>
                    <td>{{$result->derivatives_id}}</td>
                    <td>{{$result->etymology_id}}</td>
                    <td>{{$result->meaning}}</td>
                    <td>{{$result->semantic_category}}</td>
                    <td>{{$result->semantic_field}}</td>
                    <td>{{$result->simplicity_score}}</td>
                    <td>{{$result->age_score}}</td>
                    <td>{{$result->borrowed_score}}</td>
                    <td>{{$result->description}}</td>
                    <td>{{$result->typical_context}}</td>
                    <td>{{$result->representation}}</td>
                    <td>{{$result->sub_code}}</td>
                </tr>
            @endforeach
            </tbody>
        </table>

== EDIT #2 ==

Just did some other tests here and realised that when using

  $results = DB::table('lemma')
  ->join('etymology', 'lemma.id', '=', 'etymology.lemma_id_fk')
  ->join('gloss', 'etymology.id', '=', 'gloss.etymology_id')
  ->join('wold_meanings', 'gloss.gloss', '=', 'wold_meanings.meaning')
  ->select(DB::raw('lemma.*'))
  ->where('gloss.gloss', 'like', '%flower%')
  ->get();

it gives me the result only from the table lemma, i.e., it ignores the joins. So I receive results in the table of the view from <td>{{$result->id}}</td> until <td>{{$result->pageid}}</td> but not the rest.

Answer 1

Removing the call to select() from the chain will make it work.

The select method represents the SELECT part of a query. This way, writing the following:

->select(DB::raw('lemma.*'))

It's the same as:

SELECT lemma.* FROM ...

In other words, you're only selecting the columns from lemma table.

However, since you want to also select the columns from other tables, omitting that part of your chain will make the Query Builder fallback to its default behavior, which is the same as:

SELECT * FROM ...

If you want to be explicit about what you're selecting, doing this will achieve the same:

->select('*')