Postgres: deleting pivot-related records failed

[ddzobov]

• Laravel Version: 5.4.28
• PHP Version: 7.1.3
• Database Driver & Version: PostgreSQL 9.6.1 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 4.8.2-19ubuntu1) 4.8.2, 64-bit

Description:

I renamed model and table names from real production application, and in code may be mistakes. If needs, i can reproduce this with fresh project.

In PostgreSQL i reproducing this bug, but after switching this code to MySQL - all works fine.

I have two models related by pivot table

ModelOne:
public function modeltwo_items() {
     return $this->belongsToMany('App\ModelTwo', 'modelone_modeltwo','modelone_id', 'modeltwo_id');
}

ModelTwo:
public function modelone_items() {
     return $this->belongsToMany('App\ModelOne', 'modelone_modeltwo','modeltwo_id', 'modelone_id');
}

Steps To Reproduce:

This line causes error:
$modelone->modeltwo_items()->delete();

Illuminate\Database\QueryException: SQLSTATE[42P01]: Undefined table: 7 ERROR:  missing FROM-clause entry for table "modelone_modeltwo"

SQL: delete from "modelone" where "modelone_modeltwo"."modelone_id" = ID

But if i call this, i got correct SQL query:
$modelone->modeltwo_items()->toSql();

select * from "modeltwo" inner join "modelone_modeltwo" on "modeltwo"."id" = "modelone_modeltwo"."modeltwo_id" where "modelone_modeltwo"."modelone_id" = ?

[ddzobov]

https://www.postgresql.org/docs/9.6/static/sql-delete.html

Joins with DELETE not supported, but USING clause is supported.

[srmklive]

If you read the Laravel documentation, detach method is used to delete related table records. Instead of delete, do the following:

$modelone->modeltwo_items()->detach();

It works on Postgres 9.6.

[ddzobov]

Detach is used to drop relations by removing records in pivot table.

The detach method will remove the appropriate record out of the intermediate table; however, both models will remain in the database

But i need to remove records in related table, not pivot.

[srmklive]

@ddzobov Please read the Laravel documentation on collections: https://laravel.com/docs/5.4/collections

[ddzobov]

So, if i need remove all related to my object records (for example, 1000), i need to run multiple queries (N = count(relations) + 1)?! Do you really think that this is optimal way?

[srmklive]

@ddzobov That's the best possible solution in my opinion. You need to figure out a better solution on your own.

Also this is not a bug with the framework in my opinion.

[ddzobov]

Do you know difference of calling $object->relation and $object->relation()?
If i don't need a collection of records - why i need to get it for delete?

I want to remove items with single query, this feature working correctly in MySQL.

And in my opinion this is bug, because code behavior different in different types of database, but Eloquent was designed for same code behavior in different types of database.

[srmklive]

I am perfectly aware of it @ddzobov 😄. If you think its a bug then why you don't submit a PR for this.

[ddzobov]

PR with grammar for DELETE with USING clause #20041

[ddzobov]

#20062 - new PR with tests

[themsaid]

#20062