ok, and since lists are by order, they'll be processed in that order from
index 0 on up. ok. thank you Jim S.
On Friday, September 11, 2020 at 12:26:31 PM UTC-4 Jim S wrote:
> 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/51bc20e6-8339-4e8f-9732-d7858626a5a9n%40googlegroups.com.
