How to Join same table in laravel

Question

I wan to wirte a join query to connect same table, and without ON, but when i write it in laravel without on it is showing error

$key = DB::table('api_keys as ak')  
->join('api_keys as bk','') 
              ->where('ak.api_key', $api_key)->where('ak.user_id',0)
              ->pluck('api_key');

want to build the below query,

SELECT * FROM `api_keys` as ak
  JOIN `api_keys` as bk
 WHERE ak.`api_key`=$akey
   and ak.`user_id`=$auser
   and bk.`user_id`=$bsuer
   and bk.`api_key`=$bkey

score 2 · Answer 1 · edited May 23 '17 at 10:29

You must provide an ON clause for the join. More about where ON clauses are required can be found in this answer.

You can view the generated query using toSql() on a QueryBuilder object:

echo $key = DB::table('api_keys as ak')  
    ->join('api_keys as bk','') 
    ->where('ak.api_key', $api_key)->where('ak.user_id',0)
    ->toSql();

Which in your case returns:

select * from `api_keys` as `ak` inner join `api_keys` as `bk`
on `` `` where `ak`.`api_key` = ? and `ak`.`user_id` = ?

In your case it isn't totally clear what you are trying to achieve, but you might consider joining on api_key or the primary key of the api_keys table, if that is different:

$key = DB::table('api_keys as ak')  
    ->join('api_keys as bk','ak.api_key', '=', bk.api_key) 
    ->where('ak.api_key', $api_key)->where('ak.user_id',0)
    ->pluck('api_key');

score 2 · Answer 2 · answered Apr 25 '20 at 12:58

2

DB::table('registerusers as a')
  ->join('registerusers as b', 'a.id', 'b.refer_id')
  ->where('a.username', 'b.username')
  ->where('b.id', 'a.refer_id')
  ->value('b.id');

answered Apr 25 '20 at 12:58

Praveen Soni

21
2

Don't Make too complex when it can be easy. – Praveen Soni Apr 25 '20 at 12:59

Basit Munir · Answer 3 · 2016-07-14T12:39:20.303

without using on clause in laravel query builder you can use following

$key = DB::table(DB::raw('api_keys as ak, api_keys as bk'))
          ->where('ak.api_key', '=', $api_key)
          ->where('ak.user_id','=',0)
          ->where('ak.PK','=','bk.PK')
          ->pluck('ak.api_key')

where PK references to your table's primary key. result will in your case.

select * from api_keys as ak, api_keys as bk where ak.api_key= 'api_key_value' and ak.user_id = 0 and ak.PK = bk.PK

score 0 · Answer 4 · answered Jan 02 '19 at 08:31

I solved this by creating my own class and starting out with a base query which I modify to apply the join (using Laravel's joinSub function) as follows:

public function __construct()
{
     $this->query = DB::table('question_responses as BASE');
}

public function applyFilter($questionId, $questionValue) {
    $filterTableStr = 'filter_table_'.$questionId;
    $filterIdStr = 'filter_id_'.$questionId;
    $filterQuery = DB::table('question_responses AS '.$filterTableStr)
        ->select('survey_response_id AS '.$filterIdStr)
        ->where($filterTableStr.'.question_short_name', $questionId)
        ->where($filterTableStr.'.value', $questionValue);

    $resultTableStr = 'result_table_'.$questionId;
    $this->query = $this->query
        ->joinSub($filterQuery, $resultTableStr, function($join) use ($resultTableStr, $filterIdStr) {
            $join->on('BASE.survey_response_id', '=', $resultTableStr.'.'.$filterIdStr);
        });
}

After applying my required filters I can just call $this->query->get() as normal to obtain the result.

The important part was to make sure that each resulting table and join fields has unique names. With this method I can apply unlimited filters to my base query.

How to Join same table in laravel

4 Answers4