如何在Laravel Eloquent中将内部联接与子查询一起使用 [英] How to use inner joins with subqueries in Laravel Eloquent

问题描述

注意:这是laravel 5.3

基本上，当用户选择阿拉伯语翻译时，我正在运行查询.完整的sql看起来像这样

Basically I'm running a query when a user selects arabic translation.. the full sql looks like this

select s.ref, t.text as ref_ar 
  FROM stores AS s 
  INNER JOIN 
     (SELECT item, text 
      FROM translator_translations 
      WHERE locale ='ar' 
        AND namespace ='*' 
        AND item like 'store.ref%'
      ) AS t 
   ON substring(s.ref_translation from 14 for 26) = t.item;

在Laravel官方文档中没有看到太多有关子查询的文档(有内部连接的东西，但还不够好)，所以SO的建议似乎是特别hack ..的建议?

don't see much documentation on subqueries on the official Laravel docs (there are inner join stuff but not good enough) and the SO advice seems extra-hacky.. advice?

这将用作模型内的作用域，因此，例如:

this will be used as a scope inside a model, so this works for example:

public function scopeFilterLanguage($query, $language_id)
{
    if (!$language_id || intval($language_id) != LanguageConstants::ARABIC_LANGUAGE_ID) {
        return $query;
    }
    return $query->whereRaw("
    substring(ref_translation from 14 for 26) in 
                                    (select item 
                                     from 
                                     translator_translations 
                                     where 
                                     locale ='ar' and namespace ='*' 
                                     and 
                                     item like 'store.ref%')");

}

但是它没有给我我想要的东西. (即，我必须在本文开头使用较大的版本)

but it doesn't give me what i want. (ie i have to use the bigger version at the start of this post)

推荐答案

这可行(忽略动态内容，例如this-> getClassName等)..基本逻辑工作正常

this worked (ignore the dynamic stuff like this->getClassName etc).. the basic logic works just fine

public function scopeAddTranslations($query)
{
    $t = new Translation();

    $subq = $t->select('item','text as ref_ar')
              ->where('locale','=','ar')
              ->where('item','like',$this->getClassName().'.ref%');

    $query->leftjoin(\DB::raw('('.$subq->toSql().') as t'), 
      function ($join) use ($subq) { 
          $join->on(\DB::raw('SUBSTRING('.$this->getTable().'.ref_translation 
                              FROM 14 FOR 26)'),
                                 '=',
                                 \DB::raw('t.item'))
                   ->addBinding($subq->getBindings());
            });
    return $query;
}

这篇关于如何在Laravel Eloquent中将内部联接与子查询一起使用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！