[Dbix-class] Removing orphan records from linked tables

[Nick Prater]

> The docs do also say however:
>       Currently, you cannot delete from a table and select from the same
> table in a subquery.

This means that DBIC is generating SQL commands that are unsupported by
mysql.

> The good news is that your on the right track. The bad news is MySQL
> doesn't implement exactly what you want.

Mysql does implement what I want, just in a non-standard way. This
hand-crafted query does what I want, without using a subquery, and runs
well:

DELETE events.*
FROM events
LEFT JOIN clips ON (events.event_id = clips.event_id)
WHERE clips.clip_id IS NULL
AND events.status='deleted'

Is there any way to cause DBIC to generate a query like this?

>From our discussion and what I've read, I think the answer is 'no'.

> So you're going to have to perform the left join and return a array of
> event IDs, and then issue some number of
>
> delete from event where event_id in ( ... )

That would work. It's less efficient, but clearly much more portable,
which is important to me.


Thanks for taking the time to help Len - I appreciate it.

Nick