it is almost like you need another object, SubTable, which can be
built from the results of a Set object.
But SubTable can do anything a regular table can do, its information
is just dynamic.
mySubTable = db()._select(db.TasksTimeLog.TheDate,
db.TasksTimeLog.User_id,
db.TaskTimeLog.HoursWorked.sum().with_alias('DaysHoursSum),
orderby=db.TasksTimeLog.TheDate|db.TasksTimeLog.User_id)
db.define_sub_table(mySubTable)
db(Tasks_TimeLog.TheDate ==
db.mySubTable.TheDate)(Tasks_TimeLog.User_id ==
db.mySubTable.User_id)(Tasks_TimeLog.Task ==
'2').select(db.mySubTable.DaysHoursSum, db.Task_TimeLog.HoursWorked)
Well... Its just an idea.
--
Thadeus
On Tue, Apr 13, 2010 at 1:51 PM, mdipierro <[email protected]> wrote:
> The problem from the DAL point of view is not so much building the
> queries but referring to fields of a tmp table since there is no
> mechanism for it.
> I will think some more about it.
>
> On Apr 13, 1:36 pm, Thadeus Burgess <[email protected]> wrote:
>> I have no immediate use for it.
>>
>> However, basing queries off of sub-queries(sub-table) happens a lot in
>> the ms-access world.
>>
>> --
>> Thadeus
>>
>> On Tue, Apr 13, 2010 at 1:08 PM, mdipierro <[email protected]> wrote:
>> > It would be difficult but possible. Is this a priority?
>>
>> > On Apr 13, 10:20 am, Thadeus Burgess <[email protected]> wrote:
>> >> Interesting, what would be the possibility of adding this as a feature
>> >> to the new dal?
>>
>> >> --
>> >> Thadeus
>>
>> >> On Tue, Apr 13, 2010 at 5:06 AM, canna <[email protected]> wrote:
>> >> > Hello everybody!
>>
>> >> > I really need help with a query I'm trying to execute in Web2Py DAL
>> >> > is there a way to use an inline table in the FROM field of a query?
>>
>> >> > this is my query:
>>
>> >> > SELECT SUM( HoursWorked / DayHoursSum ) AS DaysWorked
>> >> > FROM `Tasks_TimeLog` H, (
>>
>> >> > SELECT TheDate, User_id, SUM( HoursWorked ) AS DayHoursSum
>> >> > FROM `Tasks_TimeLog`
>> >> > GROUP BY TheDate, User_id
>> >> > )S
>> >> > WHERE H.User_id = S.User_id
>> >> > AND H.TheDate = S.TheDate
>> >> > AND Task='2'
>> >> > GROUP BY Task
>>
>> >> > this is the inline table in Web2py:
>>
>> >> > inlineTable=db()._select(db.Tasks_TimeLog.TheDate,db.Tasks_TimeLog.User_id,db.Tasks_TimeLog.HoursWorked.sum(),groupby=db.Tasks_TimeLog.TheDate|
>> >> > db.Tasks_TimeLog.User_id)
>>
>> >> > how do I use the inline table in my full query??
>>
>> >> > rows=db(db.Tasks_TimeLog.Task=='2'.....).select(.....,groupby=db.Tasks_TimeLog.Task)
>>
>> >> > Thanks to all the helpers!!
>>
>> > --
>> > To unsubscribe, reply using "remove me" as the subject.
>
