SQL db select multiple inner and an left outer joins

[lucas]

hey all,

i know i can run the following raw postgresql statement with db.executesql.  but is there a way to do this multiple inner and one left outer join using standard db((...) & (...) & ...).select(... left=db.folm((...) &(...) & ...)) kind of syntax somehow?  i tried the various obvious ways but it either crashes the server with 502 or gives the wrong results.  thanx in advance, lucas

select c.title, c.subtitle, cg.id, cg.name, u.last_name, u.first_name, l.user_id, l.date_generated, l.date_completed, l.lab_manual, l.lab 

from classes c 

inner join class_groups cg on (c.id = cg.class_id) 

inner join class_group_users cgu on (cg.id = cgu.class_group_id) and (cgu.accepted = 'T') 

inner join auth_user u on (cgu.user_id = u.id) 

left outer join folm l on (cgu.id = l.class_group_user_id)  and (l.lab_manual = 'manual1') and (l.lab = 'FOLM01.html')

where (c.lab_manual like '%CHM1025%')

order by cg.id, u.last_name, u.first_name;

[Jim S]

This may make me sound like a horrible developer, but I never use INNER joins.  And when I say never, I mean NEVER.  I have nothing against them, it's just that I can accomplish everything I want to do with LEFT joins.  In your example for joining table class_groups I'd do something like this instead:

LEFT OUTER JOIN class_groups cg ON c.id = cg.class_id

And then in my WHERE clause I'd include:

AND cg.class_id > 0

Then, once you have it rewritten using LEFT joins only, it should be trivial to convert that to the DAL select statement.  

Probably not ideal, but this is what I'd do.

-Jim

[lucas]

hey Jim S, and others,

i've restructed the sql statement to:

select c.title, c.subtitle, cg.id, cg.name, u.last_name, u.first_name, u.id, u.email, l.id, l.date_generated, l.date_completed, length(l.lab_data) 

from class_group_users cgu 

left outer join auth_user u on (cgu.user_id = u.id) 

left outer join class_groups cg on (cgu.class_group_id = cg.id) 

left outer join classes c on (cg.class_id = c.id) 

left outer join folm l on (cgu.id = l.class_group_user_id) and (l.lab_manual = 'manual1') and (l.lab = 'FOLM01.html')

where (cgu.accepted = 'T') and (c.starting_date <= '2020/09/11') and ('2020/09/11' <= c.ending_date) and (c.lab_manual like ('%CHM1025%')) 

order by cg.id, u.last_name, u.first_name;

now, how can i implement all of those "left"s in the db().select() statement?  can it handle more then one left and does it take them in order?  and does the "where" part of the sql statement go under the db() part?

lucas

[Jim S]

Yes, you can just put them in a list:

left=[db.auth_user.on(db.auth_user.id == db.class_group_users.user_id), db.class_groups.on(db.class_groups.id == db.class_group_users.class_group_id), db.classes.on(db.classes.id == db.class_groups.class_id), etc..]

-Jim

[lucas]

ok, and since lists are by order, they'll be processed in that order from index 0 on up.  ok.  thank you Jim S.

[Jim S]

You're welcome, glad to help