Add support for joins in delete and update queries

[dakota]

This is a (multiple allowed):

• bug
• enhancement
• feature-discussion (RFC)
• CakePHP Version: 3.next
• Platform and Target: All

Currently there is no way to implement/use joins in either delete or update queries. Doing so will require a custom query compiler for at least Postgres which does not use a conventional "join" syntax.

[lorenzo]

You can do this by naming 2 tables in the from(). For example: $query->update('table 1, table 2')

[markstory]

Joins on DELETE/UPDATE are not consistent, and sometimes impossible across the various db engines we support. That is the the main reason I didn't add support originally.

[thinkingmedia]

The description for this enhancement is just to broad. There are to many different scenarios where a delete with a join is a symptom of another problem. Database design can prevent this and triggers can delete dependencies.

Can you give an example that applies to all db engines and can't be resolved by schema design or trigger?

[pperejon]

Here you can find a question at stackoverflow about an scenario where matching in delete statements would be needed.

[markstory]

Using a subquery to do joins and filter the records to delete or update is how I would recommend doing this kind of work. It works across all database vendors, and doesn't require dialect specific SQL.

[pperejon]

In this case I think that's not possible because you can't launch a DELETE and a SELECT over the same table (MySQL restriction), so you can't use the target table in the subselect. The only solution I've found (not very beautiful) is encapsulating the subquery in another select, this way.

Note that making a query to get the resulting IDs and creating a simple condition "IN (1,4,.....988)" is not a solution, 'cause if there were too many IDs the query would grow without control.

I know this kind of query is not very ussual, so for me it's no problem to write one only plain SQL query in my whole application. What happens to me is I'm a bit surprised 'cause it seems this is the first time I find something I can't do with the ORM :)

[ADmad]

What happens to me is I'm a bit surprised 'cause it seems this is the first time I find something I can't do with the ORM :)

Even the best ORM has it's limitations :)

[lorenzo]

@pperejon would MySQL actually let you delete rows when using a join?

[raul338]

@lorenzo yes, http://stackoverflow.com/questions/8598791/sql-delete-with-inner-join
You can even delete from the joined tables
For example DELETE s.*, j.* FROM t INNER JOIN s ON ... INNER JOIN j ON ... WHERE ... would actually delete from j and s tables but not from t (if t were added would have the same effect as ON DELETE CASCADE)

[lorenzo]

I think it makes sense to implement it for DELETE, it looks like that only sqlite does not implement that feature, so we can leave that one out and give support to the others.

[pperejon]

Just to confirm it, as @raul338 said, with an script you can easily test:

-- Create structures
CREATE TABLE IF NOT EXISTS departments (
  id int(11) NOT NULL AUTO_INCREMENT,
  name varchar(45) NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=4 ;

CREATE TABLE IF NOT EXISTS employees (
  id int(11) NOT NULL AUTO_INCREMENT,
  department_id int(11) NOT NULL,
  name varchar(45) NOT NULL,
  PRIMARY KEY (id),
  KEY fk_employees_department_idx (department_id)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=5 ;

ALTER TABLE employees
  ADD CONSTRAINT fk_employees_departments FOREIGN KEY (department_id) REFERENCES departments (id) ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Populate some data
INSERT INTO departments (id, name) VALUES
(1, 'Sales'),
(2, 'TI'),
(3, 'HHRR');

INSERT INTO employees (id, department_id, name) VALUES
(1, 1, 'John'),
(2, 1, 'Mary'),
(3, 2, 'Mark'),
(4, 3, 'Lorenzo');

-- Delete all employees that belong to the TI department (Mark should be removed)
DELETE emp FROM employees emp
INNER JOIN departments dep ON emp.department_id = dep.id AND dep.name = 'TI';

btw, I realize now my question at stackoverflow is not correct. The case is not to remove all the departments where there is an employee named John, but the opposite, remove all employees from the TI department (or any condition on the related table). I'll fix it right now.

[lorenzo]

@pperejon you can use a subquery for that, though

[pperejon]

My god!!! I was really confused. You're right @lorenzo. Not to make mistakes again I've tested it with a fresh Cake install.

With the previous SQL schema, if you do this:

        $departments = $this->Employees->Departments->find()
            ->select(['Departments.id'])
            ->where(['Departments.name' => 'TI']);
        $this->Employees->deleteAll(['Employees.department_id IN ' => $departments]);

...you'll get this query in the mysql log:

DELETE FROM employees WHERE department_id in
(SELECT Departments.id AS `Departments__id` 
FROM departments Departments 
WHERE Departments.name = 'TI')

..which is perfectly correct for me.

The issue about launching DELETE queries with the matching function may still be opened, but definetively it's not the case I was exposing. Sorry about that 😢

[mehov]

Tried

$query = $this->{'Some/Table'}
    ->query()
    ->delete('some_table')
    ->join([
        [
            'type' => 'LEFT',
            'alias' => 't2',
            'table' => 'some_other_table,
            'conditions' => [
                't2.key' => 'some_table.key',
            ]
        ],
    ]);

resulting in SQL could not be generated for this query as it is incomplete

[ADmad]

@mehov You are missing where().

[mehov]

@ADmad Added it but getting the same thing I'm afraid. Full query:

$alias = $this->{'Some/Table'}->getAlias();
$query = $this->{'Some/Table'}
    ->query()
    ->delete($alias)
    ->join([
        [
            'type' => 'LEFT',
            'alias' => 't2',
            'table' => $this->{'Some/Table'}->getTable(),
            'conditions' => [
                't2.remote_id' => $alias.'.remote_id',
            ]
        ],
    ])
    ->where([
        't2.remote_id' => 1234
    ])
    ;
debug($query);exit;

Am I doing it wrong?

[github-actions]

This issue is stale because it has been open for 120 days with no activity. Remove the stale label or comment or this will be closed in 15 days