You're welcome, glad to help
On Friday, September 11, 2020 at 11:47:14 AM UTC-5, lucas wrote:
>
> 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/5653c0bc-103a-4111-9829-5d97d8bde8bbo%40googlegroups.com.
