using inline tables in the query (subquery in the FROM field)

[canna]

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!!

[mdipierro]

DAL cannot do this. You need to use db.executesql("...."). sorry.

[canna]

Thank you! I thought I was missing something....
you right, I used executesql anyway in case my question wont be
answered :-)
Thank you again for clearing this up!

[Thadeus Burgess]

Interesting, what would be the possibility of adding this as a feature
to the new dal?

--
Thadeus

[mdipierro]

It would be difficult but possible. Is this a priority?

On Apr 13, 10:20 am, Thadeus Burgess <thade...@thadeusb.com> wrote:
> Interesting, what would be the possibility of adding this as a feature
> to the new dal?
>
> --
> Thadeus
>

[Thadeus Burgess]

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

> --
> To unsubscribe, reply using "remove me" as the subject.
>

[mdipierro]

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 <thade...@thadeusb.com> 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
>

[Thadeus Burgess]

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

[mdipierro]

I think this is way to do it. We can add this to the new DAL although
it is not yet a priority.

On Apr 13, 2:11 pm, Thadeus Burgess <thade...@thadeusb.com> wrote:
> 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
>

[Thadeus Burgess]

Can you add this to the appropriate TODO list with a link to this group posting?

--
Thadeus

[Richard]

is there such a list?
In case not I have added it to the user voice page:
http://web2py.uservoice.com/forums/42577-general/suggestions/648813-inline-query-tables

On Apr 14, 12:26 pm, Thadeus Burgess <thade...@thadeusb.com> wrote:
> Can you add this to the appropriate TODO list with a link to this group posting?
>
> --
> Thadeus
>