Sorry for the delayed response; when I first looked into your problem,
it seemed harder than it does now, on my second try!
I think my answer to your follow-up question wraps everything up nicely
for your example. I only need to write the query like this to get
everything to compile fine:
val q = queryL (SELECT Ss.LongName, C.ShiftDate, U1.LastName, U2.LastName
FROM contract AS C
LEFT JOIN user AS U1 ON U1.Id = C.Seller
LEFT JOIN user AS U2 ON {sql_nullable (SQL U2.Id)} =
C.Buyer
LEFT JOIN shift AS Ss ON C.Shift = Ss.Id)
P.S.: There isn't anything so specific to [queryL] here. I think your
question was about the SQL embedding in general.
On 07/15/2015 10:04 PM, Todd Roth wrote:
I’m unable to get a pretty straighforward LEFT JOIN query to work in ur/web and
would appreciate any help. The table setup is as follows:
table user : { Id : int, LastName : string, FirstName : string }
PRIMARY KEY Id
table shift : { Id : int, ShortName : string, LongName : string, Hours: int,
Created : time , Modified : time}
PRIMARY KEY Id
table contract : {Id : int, Shift : int, ShiftDate : time, Seller : int, Buyer
: option int, SettledOn : option time, Created : time, Modified : time }
PRIMARY KEY Id,
CONSTRAINT Shift FOREIGN KEY Shift REFERENCES shift(Id),
CONSTRAINT Buyer FOREIGN KEY Buyer REFERENCES user(Id),
CONSTRAINT Seller FOREIGN KEY Seller REFERENCES user(Id)
I want the query to return the following columns:
shift.LongName, contract.ShiftDate, user.LastName( for seller),
user.LastName(for buyer)
The following psql query works as expected but I can’t figure out the syntax
for ur/web.
psql query:
SELECT ss.uw_longname, c.uw_shiftdate, u1.uw_lastname, u2.uw_lastname FROM
uw_shiftxchange_contract AS c
LEFT JOIN uw_shiftxchange_user AS u1 on u1.uw_id = c.uw_seller
LEFT JOIN uw_shiftxchange_user AS u2 on u2.uw_id = c.uw_buyer
LEFT JOIN uw_shiftxchange_shift AS ss on c.uw_shift = ss.uw_id
_______________________________________________
Ur mailing list
[email protected]
http://www.impredicative.com/cgi-bin/mailman/listinfo/ur
