[Ur] queryL question

[Adam Chlipala]

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