<tt><font size=2>Len Jaffe <lenjaffe@jaffesystems.com> wrote on
06/19/2012 03:45:34 PM:<br>
<br>
> From:</font></tt>
<br><tt><font size=2>> <br>
> Len Jaffe <lenjaffe@jaffesystems.com></font></tt>
<br><tt><font size=2>> <br>
> To:</font></tt>
<br><tt><font size=2>> <br>
> "DBIx::Class user and developer list" <dbix-class@lists.scsys.co.uk></font></tt>
<br><tt><font size=2>> <br>
> Date:</font></tt>
<br><tt><font size=2>> <br>
> 06/19/2012 03:49 PM</font></tt>
<br><tt><font size=2>> <br>
> Subject:</font></tt>
<br><tt><font size=2>> <br>
> Re: [Dbix-class] Search_rs not returning newly created rows</font></tt>
<br><tt><font size=2>> <br>
> <br>
</font></tt>
<br><tt><font size=2>> On Tue, Jun 19, 2012 at 3:22 PM, Kenneth S Mclane
<ksmclane@us.ibm.com> wrote:</font></tt>
<br><tt><font size=2>> Len Jaffe <lenjaffe@jaffesystems.com> wrote
on 06/19/2012 02:06:21 PM:<br>
</font></tt>
<br><tt><font size=2>> > Can you share the relevant queries?
<br>
> > <br>
> > It may be that you're using an inner join when you need and outer
<br>
> > join if you can;t create the related records before the re-query.
<br>
> > <br>
</font></tt>
<br><tt><font size=2>> This is the code with the prefetch commented
out. </font></tt>
<br><tt><font size=2>> <br>
> We were right. The prefetch is generating INNER JOINS, using the old<br>
> grammar, rather than the JOIN grammer, so if you do not have the <br>
> related records, you will not get the "master" records.</font></tt>
<br><tt><font size=2>> <br>
> select *</font></tt>
<br><tt><font size=2>> from foo, bar</font></tt>
<br><tt><font size=2>> where foo.id = bar.foo_id </font></tt>
<br><tt><font size=2>> <br>
> will only return records where the join condition (foo.id = <br>
> bar.foo_id) is true. But </font></tt>
<br><tt><font size=2>> <br>
> select *</font></tt>
<br><tt><font size=2>> from foo</font></tt>
<br><tt><font size=2>> left outer join bar on foo.id = bar.foo_id</font></tt>
<br><tt><font size=2>> <br>
> will return a row for every row of foo and will return the data
from<br>
> bar where the join condition is true, and tuples full of NULLs where<br>
> there is not bar row that satisfies the join condition.</font></tt>
<br><tt><font size=2>> <br>
> I'm going to punt here, and announce that implementing the outer <br>
> join in DBIX/SQLA is left as an exercise for the reader.</font></tt>
<br><tt><font size=2>> <br>
> But try the two queries above (modified for your database) from a
<br>
> SQL prompt and compare & contrast the results.</font></tt>
<br><tt><font size=2>> <br>
> Len.</font></tt>
<br><tt><font size=2>> - depending on your database, you might
need to select foo.*, bar.* <br>
> rather than just the generic *. in order to get the data from both
tables.</font></tt>
<br><tt><font size=2>> <br>
> Len. </font></tt>
<br><tt><font size=2>> <br>
> -- <br>
> lenjaffe@jaffesystems.com 614-404-4214
</font></tt><a href=www.volunteerable.net><tt><font size=2>www.volunteerable.net</font></tt></a>
<br><tt><font size=2>> Proprietor: </font></tt><a href=http://www.theycomewithcheese.com/><tt><font size=2>http://www.theycomewithcheese.com/</font></tt></a><tt><font size=2>
- An Homage to Fromage</font></tt>
<br><tt><font size=2>> Greenbar: Grubmaster: 2012-2009, Grub
Asst: 2008, Trained: 2007.</font></tt>
<br><tt><font size=2>> <br>
Strangely, I found that without the prefetch I am getting all the data,
it's just that the columns are null as you said. It seems to do the left
outer join when left to it's own devices. I can still access the fields
in the related tables from my templates. I just added some template code
to compensate for the null fields.</font></tt>