start smaller.....
post a model, some data, the DAL select statement and what you want back
as a resultset.
>From the looks of it, from statements are fine but there are far too many
group by
On Wednesday, May 7, 2014 10:13:02 PM UTC+2, Richard wrote:
>
> 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]
> <javascript:>>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] <javascript:>.
>> 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.
