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
On Friday, September 11, 2020 at 10:48:16 AM UTC-5, lucas wrote:
>
> 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/2515c29b-0d0b-4566-a89c-f502af83a14co%40googlegroups.com.
