update and join

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

update and join

RAPPAZ Francois
I have two tables joined by a "belongs to" and a "has many" relationship
      Ddref
           idref iduser finished created
           n    n--------------+
           ^                   ¦
           ¦                   ¦RefUser (bt iduser)
           ¦UserRef (hm)       ¦
           ¦                   ¦
           1     1<------------+
           Dduser
           iduser email id_credit

idref is a primary key for Ddref
iduser is a pk for Dduser

In Dduser.pm
__PACKAGE__->belongs_to(RefUser=> 'Dbc::Schema::Result::Dduser',  {'foreign.iduser' => 'self.iduser'});

In Dduser.pm
__PACKAGE__->has_many(UserRef => 'Dbc::Schema::Result::Ddref',  {'foreign.iduser' => 'self.iduser'});


I would like to update the field id_credit in Ddref :
my $href2 = {
                  '+select' => [  'RefUser.id_credit', 'RefUser.email'],
                        '+as' => [qw/id_credit email/],
                  order_by => 'created',
                  join => ['RefUser']};

my $href = {idref => 24 };
my $rs1 = $s->resultset('Ddref')->search_rs($href, $href2);
my $row = $rs1->single;
        $row->set_column('id_credit' => 28);
        $row->update();

But update fails with since the sql is
UPDATE ddrefs SET id_credit = ? WHERE ( idref = ? )" with ParamValues: 0=28, 1='24'

Obviously the join is lost. What am I missing ?
Thanks


François

_______________________________________________
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: update and join

Darius Jokilehto-2
On Thursday, 13 November 2014, 11:36, RAPPAZ Francois <[hidden email]> wrote:



[...]

>
>
>I have two tables joined by a "belongs to" and a "has many" relationship
>      Ddref
>           idref iduser finished created
>           n    n--------------+
>           ^                   ¦
>           ¦                   ¦RefUser (bt iduser)
>           ¦UserRef (hm)       ¦
>           ¦                   ¦
>           1     1<------------+
>           Dduser
>           iduser email id_credit
>
>idref is a primary key for Ddref
>iduser is a pk for Dduser
>
>In Dduser.pm
>__PACKAGE__->belongs_to(RefUser=> 'Dbc::Schema::Result::Dduser',  {'foreign.iduser' => 'self.iduser'});

>

This relationship is incorrect - it's pointing to itself.

Hope that helps,

Darius

_______________________________________________
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: update and join

RAPPAZ Francois
Oh thanks, and sorry my message was wrong the RefUser relationship is in Ddref.pm not in Dduser.pm

But my code is correct for that. So again:

I have two tables joined by a "belongs to" and a "has many" relationship
      Ddref
           idref iduser finished created
           n    n--------------+
           ^                   ¦
           ¦                   ¦RefUser (bt iduser)
           ¦UserRef (hm)       ¦
           ¦                   ¦
           1     1<------------+
           Dduser
           iduser email id_credit

idref is a primary key for Ddref
iduser is a pk for Dduser

** In Ddref.pm **
__PACKAGE__->belongs_to(RefUser=> 'Dbc::Schema::Result::Dduser',  {'foreign.iduser' => 'self.iduser'});

In Dduser.pm
__PACKAGE__->has_many(UserRef => 'Dbc::Schema::Result::Ddref',  {'foreign.iduser' => 'self.iduser'});

I would like to update the field id_credit in Ddref :
my $href2 = {
                  '+select' => [  'RefUser.id_credit', 'RefUser.email'],
                        '+as' => [qw/id_credit email/],
                  order_by => 'created',
                  join => ['RefUser']};

my $href = {idref => 24 };
my $rs1 = $s->resultset('Ddref')->search_rs($href, $href2); my $row = $rs1->single;
        $row->set_column('id_credit' => 28);
        $row->update();

But update fails with since the sql is
UPDATE ddrefs SET id_credit = ? WHERE ( idref = ? )" with ParamValues: 0=28, 1='24'

Obviously the join is lost. What am I missing ?
Thanks


François

> -----Original Message-----
> From: Darius Jokilehto [mailto:[hidden email]]
> Sent: jeudi, 13. novembre 2014 13:46
> To: DBIx::Class user and developer list
> Subject: Re: [Dbix-class] update and join
>
> On Thursday, 13 November 2014, 11:36, RAPPAZ Francois
> <[hidden email]> wrote:
>
>
>
> [...]
> >
> >
> >I have two tables joined by a "belongs to" and a "has many"
> relationship
> >      Ddref
> >           idref iduser finished created
> >           n    n--------------+
> >           ^                   ¦
> >           ¦                   ¦RefUser (bt iduser)
> >           ¦UserRef (hm)       ¦
> >           ¦                   ¦
> >           1     1<------------+
> >           Dduser
> >           iduser email id_credit
> >
> >idref is a primary key for Ddref
> >iduser is a pk for Dduser
> >
> >In Dduser.pm
> >__PACKAGE__->belongs_to(RefUser=> 'Dbc::Schema::Result::Dduser',
> {'foreign.iduser' => 'self.iduser'});
>
> >
>
> This relationship is incorrect - it's pointing to itself.
>
> Hope that helps,
>
> Darius
>
> _______________________________________________
> 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-
> [hidden email]
_______________________________________________
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: update and join

RAPPAZ Francois
Thanks for replying !

So it's a feature, not a bug ?

In mysql, I can execute
"update `ddrefs` inner join `ddusers` using (iduser) set id_credit = 22 WHERE ddrefs.iduser = 3"

It would have been nice to have the same thing working with DBIC: the update being possible on a resultset derived from Ddref.

Best

François




> -----Original Message-----
> From: Peter Rabbitson [mailto:[hidden email]]
> Sent: jeudi, 13. novembre 2014 22:15
> To: Class user and developer list; Darius Jokilehto
> Subject: Re: [Dbix-class] update and join
>
> On 11/13/2014 02:31 PM, RAPPAZ Francois wrote:
> > ...
> > I would like to update the field id_credit in Ddref :
> > my $href2 = {
> >  '+select' => [  'RefUser.id_credit', 'RefUser.email'],
> > '+as' => [qw/id_credit email/],
> >  order_by => 'created',
> >  join => ['RefUser']};
> >
> > my $href = {idref => 24 };
> > my $rs1 = $s->resultset('Ddref')->search_rs($href, $href2); my $row =
> $rs1->single;
> > $row->set_column('id_credit' => 28);
> > $row->update();
> >
> > But update fails with since the sql is UPDATE ddrefs SET id_credit =
> ?
> > WHERE ( idref = ? )" with ParamValues: 0=28, 1='24'
> >
> > Obviously the join is lost. What am I missing ?
>
> $row does not represent a "row". It represents an item from the
> ResultSource of DDrefs. This is where your confustion is coming from -
> you assumed that a thing that comes from a $resultset is a
> representation of the "row that came off the cursor after all JOINs put
> together". This is an implementation detail, which only bled through
> because you used the advanced +select/+as options.
>
> In any case - any ->update/->delete/->copy call on a *Result* object
> will only operate on the table corresponding to the *ResultSource* of
> the *Result* you are operating on.
>
> Please feel free to ask additional question if the above isn't entirely
> clear.
>
> Cheers
>
> _______________________________________________
> 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-
> [hidden email]

_______________________________________________
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: update and join

Lasse Makholm


On Tue, Nov 18, 2014 at 12:50 PM, Peter Rabbitson <[hidden email]> wrote:
On 11/18/2014 12:36 PM, RAPPAZ Francois wrote:
Thanks for replying !

So it's a feature, not a bug ?

It's neither, it has to do with design.

In mysql, I can execute
"update `ddrefs` inner join `ddusers` using (iduser) set id_credit = 22 WHERE ddrefs.iduser = 3"

A similar (though *not* identical) effect can be achieved by operating on a ResultSet (not Result). Given your original example, the above would look something like:

$s->resultset('Ddref')->search(
  { 'RefUser.iduser' => 3 },
  { join => 'RefUser' }
)->update({ id_credit => 22 });

Again - you need to internalize the difference between the Result and ResultSet concepts, they are not interchangeable (if anything - they are orthogonal).

What really made it "click" for me was realizing that I should think of a ResultSet, not primarily as a collection of rows, but rather as an SQL query under construction (that might eventually get executed by calling ->first, ->next, ->all, etc...).

In reality, of course, ResultSets behave as both, but the part about it being a query-in-the-making is a subtle yet profound aspect that enables all sorts of interesting design patterns.

It took me too long to realize this when I started out with DBIC - probably because our code base made almost no use of it...

/L
 


_______________________________________________
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: update and join

Paul Newell
Forgive me if I am misinformed on this issue but this discussion seems to illustrate some of the confusion I have had in following DBIC with less than desired success. 

The problem here seems to have nothing to do with rows and resultsets but with the implementation on the part of the programmer. 

After using the criteria ddref.idref = 28 he joins that with a dduser table and gets a resultset (not, notably a result) and wants update the column id_credit in the ddref table. But this has nothing to do with the dduser table and regardless of what corresponding rows are in that table the programmer has expressed his intention of updating all of the rows in the ddref table with that idref. So there is no need to make the join to complete the action and the module has done exactly what it was asked to do. If the programmer wanted to update the table based on the join he would need to put in some criteria relating to the joined table, which of course Peter does provide in his example.

If my understanding is correct the update statement listed would not have "failed" but simply done exactly what was asked of it. ... Unless you were perhaps looking for an inner join - (WHERE ddrefs.iduser = dduser.iduser) - which the module could have done but wasn't really clearly indicated.

On 19 November 2014 09:26, Lasse Makholm <[hidden email]> wrote:


On Tue, Nov 18, 2014 at 12:50 PM, Peter Rabbitson <[hidden email]> wrote:
On 11/18/2014 12:36 PM, RAPPAZ Francois wrote:
Thanks for replying !

So it's a feature, not a bug ?

It's neither, it has to do with design.

In mysql, I can execute
"update `ddrefs` inner join `ddusers` using (iduser) set id_credit = 22 WHERE ddrefs.iduser = 3"

A similar (though *not* identical) effect can be achieved by operating on a ResultSet (not Result). Given your original example, the above would look something like:

$s->resultset('Ddref')->search(
  { 'RefUser.iduser' => 3 },
  { join => 'RefUser' }
)->update({ id_credit => 22 });

Again - you need to internalize the difference between the Result and ResultSet concepts, they are not interchangeable (if anything - they are orthogonal).

What really made it "click" for me was realizing that I should think of a ResultSet, not primarily as a collection of rows, but rather as an SQL query under construction (that might eventually get executed by calling ->first, ->next, ->all, etc...).

In reality, of course, ResultSets behave as both, but the part about it being a query-in-the-making is a subtle yet profound aspect that enables all sorts of interesting design patterns.

It took me too long to realize this when I started out with DBIC - probably because our code base made almost no use of it...

/L
 


_______________________________________________
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: update and join

RAPPAZ Francois
Thanks you all for the comments.

id_credit is in Dduser, not in Ddref.

           
           Ddref
           idref ¦ iduser ¦ finished ¦ created

           n    n----------------+
           ^                     ¦
           ¦                     ¦RefUser (belongs to, using iduser)
           ¦UserRef (has many)   ¦
             ¦   using iduser      ¦
           1     1<--------------+
           DdUser
           iduser ¦ email ¦ id_credit

           n     n---------------+
           ^                     ¦
             ¦                     ¦
           ¦CredUser (has many)  ¦UserCred (belongs to, using id_credit)
             ¦    using id_credit  ¦
           1                     ¦
           Credit 1<-------------+
           id_credit ¦ nom_credit




I can’t get Peter’s suggestion  working.
This fails:
my $href2 = {
                  '+select' => [  'RefUser.id_credit', 'RefUser.email'],
                        '+as' => [qw/id_credit email/],
                  order_by => 'created',
                  join => ['RefUser']
          };


$rs1 = $s->resultset('Ddref')->search_rs( {'RefUser.iduser' => 3}, $href2);

print "credit : ", ($rs1->first->get_column('id_credit') ? $rs1->first->get_column('id_credit') : "NULL");
$rs1->update({'id_credit' => 22});
print "credit : ", $rs1->first->get_column('id_credit');

The update fails if I use ‘id_credit’ or ‘RefUser.id_credit’

The statements produced are
UPDATE ddrefs SET RefUser.id_credit = ? WHERE (  idref IN ( SELECT * FROM ( SELECT me.idref FROM ddrefs me  JOIN ddusers RefUser ON RefUser.iduser = me.iduser WHERE ( RefUser.iduser = ? ) ORDER BY created )
`_forced_double_subquery` )  )"
with ParamValues: 0=22, 1=3]

UPDATE ddrefs SET id_credit = ? WHERE (  idref IN ( SELECT * FROM ( SELECT me.idref FROM ddrefs me  JOIN ddusers RefUser ON RefUser.iduser = me.iduser WHERE ( RefUser.iduser = ? ) ORDER BY created ) `_
forced_double_subquery` )  )" with ParamValues: 0=22, 1=3]

Best

François


From: Paul Newell [mailto:[hidden email]]
Sent: mercredi, 19. novembre 2014 18:03
To: DBIx::Class user and developer list
Subject: Re: [Dbix-class] update and join

Forgive me if I am misinformed on this issue but this discussion seems to illustrate some of the confusion I have had in following DBIC with less than desired success. 

The problem here seems to have nothing to do with rows and resultsets but with the implementation on the part of the programmer. 

After using the criteria ddref.idref = 28 he joins that with a dduser table and gets a resultset (not, notably a result) and wants update the column id_credit in the ddref table. But this has nothing to do with the dduser table and regardless of what corresponding rows are in that table the programmer has expressed his intention of updating all of the rows in the ddref table with that idref. So there is no need to make the join to complete the action and the module has done exactly what it was asked to do. If the programmer wanted to update the table based on the join he would need to put in some criteria relating to the joined table, which of course Peter does provide in his example.

If my understanding is correct the update statement listed would not have "failed" but simply done exactly what was asked of it. ... Unless you were perhaps looking for an inner join - (WHERE ddrefs.iduser = dduser.iduser) - which the module could have done but wasn't really clearly indicated.

On 19 November 2014 09:26, Lasse Makholm <[hidden email]> wrote:


On Tue, Nov 18, 2014 at 12:50 PM, Peter Rabbitson <[hidden email]> wrote:
On 11/18/2014 12:36 PM, RAPPAZ Francois wrote:
Thanks for replying !

So it's a feature, not a bug ?

It's neither, it has to do with design.
In mysql, I can execute
"update `ddrefs` inner join `ddusers` using (iduser) set id_credit = 22 WHERE ddrefs.iduser = 3"

A similar (though *not* identical) effect can be achieved by operating on a ResultSet (not Result). Given your original example, the above would look something like:

$s->resultset('Ddref')->search(
  { 'RefUser.iduser' => 3 },
  { join => 'RefUser' }
)->update({ id_credit => 22 });

Again - you need to internalize the difference between the Result and ResultSet concepts, they are not interchangeable (if anything - they are orthogonal).

What really made it "click" for me was realizing that I should think of a ResultSet, not primarily as a collection of rows, but rather as an SQL query under construction (that might eventually get executed by calling ->first, ->next, ->all, etc...).

In reality, of course, ResultSets behave as both, but the part about it being a query-in-the-making is a subtle yet profound aspect that enables all sorts of interesting design patterns.

It took me too long to realize this when I started out with DBIC - probably because our code base made almost no use of it...

/L
 

_______________________________________________
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: update and join

Peter Rabbitson-2
In reply to this post by RAPPAZ Francois
On 11/13/2014 12:34 PM, RAPPAZ Francois wrote:

> I have two tables joined by a "belongs to" and a "has many" relationship
>        Ddref
>             idref iduser finished created
>             n    n--------------+
>             ^                   ¦
>             ¦                   ¦RefUser (bt iduser)
>             ¦UserRef (hm)       ¦
>             ¦                   ¦
>             1     1<------------+
>             Dduser
>             iduser email id_credit
>
> I would like to update the field id_credit in Ddref :
> my $href2 = {
>  '+select' => [  'RefUser.id_credit', 'RefUser.email'],
> '+as' => [qw/id_credit email/],
>  order_by => 'created',
>  join => ['RefUser']};
>
> my $href = {idref => 24 };
> my $rs1 = $s->resultset('Ddref')->search_rs($href, $href2);
> my $row = $rs1->single;
> $row->set_column('id_credit' => 28);
> $row->update();
>
> But update fails with since the sql is
> UPDATE ddrefs SET id_credit = ? WHERE ( idref = ? )" with ParamValues: 0=28, 1='24'
>

Hi Francois,

I just wanted to check with you whether you ended up with a reasonable
answer to your question, or if you are still confused.

Thanks!


_______________________________________________
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: update and join

RAPPAZ Francois
Hi Peter,

As I said,
$s->resultset('Ddref')->search(
  { 'RefUser.iduser' => 3 },
  { join => 'RefUser' }
)->update({ id_credit => 22 });

Fails since id_credit is in Dduser

Or
$s->resultset('Ddref')->search(
  { 'RefUser.iduser' => 3 },
  { join => 'RefUser' }
)->update({ 'RefUser.id_credit' => 22 });
Fails also ( Unknown column 'RefUser.id_credit)

But updating a resultset based on Dduser works of course

$s->resultset('Dduser')->search_rs({iduser => $user})->update({id_credit => $value});


Is the update within a resulset based on Ddref possible ? what am I missing then ?

Thanks

François

> -----Original Message-----
> From: Peter Rabbitson [mailto:[hidden email]]
> Sent: samedi, 20. décembre 2014 16:08
> To: [hidden email]
> Subject: Re: [Dbix-class] update and join
>
> On 11/13/2014 12:34 PM, RAPPAZ Francois wrote:
> > I have two tables joined by a "belongs to" and a "has many"
> relationship
> >        Ddref
> >             idref iduser finished created
> >             n    n--------------+
> >             ^                   ¦
> >             ¦                   ¦RefUser (bt iduser)
> >             ¦UserRef (hm)       ¦
> >             ¦                   ¦
> >             1     1<------------+
> >             Dduser
> >             iduser email id_credit
> >
> > I would like to update the field id_credit in Ddref :
> > my $href2 = {
> >  '+select' => [  'RefUser.id_credit', 'RefUser.email'],
> > '+as' => [qw/id_credit email/],
> >  order_by => 'created',
> >  join => ['RefUser']};
> >
> > my $href = {idref => 24 };
> > my $rs1 = $s->resultset('Ddref')->search_rs($href, $href2); my $row =
> > $rs1->single;
> > $row->set_column('id_credit' => 28);
> > $row->update();
> >
> > But update fails with since the sql is UPDATE ddrefs SET id_credit =
> ?
> > WHERE ( idref = ? )" with ParamValues: 0=28, 1='24'
> >
>
> Hi Francois,
>
> I just wanted to check with you whether you ended up with a reasonable
> answer to your question, or if you are still confused.
>
> Thanks!
>
>
> _______________________________________________
> 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-
> [hidden email]
_______________________________________________
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: update and join

Peter Rabbitson-2
On 01/22/2015 09:18 AM, RAPPAZ Francois wrote:

> Hi Peter
>
> Thanks for the explanations: very clear
>
> I tried both
> $s->resultset('Ddref')->search_related('RefUser')->search({ 'RefUser.iduser' => 3 })->update({ 'id_credit' => 22 });
> $s->resultset('Ddref')->search_related_rs('RefUser',{ 'RefUser.iduser' => 3 })->update({id_credit => 22});
>
> Which silently failed. Id_credit in Ddusers is not changed. :-<
>

That is unsettling... Can you please do the following before the above:

  $s->storage->debug(1)

And tell me what queries were executed? Also - what version of
DBIx::Class are you running?


_______________________________________________
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: update and join

RAPPAZ Francois
U:\docs\perl\dokpe_i02_dd>perl -M"DBIx::Class 9999"
DBIx::Class version 9999 required--this is only version 0.08270.



UPDATE ddusers SET id_credit = ? WHERE (  iduser IN ( SELECT * FROM ( SELECT Ref
User.iduser FROM ddrefs me  JOIN ddusers RefUser ON RefUser.iduser = me.iduser W
HERE ( RefUser.iduser = ? ) ) `_forced_double_subquery` )  ): '22', '3'

For:
$s->resultset('Ddref')->search_related_rs('RefUser',{ 'RefUser.iduser' => 3 })->update({id_credit => 22});

> -----Original Message-----
> From: Peter Rabbitson [mailto:[hidden email]]
> Sent: jeudi, 22. janvier 2015 10:26
> To: [hidden email]
> Cc: RAPPAZ Francois
> Subject: Re: [Dbix-class] update and join
>
> On 01/22/2015 09:18 AM, RAPPAZ Francois wrote:
> > Hi Peter
> >
> > Thanks for the explanations: very clear
> >
> > I tried both
> > $s->resultset('Ddref')->search_related('RefUser')->search({
> > 'RefUser.iduser' => 3 })->update({ 'id_credit' => 22 });
> > $s->resultset('Ddref')->search_related_rs('RefUser',{
> 'RefUser.iduser'
> > => 3 })->update({id_credit => 22});
> >
> > Which silently failed. Id_credit in Ddusers is not changed. :-<
> >
>
> That is unsettling... Can you please do the following before the above:
>
>   $s->storage->debug(1)
>
> And tell me what queries were executed? Also - what version of
> DBIx::Class are you running?

_______________________________________________
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: update and join

Peter Rabbitson-2
On 01/22/2015 10:38 AM, RAPPAZ Francois wrote:

> U:\docs\perl\dokpe_i02_dd>perl -M"DBIx::Class 9999"
> DBIx::Class version 9999 required--this is only version 0.08270.
>
>
>
> UPDATE ddusers SET id_credit = ? WHERE (  iduser IN ( SELECT * FROM ( SELECT Ref
> User.iduser FROM ddrefs me  JOIN ddusers RefUser ON RefUser.iduser = me.iduser W
> HERE ( RefUser.iduser = ? ) ) `_forced_double_subquery` )  ): '22', '3'
>
> For:
> $s->resultset('Ddref')->search_related_rs('RefUser',{ 'RefUser.iduser' => 3 })->update({id_credit => 22});

Are you saying that after the above command/SQL the ddusers table is
*not* updated to id_credit=22 for the rows you want? How are you
checking for this?


_______________________________________________
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: update and join

Peter Rabbitson-2
On 01/22/2015 11:12 AM, RAPPAZ Francois wrote:

> I'm checking with phpMyAdmin
>
> If I do
> $s->resultset('Dduser')->search_rs({iduser => 3})->update({id_credit => 22});
>
> UPDATE ddusers SET id_credit = ? WHERE ( iduser = ? ): '22', '3'
>
> The field is updated
>
> If I do
> $s->resultset('Ddref')->search_related_rs('RefUser',{ 'RefUser.iduser' => 3 })->update({id_credit => 22});
>
> UPDATE ddusers SET id_credit = ? WHERE (  iduser IN ( SELECT * FROM ( SELECT Ref
> User.iduser FROM ddrefs me  JOIN ddusers RefUser ON RefUser.iduser = me.iduser W
> HERE ( RefUser.iduser = ? ) ) `_forced_double_subquery` )  ): '22', '3'
>
> It is not

This is... bizarre, and a *VERY* serious issue if indeed the case. I am
trying to reproduce it locally, will get back to you in a bit...



_______________________________________________
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: update and join

Peter Rabbitson-2
In reply to this post by Peter Rabbitson-2
On 01/22/2015 11:12 AM, RAPPAZ Francois wrote:

> I'm checking with phpMyAdmin
>
> If I do
> $s->resultset('Dduser')->search_rs({iduser => 3})->update({id_credit => 22});
>
> UPDATE ddusers SET id_credit = ? WHERE ( iduser = ? ): '22', '3'
>
> The field is updated
>
> If I do
> $s->resultset('Ddref')->search_related_rs('RefUser',{ 'RefUser.iduser' => 3 })->update({id_credit => 22});
>
> UPDATE ddusers SET id_credit = ? WHERE (  iduser IN ( SELECT * FROM ( SELECT Ref
> User.iduser FROM ddrefs me  JOIN ddusers RefUser ON RefUser.iduser = me.iduser W
> HERE ( RefUser.iduser = ? ) ) `_forced_double_subquery` )  ): '22', '3'
>
> It is not
>

Wait a second, I might have assumed too much here. Can you give me the
results (the return value and the executed SQL trace) of these three
statements:

$s->resultset('Ddref')->search_related('RefUser')->count;
$s->resultset('Ddref')->search_related('RefUser', { 'RefUser.iduser' =>
3 })->count;
$s->resultset('Dduser')->count;

Cheers


_______________________________________________
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: update and join

Peter Rabbitson-2
On 01/22/2015 12:42 PM, RAPPAZ Francois wrote:

> Bravo, That was it and I'm sorry for the time lost ...
> The count are
> 9
> 0
> 8
>
> I had no corresponding iduser with value 3 in Ddref.
> If I change this, the update works
>
> Sorry again

:)

As long as you actually understood the underlying mechanics (i.e. how
the search chain works and behaves) - I don't consider it lost time at all.

Cheers


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