[Dbix-class] INSERT occurring on SELECT

[Dermot]

2008/6/30 Jonathan Rockway <jon at jrock.us>:
> * On Tue, Jun 24 2008, Dermot wrote:
>> Hi,
>>
>> I have been trying to get started using dbix (in a Catalyst env) and
>> have hit an early problem. When I call a method to list all the
>> entries in one table, null entries are created in a different table.
>> Below is the trace.
>>
>> SELECT me.id, me.name, me.contrib_id FROM submissions me:
>> SELECT me.id, me.name, me.contrib_id FROM submissions me:
>> SELECT me.id, me.code FROM contributors me WHERE ( ( ( me.id = ? ) ) ): '1'
>> SELECT me.id, me.code FROM contributors me WHERE ( ( ( me.id = ? ) ) ): '2'
>> INSERT INTO contributors (id) VALUES (?): '2'
>> SELECT me.id, me.code FROM contributors me WHERE ( ( ( me.id = ? ) ) ): '3'
>> INSERT INTO contributors (id) VALUES (?): '3'
>>
>>
>> I have a small dataset. There are 2 tables. T1 has with a single entry.
>>>sqlite3 motion.db "select * from contributors"
>> 1|S6G
>>
>> T2 have 3 entries. The 3rd column is the ID in T1.
>>>sqlite3 motion.db "select * from submissions"
>> 1|23|1
>> 2|167|1
>> 3|254|1
>>
>> If I use a conventional script to list the data, all is fine.
>>
>> I have traced the problem to my Template within my Catalyst app.
>>
>>    [% FOREACH submission IN submissions %]
>>    [%# SET contributor = submission.contrib_id %]
>>   <li>
>>     [% submission.name | html %]&nbsp;[%# contributor.code | html
>> %]&nbsp;[%# submission.contrib_id.code %]
>>
>> If any of the commented lines are used I get the result below.
>>
>>>sqlite3 motion.db "select * from contributors"
>> 1|S6G
>> 2|
>> 3|
>>
>> I hope this isn't OT for the list. Can anyone suggest where the
>> problem might be?
>
> Can you boil this down to a pure Perl test case?  There is really too
> much going on in your template to know what could be causing the insert.
> (Basically, you have three things commented out, not all related.  I
> don't feel like manually testing each case.)
>
> If you can narrow it down to one line of Perl that causes the problem,
> then we can help you.
>
> Using a minimal schema would also be helpful, but probably not necessary
> quite yet.  I think I can guess what your schema looks like.
>

The problem was not with the perl but my dbix resource file. I had a
belongs_to defined something like

__PACKAGE__->belongs_to(
                sub_id => 'MyApp', 'id'
                );

When I removed the id, it worked fine. I got there with the help of
someone on IRC which I heard about from your book :D
snippet below.

Thanx,
Dp.


<@castaway> TomRed: anyway.. does your submissions table have a
belongs_to field with a 2 in it, with no corresponding entry in the
contributors table?
<@castaway> in which case thats why it does it.. data inconsistent
<@castaway> TomRed: but looking at your output, youve set up that
relationship wrong
<@castaway> cant tell you how since you didnt include any code!
01<TomRed> submissions has a belongs_to contrib_id =>
'Motion::Schema::MotionDB::Contributors', 'id'
<@castaway> ok, why does it say "id" there?
<@castaway> you're telling it the ID of the contributors table is
inyour submissions "id" field
<@castaway> probably not what you meant?
01<TomRed> Id is the field it relates to in contributors
01<TomRed> No!
<@castaway> you dont need to tell it the field in contributors, it
assumes you meant the PK
<@castaway> you do need to tell it which field in submissions tho ;)
<@castaway> or supply both,. see the examples that have a hashref in there
01<TomRed> Ok. I'll re-read and try again. Not sure now why I had
'id' there now.
<@castaway> me either ;)