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
On Friday, September 11, 2020 at 10:19:02 AM UTC-4 Jim S wrote:
> 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
>
>
>
> On Friday, September 11, 2020 at 8:54:41 AM UTC-5, lucas wrote:
>>
>> 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;
>>
>
--
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
---
You received this message because you are subscribed to the Google Groups
"web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/web2py/8166fde5-1c27-48e5-9ef2-c238ccea39a4n%40googlegroups.com.
