[Dbix-class] Removing orphan records from linked tables

[Nick Prater]

Thanks for the response. I appreciate it.

>> In plain SQL, I can use the following command:
>>
>> DELETE events.*
>> FROM events
>> LEFT JOIN clips ON (events.event_id = clips.event_id)
>> WHERE clips.clip_id IS NULL
>> AND events.status='deleted'
>>
>>
>> DELETE events.*
> Shouldn't that just be 'DELETE FROM events'  ?

Perhaps this is mysql specific syntax? The `events.*` is required to
specify which table we are deleting from, as the query is forming a join
between two  tables. Otherwise it is ambiguous whether we are deleting
records from events or clips table and the query fails with an error.

>> WHERE clips.clip_id IS NULL
> Is clip_id the PK of clips?

Yes

> How does clips.clip_id come to be null?

The query is doing a LEFT JOIN, so clips.clip_id (or any clips field for
that matter) will be null where there are no corresponding records in the
clips table to join.

> What was the design decision that led to not having FK constraints to
> prevent orphans?

My use of 'orphan' is misleading. 'Childless' would be better.

Records are added to the `events` table on a continuous and automatic
basis. Most turn out to be unwanted, so can be deleted after a short time.
`events.event_id` is the auto-incrementing primary key for the table.

For wanted events, users can cause one (or more) related records to be
created in the `clips` table. `clips.clip_id` is it's auto-incrementing
primary key.

`clips` are related to `events` using the `event_id` field.

FK constraints prevent `events` records being deleted if there are any
`clips` records relating to them.

I am trying to delete the unwanted, childless `events` records, older than
28 days, that have no related `clips`.