[jifty-devel] re: Re: how to do simple SQL joins?

[Todd Chapman]

This is what I would do. Look at the branch of RT that uses Jifty.
There are some complex queries their which should help. In particular,
this file:

http://svn.bestpractical.com/cgi-bin/index.cgi/bps/view/rt/branches/3.999-DANGEROUS/lib/RT/Model/TicketCollection.pm

-Todd

On 11/6/07, Steve H <s_t_e_v_e_h at hotmail.com> wrote:
>
> Hi Andrew.  Thanks for the reply.
>
> > I'm not sure, but you may not need a "join" to do what you're asking to do.
> ...
> >> etc, etc, ...I've tried many, many combinations, resulting in errors (from
> >> bin/jifty server) ranging from "Unsatisfied dependency chain in joins"
> >> through to garbled SQL being generated (that fails 'prepare'). Although
> >> I've been hand-coding SQL for years... I don't seem to be getting the hang
> >> of this abstraction from the Jifty docs sans examples.
> ...
> > You should be able to just iterate over order lines:
> >
> > my $order_lines = MyTest::Model::OrderLineCollection->new;
> > # Add any limits you want here...
> > while (my $order_line = $order_lines->next) {
> > Jifty->web->out(join(' : ', $order_line->ol_oh_pk->oh_deliv_addr,
> > $order_line->ol_descr, $order_line->ol_qtr), "\n");
> > }
> >
> > You could use a join to do this, but your test case makes it appear that
> > just this code should do the job.
> >
> I couldn't manage to 'stumble on' the appropriate Join syntax to express it though, despite reading, reading and re-reading the manual and Googling.
>
> The way you suggested above seems really obvious once shown :(
> I revisited the obvious doco, such as Jifty::DBI (Collection, Record, etc), to look for where I may have missed it... however I still couldn't find where it states this is how you do such a simple Join.  Maybe I'm disadvantaged by starting thinking about it by constructing the raw SQL then trying to figure out how Jifty::DBI can generate it... although I don't know which other way to approach it.  ...and it leaves me wondering where I might look for how it handles things like outer joins, or returns amongst other cols, a col that is say true/false on whether a sub select 'exists' in some other table... and and a trillion other oft' used constructs.
> I'm not looking for Jifty::DBI to do things it doesn't or can't... merely to know how to express in Jifty::DBI, the things it can do (and/or how to work around), from the perspective of knowing what data I need and what SQL would otherwise fetch it.  Are there any how-to's, cook-books, annotated code snippets, etc, etc that I could be pointed at?? that would save me from asking what would appear to others to be the obvious.
>
> Another thing... can you indicate how I might feed back doco/examples?  I'd like to some lessons learned... Surely I'm not alone in encountering such issues ... although I may be a bit more stubborn in my resolve to use Jifty rather than click on.
>
> thanks again
> SteveH
> _________________________________________________________________
> New music from the Rogue Traders - listen now!
> http://ninemsn.com.au/share/redir/adTrack.asp?mode=click&clientID=832&referral=hotmailtaglineOct07&URL=http://music.ninemsn.com.au/roguetraders_______________________________________________
> jifty-devel mailing list
> jifty-devel at lists.jifty.org
> http://lists.jifty.org/cgi-bin/mailman/listinfo/jifty-devel
>