[Dbix-class] Search and Delete with Single Query

[Will Crawford]

Are you sure? A similar construct here:

$schema->resultset('Contact')->search( { client_id => 123 } )->delete;

generates the query:

DELETE FROM "contacts"
WHERE "client_id" = '123'

On 28 August 2014 14:10, Sheeju Alex <sheejuec7 at gmail.com> wrote:
> No This doesn't work since DBIx converts this into below quries
>
> SELECT * FROM User WHERE GroupId = 712;
> SELECT * FROM User WHERE Id = 1;
> DELETE FROM User WHERE Id = 1;
> SELECT * FROM User WHERE Id = 2;
> DELETE FROM User WHERE Id = 2;
> SELECT * FROM User WHERE Id = 3;
> DELETE FROM User WHERE Id = 3;
>
>
> and so on
>
> So if we have 100 Users it would 201 (1 + 100 + 100) queries.
>
>
> Best Regards,
> Sheeju Alex
>
>
> On Thu, Aug 28, 2014 at 5:38 PM, Will Crawford <billcrawford1970 at gmail.com>
> wrote:
>>
>> $schema->resultset('User')->search({GroupId => 712})->delete;
>>
>> On 28 August 2014 13:01, Sheeju Alex <sheejuec7 at gmail.com> wrote:
>> > Hi All,
>> >
>> >     Is there any way in DBIx to delete all rows from resultset in a
>> > single
>> > query, it looks like delete and delete_all will delete the resultset row
>> > by
>> > row.
>> >
>> > my $rs = $schema->resultset('User')->search({GroupId => 712});
>> > $rs->delete_all;
>> >
>> > If there are 100 Users in User table then the above statement will
>> > delete in
>> > 100 query instead is there a way to delete in single query.
>> >
>> > DELETE FROM User WHERE GroupId = 712;
>> >
>> >
>> > Best Regards,
>> > Sheeju Alex
>> >
>> > _______________________________________________
>> > 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
>>
>> _______________________________________________
>> 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
>
>
>
> _______________________________________________
> 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