Problem with attach relationship Many to Many

Are you sure $request->input('areas') is a single id?

The first parameter of attach method must be the id or model of related. I dont think $request->input('areas') return the id.

in https://laracasts.com/series/laravel-5-fundamentals/episodes/21 used $request->input('areas')

mi form

 <div class="col-sm-6">
             @foreach ($areas as $area)
                        <div class="checkbox">
                                 <label>{!! Form::checkbox('areas[]', $area->id ) !!}{!!$area->nombre !!}</label>
                        </div> 
             @endforeach
</div>

the problem is the model Area used another connection database

As you can see areas[] is an array, the attach method only accept on id or a model as parameter

Try snyc instead of attach.

$investigation->areas()->save($request->input('areas'));

Make sure the fillable array of area is defined

I get this error

ErrorException in BelongsToMany.php line 550: Argument 1 passed to Illuminate\Database\Eloquent\Relations\BelongsToMany::save() must be an instance of Illuminate\Database\Eloquent\Model, array given, called in /var/www/html/laravel51/app/Http/Controllers/InvestigationController.php on line 49 and defined

$investigation = Investigation::create($request->all());
$investigation->areas()->save($request->input('areas'));

And

$investigation = Investigation::create($request->all());
$areas = $request->input('areas');
$investigation->areas()->sync([$areas]);

I get this error

QueryException in Connection.php line 636: SQLSTATE[42P01]: Undefined table: 7 ERROR: relation "area_investigation" does not exist LINE 1: select "area_id" from "area_investigation" where "investigat... ^ (SQL: select "area_id" from "area_investigation" where "investigation_id" = 15)

@omontes Did you make a migration to create the pivot table "area_investigation"?

Also...

$investigation = Investigation::create($request->all());
$areas = $request->input('areas');
$investigation->areas()->sync($areas); // $areas is already an array, don't encapsulate it.

@Francismori7 , yes the table area_investigation exists in the database and I did

$investigation = Investigation::create($request->all());
$areas = $request->input('areas');
$investigation->areas()->sync($areas);

QueryException in Connection.php line 636: SQLSTATE[42P01]: Undefined table: 7 ERROR: relation "area_investigation" does not exist LINE 1: select "area_id" from "area_investigation" where "investigat... ^ (SQL: select "area_id" from "area_investigation" where "investigation_id" = 27)

I resolved

$investigation = Investigation::create($request->all());
$areas = $request->input('areas');
$idInvestigation = $investigation->id;
foreach ($areas as $key => $value) {
       $area_investigation = [
            ['area_id' => $value,
            'investigation_id' => $idInvestigation]
        ];
DB::table('area_investigation')->insert($area_investigation);
    }

$investigation->areas()->sync($request->input('areas'));

Then your error specify the expected pivot table doesn't exist. Specify the right pivot table name in the relationship definition.

@pmall, where?

class Investigation extends Model  {

    protected $table = 'investigations';

    public function areas()
    {
        return $this->belongsToMany('App\Area');
    }

}

class Area extends Model
{
    protected $connection = 'conecc2';

    public $table = "areas";


    public function investigaciones()
    {
        return $this->belongsToMany('App\Investigation');
    }
}

//InvestigationController.php
    public function store(Request $request)
    {
        $investigation = Investigation::create($request->all());
        $investigation->areas()->sync($request->input('areas'));
        
        return redirect('investigations');
    }
                

table if there

CREATE TABLE area_investigation
(
  area_id integer NOT NULL,
  investigation_id integer NOT NULL,
  CONSTRAINT area_investigation_investigation_id_foreign FOREIGN KEY (investigation_id)
      REFERENCES investigations (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE CASCADE
)
WITH (
  OIDS=FALSE
);
ALTER TABLE area_investigation
  OWNER TO postgres;

I get this error SQLSTATE[42P01]: Undefined table: 7 ERROR: relation "area_investigation" does not exist LINE 1: select "area_id" from "area_investigation" where "investigat... ^ (SQL: select "area_id" from "area_investigation" where "investigation_id" = 39)

CREATE TABLE area_investigation
// ...

ERROR: relation "area_investigation" does not exist

Obviously something is wrong...

the table is created.

SQLSTATE[42P01]: Undefined table: 7 ERROR: relation "area_investigation" does not exist LINE 1: select "area_id" from "area_investigation" where "investigat...

Obviously not. There is something wrong in your setup.

can be Connection to another database?

class Area extends Model
{
    protected $connection = 'conecc2';

and to do

$investigation = Investigation::findOrFail(46);
$areas = $investigation->areas;
dd($areas);

SQLSTATE[42P01]: Undefined table: 7 ERROR: relation "area_investigation" does not exist LINE 1: ...ea_id" as "pivot_area_id" from "areas" inner join "area_inve... ^ (SQL: select "areas".*, "area_investigation"."investigation_id" as "pivot_investigation_id", "area_investigation"."area_id" as "pivot_area_id" from "areas" inner join "area_investigation" on "areas"."id" = "area_investigation"."area_id" where "area_investigation"."investigation_id" = 46)

@omontes, still the same error, table "area_investigation" does not exist in the database you are querying.

@omontes It looks like building relationships across two different databases are causing some problem. Where is your pivot table? You should specify that. For example, this example assumes that your pivot table is in the second database.

public function areas()
{
    // You need to pass in the  second argument specifying where your pivot table is
    return $this->belongsToMany('App\Area', 'conecc2.area_investigation');
}

public function investigaciones()
{
    // You need to pass in the  second argument specifying where your pivot table is
    return $this->belongsToMany('App\Investigation', 'conecc2.area_investigation');
}

pivot table is local or primary database, areas table second database

file .env

DB_HOST=localhost
DB_DATABASE=investigations
DB_USERNAME=*****
DB_PASSWORD=******

conecc2_HOST= 192.168.102.50
conecc2_DATABASE=app
conecc2_USERNAME=*****
conecc2_PASSWORD=***

class Area extends Model
{
    protected $connection = 'conecc2';

    public $table = "areas";

    public function investigaciones()
    {
        return $this->belongsToMany('App\Investigation', 'area_investigation');
    }
}

class Investigation extends Model  {
  
 public function areas()
    {
        return $this->belongsToMany('App\Area','area_investigation');
    }

}

QueryException in Connection.php line 636: SQLSTATE[42P01]: Undefined table: 7 ERROR: relation "area_investigation" does not exist LINE 1: ...ea_id" as "pivot_area_id" from "areas" inner join "area_inve... ^ (SQL: select "areas".*, "area_investigation"."investigation_id" as "pivot_investigation_id", "area_investigation"."area_id" as "pivot_area_id" from "areas" inner join "area_investigation" on "areas"."id" = "area_investigation"."area_id" where "area_investigation"."investigation_id" = 52)

@omontes Then, for your areas model, define the primary database. I'm assuming it's named mysql in your config/database.php file. Try this.

public function investigaciones()
{
    // Change mysql to whatever you named your primary connection
    return $this->belongsToMany('App\Investigation', 'mysql.area_investigation');
}

I use pgsql

config/database.php

'default' => env('DB_CONNECTION', 'pgsql'),

Model

    public function investigaciones()
    {
        return $this->belongsToMany('App\Investigation', 'pgsql.area_investigation');
    }

Controller

                $investigation = Investigation::findOrFail(55);
                $areas = $investigation->areas;

SQLSTATE[3F000]: Invalid schema name: 7 ERROR: schema "pgsql" does not exist LINE 1: ...ea_id" as "pivot_area_id" from "areas" inner join "pgsql"."a... ^ (SQL: select "areas".*, "pgsql"."area_investigation"."investigation_id" as "pivot_investigation_id", "pgsql"."area_investigation"."area_id" as "pivot_area_id" from "areas" inner join "pgsql"."area_investigation" on "areas"."id" = "pgsql"."area_investigation"."area_id" where "pgsql"."area_investigation"."investigation_id" = 55)

Sorry, I just realized that they are not on the same host. I think you can join different databases on the same host but not different databases on different hosts...

Your relationships need to be on the same database connection. You cannot execute an inner join query on two different connections, it's not supported by any SQL provider, PG, My, Lite, none of them.

You will need to merge the database for this to work.

change

DB_HOST=localhost
DB_DATABASE=investigations
DB_USERNAME=******
DB_PASSWORD=******

conecc2_HOST= localhost
conecc2_DATABASE=app
conecc2_USERNAME=******
conecc2_PASSWORD=****

class Area extends Model
{
    protected $connection = 'conecc2';

    public $table = "areas";
    public function investigaciones()
    {
        return $this->belongsToMany('App\Investigation', 'area_investigation');
    }
}

class Investigation extends Model  {
   protected $table = 'investigations';
   public function areas()
    {
        return $this->belongsToMany('App\Area','area_investigation');
    }

}

    public function show($id)
    {
        $investigation = Investigation::findOrFail($id);
        $areas = $investigation->areas;
        return view('investigation.show', compact('investigation','areas'));
    }

I get this error

SQLSTATE[42P01]: Undefined table: 7 ERROR: no existe la relación «area_investigation» LINE 1: ...ea_id" as "pivot_area_id" from "areas" inner join "area_inve... ^ (SQL: select "areas".*, "area_investigation"."investigation_id" as "pivot_investigation_id", "area_investigation"."area_id" as "pivot_area_id" from "areas" inner join "area_investigation" on "areas"."id" = "area_investigation"."area_id" where "area_investigation"."investigation_id" = 52)

@omontes If they are on the same host, try the solution I recommended.

    public function investigaciones()
    {
        return $this->belongsToMany('App\Investigation', 'pgsql.area_investigation');
    }

@thomaskim, I did what you indicated does not work

SQLSTATE[42P01]: Undefined table: 7 ERROR: no existe la relación «area_investigation» LINE 1: ...ea_id" as "pivot_area_id" from "areas" inner join "area_inve... ^ (SQL: select "areas".*, "area_investigation"."investigation_id" as "pivot_investigation_id", "area_investigation"."area_id" as "pivot_area_id" from "areas" inner join "area_investigation" on "areas"."id" = "area_investigation"."area_id" where "area_investigation"."investigation_id" = 52)

Last time I've tried something like this I had a problem with defining connections in .env file. I've resolved the problem by deleting .env file and declaring everything regarding connections in database.php.