[Dbix-class] Search and Delete with Single Query

[Spevak, Martin (HPES Network Management Solutions)]

You must have something wrong in DBIx relationship, many be in part:
    { is_deferrable => 1, on_delete => "CASCADE", on_update => "CASCADE" },

try this:

my $sql = $schema->resultset('User')->search({GroupId => 712});
$schema->resultset('User')->search({
  Id => { IN => $sql->get_column('Id')->as_query },
})->delete();

It's create query
delete from User where Id in (select Id from User where GroupId = 712});

But first try to look at your relationship definitions

singer


On Thu, 2014-08-28 at 18:40 +0530, Sheeju Alex 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<mailto:billcrawford1970 at gmail.com>> wrote:
$schema->resultset('User')->search({GroupId => 712})->delete;

On 28 August 2014 13:01, Sheeju Alex <sheejuec7 at gmail.com<mailto: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<http://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<http://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


--
Martin Spevak
HPES Software Development Engineer
HPES Network Management Solutions
Location:  Galvaniho 7A, Bratislava, Slovakia
Tel:     +421 2 5752 5574
Email:martin.spevak at hp.com
Out of Office Alert: