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

Steve H s_t_e_v_e_h at hotmail.com
Sun Nov 4 08:50:29 EST 2007


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

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


More information about the jifty-devel mailing list