Laravel - Join with a table with composite primary key

Question

My problem is to join 2 tables in Laravel framework. One is dynamic name table (it's a variable) and second has composite primary key. I have to use query builder instead of where(). Please view my following for details:

I have 2 tables:

CREATE TABLE `details` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `source_id` int(10) unsigned NOT NULL,
  `brand_id` int(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
);
CREATE TABLE `links` (
  `source_id` int(10) unsigned NOT NULL,
  `brand_id` tinyint(3) unsigned NOT NULL DEFAULT '1',
  PRIMARY KEY (`source_id`,`brand_id`)
);

Now, I need to join 2 these tables, I use this code:

<?php $results =  \DB::table('details')
            ->join('links', function($join)
            {
                $join->on('details.source_id', '=',  'links.source_id');
                $join->on('details.brand_id','=', 'links.brand_id');
            })
            ->get();?>

It's quite simple to join these table, OK. But my problem is the table name is dynamic.

<?php 
$type = Input::get('type', null);
$table = $type . '_details';
$results =  \DB::table($table)
                ->join('links', function($join)
                {
                    // the following code will show errors undefined $table
                    $join->on($table . '.source_id', '=',  'links.source_id');
                    $join->on($table . '.brand_id','=', 'links.brand_id');
                })
                ->get();

?>

Please help me to solve this problem. Many thanks!!!

Answer 1

You need to import variables from the local scope to the anonymous function's scope, this is how:

$results =  \DB::table($table)
                ->join('links', function($join) use ($table)
                {
                    $join->on($table . '.source_id', '=',  'links.source_id');
                    $join->on($table . '.brand_id','=', 'links.brand_id');
                })
                ->get();

Notice the line:

->join('links', function($join) use ($table)

Problem is the anonymous function doesn't know about the variable $table, so you tell it about the variable using use.

You can find it in the docs.

Answer 2

Please try :

<?php 
$type = Input::get('type', null);
$table = $type . '_details';
$joinFunction = function($join) use ($table)
                {
                    $join->on($table . '.source_id', '=',  'links.source_id');
                    $join->on($table . '.brand_id','=', 'links.brand_id');
                }
$results =  \DB::table($table)
                ->join('links',$joinFunction )
                ->get();

?>

The problem was that the function doesn't see the $table variable inside it. That's why you need to use the "use" statement .

Read more about anonymous functions in php here