dph23577
2018-02-12 12:54
浏览 208
已采纳

如何在Laravel Eloquent中使用内部联接和子查询

Note: this is laravel 5.3

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;

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?

context

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)

图片转代码服务由CSDN问答提供 功能建议

注意:这是laravel 5.3

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

 选择s.ref,t.text为ref_ar 
 FROM stores AS  s 
 INNER JOIN 
(选择项目,文本
 FROM translator_translations 
 WHERE locale ='ar'
 AND namespace ='*'
 AND项目如'store.ref%'
)AS t 
  ON substring(从14开始的s.ref_translation为26)= t.item; 
   
 
 

在官方的Laravel文档上看不到关于子查询的文档很多(有内部的 加入东西,但还不够好)和SO建议似乎超出hacky ..建议?

context

这将用作内部范围 一个模型,所以这适用于例如:

  public function scopeFilterLanguage($ query,$ language_id)
 {
 if if(!$ language_id || intval($ language_id)  != LanguageConstants :: ARABIC_LANGUAGE_ID){
返回$ que  ry; 
} 
在
中返回$ query-> whereRaw(“
 substring(ref_translation from 14 for 26)”(从
 translator_translations 
中选择项目
其中
 locale ='ar'和命名空间 ='*'
和
项目如'store.ref%')“); 
 
} 
   
 
 

但它不会给我 我想要的是。 (即我必须在本文开头使用更大的版本)

  • 写回答
  • 好问题 提建议
  • 关注问题
  • 收藏
  • 邀请回答

2条回答 默认 最新

  • dongwen2794 2018-02-14 19:57
    已采纳

    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;
    }
    
    已采纳该答案
    评论
    解决 无用
    打赏 举报
  • dslkchyv673627 2018-02-12 13:47

    Here's my completely untested and best guess effort.

    public function scopeFilterLanguage($query, $language_id)
    {
        if (!$language_id || intval($language_id) != LanguageConstants::ARABIC_LANGUAGE_ID) {
            return $query;
        }
        return $query->join('translator_translations', function($join) {
            $join->selectSub(function($q) {
                   $q->where('t.locale' => 'ar')
                   $q->where('t.namespace', '*')
                   $q->where('t.item', 'like', $this->ref . '%')
              }, 't');
         })->on('t.item', '=', substr($this->ref_translation, 14, 26))
           ->select('t.text', 'ref');
    }
    
    评论
    解决 无用
    打赏 举报

相关推荐 更多相似问题