[Dbix-class] Removing orphan records from linked tables

[Len Jaffe]

On Wed, Sep 12, 2012 at 2:47 PM, Nick Prater <nick at npbroadcast.com> wrote:

> > 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.

Yup.


> 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 =3D clips.event_id)
> WHERE clips.clip_id IS NULL
> AND events.status=3D'deleted'
>
> Is there any way to cause DBIC to generate a query like this?
>
Generate? You might have to fix a bug int he MySQL-specific code generator.
Other wise, since you've written it, and it isn't returning anything other
than pass/fail, you might as well just execute the raw SQL.


>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.
>
In my opinion, the key determinant in the maximally efficient vs done, is
whether done is good enough.  Does it run fast enough now? Will it slow
linearly, or exponentially? How much time will it take to maximize now vs
waiting?

You'll answer those questions based on your app.

But since you have the raw, working SQL you have a workaround.



> Thanks for taking the time to help Len - I appreciate it.
>
You're welcome.


-- =

lenjaffe at jaffesystems.com   614-404-4214             www.volunteerable.net
Proprietor: http://www.theycomewithcheese.com/ - An Homage to Fromage
Greenbar <http://www.greenbartraining.org/>: Grubmaster: 2012-2009, Grub
Asst: 2008, Trained: 2007.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.scsys.co.uk/pipermail/dbix-class/attachments/20120912/ad6=
3ea2a/attachment.htm