[Dbix-class] 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:paulgnewell at gmail.com] 
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 <lasse at unity3d.com> wrote:


On Tue, Nov 18, 2014 at 12:50 PM, Peter Rabbitson <rabbit+dbic at rabbit.us> 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@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