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

[Andrew Sterling Hanenkamp]

Steve,

I'm not sure, but you may not need a "join" to do what you're asking to do.

On 11/4/07, Steve H <s_t_e_v_e_h at hotmail.com> wrote:
>
>
> Hi again
> (BTW, Andrew, thanks for your last help... I've been busy working with it
> and distracted by my next stumbling block).
> I've been puzzling over the manual and examples for how to express a
> simple join... I can't make sense of the Doxory example, for example,
> relating it to my simple context/example below.
> I seem to be losing the plot with where/whether Alias's are required,
> where Type comes into it, why/where 'main' is used in various examples
> (although I've seen that Jifty appears to use 'main' as a default alias),
> 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.
> I created a simple app to indicate the issue per below.  Some (code)
> fragments include:
>
> sqlite3 DB, created via Jifty:
> CREATE TABLE order_hdrs (
>   id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL  ,
>   oh_deliv_addr text
> );
> CREATE TABLE order_lines (
>   id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL  ,
>   ol_oh_pk integer   ,
>   ol_descr text   ,
>   ol_qty integer
> );
>
> Example of a simple SQL join:
> sqlite> select oh_deliv_addr, ol_descr, ol_qty from order_hdrs oh,
> order_lines ol where ol.id = oh.id;
> deliv addr1|descr1 for deliv1|1
> deliv addr2|descr2 for deliv1|2


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.

Example of a template/index.html to display via:
>   ol_descr   ol_qty
> % }
>
> </&>
>
> The Models used for this example include:
> ==> Model/OrderHdr.pm <==
> use strict;
> use warnings;
> package MyTest::Model::OrderHdr;
> use Jifty::DBI::Schema;
> use MyTest::Record schema {
>         column oh_deliv_addr =>
>                 type is 'text',
>                 label is 'Delivery Address';
>                 is mandatory,
>                 render_as 'Textarea';
> };
> # Your model-specific methods go here.
> 1;
>
> ==> Model/OrderLine.pm <==
> use strict;
> use warnings;
> package MyTest::Model::OrderLine;
> use Jifty::DBI::Schema;
> use MyTest::Record schema {
>         column ol_oh_pk =>
>                 type is 'integer',
>                 label is 'OrdrHdr PK',
>                 refers_to MyTest::Model::OrderHdr by 'id';
>         column ol_descr =>
>                 type is 'text',
>                 label is 'Descr';
>         column ol_qty =>
>                 type is 'integer',
>                 label is 'Qty';
> };
> # Your model-specific methods go here.
> 1;
>
>
>
> _________________________________________________________________
> Your Future Starts Here. Dream it? Then be it! Find it at www.seek.com.au
>
> http://a.ninemsn.com.au/b.aspx?URL=http%3A%2F%2Fninemsn%2Eseek%2Ecom%2Eau%2F%3Ftracking%3Dsk%3Ahet%3Ask%3Anine%3A0%3Ahot%3Atext&_t=764565661&_r=OCT07_endtext_Future&_m=EXT_______________________________________________
> jifty-devel mailing list
> jifty-devel at lists.jifty.org
> http://lists.jifty.org/cgi-bin/mailman/listinfo/jifty-devel
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.bestpractical.com/pipermail/jifty-devel/attachments/20071105/3d69d08b/attachment.htm