[Ur] queryL question

[Todd Roth]

Thanks, that did clear everything up.  

  
> On Jul 19, 2015, at 3:35 PM, Adam Chlipala <adamc at csail.mit.edu> wrote:
> 
> 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
> Ur at impredicative.com
> http://www.impredicative.com/cgi-bin/mailman/listinfo/ur