Search and Delete with Single Query

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
8 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Search and Delete with Single Query

Sheeju Alex
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@...
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Search and Delete with Single Query

Will Crawford
$schema->resultset('User')->search({GroupId => 712})->delete;

On 28 August 2014 13:01, Sheeju Alex <[hidden email]> 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@...

_______________________________________________
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@...
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Search and Delete with Single Query

Sheeju Alex
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 <[hidden email]> wrote:
$schema->resultset('User')->search({GroupId => 712})->delete;

On 28 August 2014 13:01, Sheeju Alex <[hidden email]> 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@...

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


_______________________________________________
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@...
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: 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 <[hidden email]> 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 <[hidden email]>
> wrote:
>>
>> $schema->resultset('User')->search({GroupId => 712})->delete;
>>
>> On 28 August 2014 13:01, Sheeju Alex <[hidden email]> 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@...
>>
>> _______________________________________________
>> 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@...
>
>
>
> _______________________________________________
> 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@...

_______________________________________________
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@...
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Search and Delete with Single Query

martin spevak
In reply to this post by Sheeju Alex
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 <[hidden email]<mailto:[hidden email]>> wrote:
$schema->resultset('User')->search({GroupId => 712})->delete;

On 28 August 2014 13:01, Sheeju Alex <[hidden email]<mailto:[hidden email]>> 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@...

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




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


--
Martin Spevak
HPES Software Development Engineer
HPES Network Management Solutions
Location:  Galvaniho 7A, Bratislava, Slovakia
Tel:     +421 2 5752 5574
Email:[hidden email]
Out of Office Alert:



_______________________________________________
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@...
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Search and Delete with Single Query

Sheeju Alex
In reply to this post by Sheeju Alex
I tried ->delete and ->delete_all both operation was deleting one by one. The version of DBIx::Class is 0.08250

You guessed right :) I had changed the table name but the code is no different, here is the code and schema definition of $rs

$schema->resultset('AlertParameterValue')->search({ProfileId => 712})->delete;

Schema Class is below and I don't have cascade relationship or is_deferable

use utf8;

package PAMS::WWW::Schema::Result::AlertParameterValue;

use strict;
use warnings;

use Moose;
use MooseX::NonMoose;
use MooseX::MarkAsMethods autoclean => 1;
extends 'DBIx::Class::Core';

__PACKAGE__->table("AlertParameterValue");

__PACKAGE__->add_columns(
    "Id",
    {
        data_type         => "integer",
        is_auto_increment => 1,
        is_nullable       => 0,
        sequence          => "\"AlertParameterValue_Id_seq\"",
    },
    "ProfileId",
    {data_type => "integer", is_nullable => 1},
    "Value",
    {data_type => "varchar", is_nullable => 0, size => 255},
);

__PACKAGE__->set_primary_key("Id");

# Created by DBIx::Class::Schema::Loader v0.07035 @ 2013-06-19 16:08:39
# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:ykWXuRi/+hlXQIwEwVakfQ

__PACKAGE__->belongs_to(
    Profile => 'PAMS::WWW::Schema::Result::AlertProfile',
    {'foreign.Id' => 'self.ProfileId'}
);

# PgLog Settings goes here
__PACKAGE__->load_components(qw/PgLog/);

# You can replace this text with custom code or comments, and it will be preserved on regeneration
__PACKAGE__->meta->make_immutable;

1;




Best Regards,    
Sheeju Alex


On Fri, Aug 29, 2014 at 12:04 PM, Peter Rabbitson <[hidden email]> wrote:
On 08/28/2014 02:01 PM, Sheeju Alex 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;

This is exactly how ->delete_all is supposed to work (one by one, so that the business logic is invoked on per-object basis).

This is not supposed to happen with $rs->delete however. Please provide your version of DBIx::Class, and the true definition of the $rs (I have a strong suspicion you did *not* give us the actual code).


_______________________________________________
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@...
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Search and Delete with Single Query

William Cox
Read the documentation for PgLog:
https://metacpan.org/pod/DBIx::Class::ResultSet::PgLog#delete - it
forces ->delete_all behavior

On Fri, Aug 29, 2014 at 2:41 PM, Sheeju Alex <[hidden email]> wrote:

> I tried ->delete and ->delete_all both operation was deleting one by one.
> The version of DBIx::Class is 0.08250
>
> You guessed right :) I had changed the table name but the code is no
> different, here is the code and schema definition of $rs
>
> $schema->resultset('AlertParameterValue')->search({ProfileId =>
> 712})->delete;
>
> Schema Class is below and I don't have cascade relationship or is_deferable
>
> use utf8;
>
> package PAMS::WWW::Schema::Result::AlertParameterValue;
>
> use strict;
> use warnings;
>
> use Moose;
> use MooseX::NonMoose;
> use MooseX::MarkAsMethods autoclean => 1;
> extends 'DBIx::Class::Core';
>
> __PACKAGE__->table("AlertParameterValue");
>
> __PACKAGE__->add_columns(
>     "Id",
>     {
>         data_type         => "integer",
>         is_auto_increment => 1,
>         is_nullable       => 0,
>         sequence          => "\"AlertParameterValue_Id_seq\"",
>     },
>     "ProfileId",
>     {data_type => "integer", is_nullable => 1},
>     "Value",
>     {data_type => "varchar", is_nullable => 0, size => 255},
> );
>
> __PACKAGE__->set_primary_key("Id");
>
> # Created by DBIx::Class::Schema::Loader v0.07035 @ 2013-06-19 16:08:39
> # DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:ykWXuRi/+hlXQIwEwVakfQ
>
> __PACKAGE__->belongs_to(
>     Profile => 'PAMS::WWW::Schema::Result::AlertProfile',
>     {'foreign.Id' => 'self.ProfileId'}
> );
>
> # PgLog Settings goes here
> __PACKAGE__->load_components(qw/PgLog/);
>
> # You can replace this text with custom code or comments, and it will be
> preserved on regeneration
> __PACKAGE__->meta->make_immutable;
>
> 1;
>
>
>
>
> Best Regards,
> Sheeju Alex
>
>
> On Fri, Aug 29, 2014 at 12:04 PM, Peter Rabbitson <[hidden email]>
> wrote:
>>
>> On 08/28/2014 02:01 PM, Sheeju Alex 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;
>>
>>
>> This is exactly how ->delete_all is supposed to work (one by one, so that
>> the business logic is invoked on per-object basis).
>>
>> This is not supposed to happen with $rs->delete however. Please provide
>> your version of DBIx::Class, and the true definition of the $rs (I have a
>> strong suspicion you did *not* give us the actual code).
>>
>>
>>
>> _______________________________________________
>> 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@...
>
>
>
> _______________________________________________
> 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@...



--
William Cox

e: [hidden email]
www.google.com/+WilliamCoxx

-----BEGIN GEEK CODE BLOCK-----
Version: 3.1
GCS d- s+:+() a C++(++++)$ UBLC(++)$
P+++(++++)$ L++(+++)$ !E--- W++(+++)$
!N !o? K--? !w--- !O M++ !V- PS-(--)@ PE+()
Y+ !PGP t++ !5 X+++ !R tv(+) b+>++
DI+(++) D+() G e h--- r+++ y+++>++++
------END GEEK CODE BLOCK------

_______________________________________________
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@...
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Search and Delete with Single Query

Sheeju Alex
Thanks William.. I checked everywhere else completely forgot about PgLog :) I will debug and try to fix at PgLog module, my expectation of PgLog was to use it only if the query is wrapped around with txn_do.


Best Regards,    
Sheeju Alex


On Sat, Aug 30, 2014 at 6:02 AM, William Cox <[hidden email]> wrote:
Read the documentation for PgLog:
https://metacpan.org/pod/DBIx::Class::ResultSet::PgLog#delete - it
forces ->delete_all behavior

On Fri, Aug 29, 2014 at 2:41 PM, Sheeju Alex <[hidden email]> wrote:
> I tried ->delete and ->delete_all both operation was deleting one by one.
> The version of DBIx::Class is 0.08250
>
> You guessed right :) I had changed the table name but the code is no
> different, here is the code and schema definition of $rs
>
> $schema->resultset('AlertParameterValue')->search({ProfileId =>
> 712})->delete;
>
> Schema Class is below and I don't have cascade relationship or is_deferable
>
> use utf8;
>
> package PAMS::WWW::Schema::Result::AlertParameterValue;
>
> use strict;
> use warnings;
>
> use Moose;
> use MooseX::NonMoose;
> use MooseX::MarkAsMethods autoclean => 1;
> extends 'DBIx::Class::Core';
>
> __PACKAGE__->table("AlertParameterValue");
>
> __PACKAGE__->add_columns(
>     "Id",
>     {
>         data_type         => "integer",
>         is_auto_increment => 1,
>         is_nullable       => 0,
>         sequence          => "\"AlertParameterValue_Id_seq\"",
>     },
>     "ProfileId",
>     {data_type => "integer", is_nullable => 1},
>     "Value",
>     {data_type => "varchar", is_nullable => 0, size => 255},
> );
>
> __PACKAGE__->set_primary_key("Id");
>
> # Created by DBIx::Class::Schema::Loader v0.07035 @ 2013-06-19 16:08:39
> # DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:ykWXuRi/+hlXQIwEwVakfQ
>
> __PACKAGE__->belongs_to(
>     Profile => 'PAMS::WWW::Schema::Result::AlertProfile',
>     {'foreign.Id' => 'self.ProfileId'}
> );
>
> # PgLog Settings goes here
> __PACKAGE__->load_components(qw/PgLog/);
>
> # You can replace this text with custom code or comments, and it will be
> preserved on regeneration
> __PACKAGE__->meta->make_immutable;
>
> 1;
>
>
>
>
> Best Regards,
> Sheeju Alex
>
>
> On Fri, Aug 29, 2014 at 12:04 PM, Peter Rabbitson <[hidden email]>
> wrote:
>>
>> On 08/28/2014 02:01 PM, Sheeju Alex 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;
>>
>>
>> This is exactly how ->delete_all is supposed to work (one by one, so that
>> the business logic is invoked on per-object basis).
>>
>> This is not supposed to happen with $rs->delete however. Please provide
>> your version of DBIx::Class, and the true definition of the $rs (I have a
>> strong suspicion you did *not* give us the actual code).
>>
>>
>>
>> _______________________________________________
>> 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@...
>
>
>
> _______________________________________________
> 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@...



--
William Cox

e: [hidden email]
www.google.com/+WilliamCoxx

-----BEGIN GEEK CODE BLOCK-----
Version: 3.1
GCS d- s+:+() a C++(++++)$ UBLC(++)$
P+++(++++)$ L++(+++)$ !E--- W++(+++)$
!N !o? K--? !w--- !O M++ !V- PS-(--)@ PE+()
Y+ !PGP t++ !5 X+++ !R tv(+) b+>++
DI+(++) D+() G e h--- r+++ y+++>++++
------END GEEK CODE BLOCK------


_______________________________________________
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@...
Loading...