CODEWITHSUNDEEP
laraveljoininner-joinlaravel-5
Thomas
Thomas

Reputation: 11

How to Join same table in laravel

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

Upvotes: 1

Views: 16165

Answers (4)

Praveen Soni
Praveen Soni

Reputation: 31

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');

Upvotes: 2

HPage
HPage

Reputation: 1503

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.

Upvotes: 0

Basit Munir
Basit Munir

Reputation: 438

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

Upvotes: 1

Superfly
Superfly

Reputation: 601

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');

Upvotes: 2

Related Questions