This SQL failed :
SELECT table_number.number_computed,
view_number_part2.part2_number,
AVG(table_value.field_value) AS field_value
FROM table_number
JOIN view_number_part2 ON (view_number_part2.number_part2_id =
number_part2.id)
LEFT JOIN number_part2 ON (number_part2.lot_number_id = table_number.id)
LEFT JOIN table_value ON (table_value.number_part2_id = number_part2.id)
LEFT JOIN intermediate_table1 ON (intermediate_table1.id =
number_part2.intermediate_table1_id)
LEFT JOIN intermediate_table2 ON (intermediate_table2.id =
number_part2.intermediate_table2_id)
WHERE (table_number.number_computed IN ('number1', 'number2'))
GROUP BY table_value.number_part2_id,
view_number_part2.part2_number,
table_number.number_computed,
intermediate_table2.number_part2_order,
number_part2.date
ORDER BY intermediate_table2.number_part2_order,
number_part2.date;
With error :
ERROR: missing FROM-clause entry for table "number_part2"
If I correct the query by moving the JOIN below the LEFT JOIN and it
works...
table_number containt essentially a list of number,
number_part2 a second particul added to number,
the view_number_part2 is a view (postgres view define as a web2py table)
that only purpose is to display a concatenation of number and part2_number
(I know it seems useless, but for other consideration it is essential)
table_value containt the interresting value
intermediate_table1 and 2 are there for groupby and are not important for
the problem...
One-to-one relation between number_part2 and view_number_part2 lead me
intuitively to create a join... I could workaround the problem by making a
LEFT JOIN and put it below this "LEFT JOIN number_part2 ON
(number_part2.lot_number_id = table_number.id) "
Hope it can be understand.
Richard
On Wed, May 7, 2014 at 10:44 AM, Niphlod <[email protected]> wrote:
> it's not a matter of order, it's a matter of how the select gets
> constructed in the code. I'd still like to see what query presents this
> "issue" because ideally there shouldn't be problems with the current
> implementation.
>
>
> On Wednesday, May 7, 2014 3:35:50 PM UTC+2, Richard wrote:
>
>> Yes, to me in the context of my particular select it was making issue
>> that the order was not respected. However, as a workaround I can just
>> define my join as a left join because my join was a one-one relation, but I
>> see that it could happen I could just not get the proper SQL from my web2py
>> query...
>>
>> I guess that the keyed elements in the select() should be collected in a
>> dict, so the order get loss??
>>
>> Richard
>>
>>
>> On Wed, May 7, 2014 at 4:43 AM, Niphlod <[email protected]> wrote:
>>
>>> there isn't any order defined between left and "normal" joins.
>>> the exact same select would be created either with
>>>
>>> db().select(left=[], join=[])
>>>
>>> or
>>>
>>> db().select(join=[], left=[])
>>>
>>> If that's what you're pointing, you're out of luck ^_^
>>>
>>>
>>> On Tuesday, May 6, 2014 10:19:51 PM UTC+2, Richard wrote:
>>>>
>>>> Hello,
>>>>
>>>> I just struggle more than I would with this :
>>>>
>>>> db(...).select(...,
>>>> left=[left1,
>>>> left2,
>>>> ...],
>>>> join=join)
>>>>
>>>> I would expect that the JOIN be after the LEFT JOINS in SQL generated,
>>>> since one of my left join was required to be before the join...
>>>>
>>>> Is there a reason why web2py change the order of join and left join ?
>>>>
>>>> Thanks
>>>>
>>>> Richard
>>>>
>>>>
>>>> --
>>> 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].
>>>
>>> For more options, visit https://groups.google.com/d/optout.
>>>
>>
>> --
> 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].
> For more options, visit https://groups.google.com/d/optout.
>
--
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].
For more options, visit https://groups.google.com/d/optout.
