On Wed, Sep 12, 2012 at 2:47 PM, Nick Prater <n...@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 = 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? > 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. -- lenja...@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.
_______________________________________________ List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class IRC: irc.perl.org#dbix-class SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/ Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
