[web2py] Re: need help with query
Solved it by using 2 seperate queries: rij = db().select( db.track_trace.id_reparaties, db.track_trace.id_status, db.track_trace.created_by, max, groupby=~db.track_trace.id_reparaties, having=(db.track_trace.id_status == 4)) ids = [] for item in rij: ids.append(item.track_trace.id_reparaties) rijen = db((db.reparaties.created_by==auth.user.id)(db.reparaties.id.belongs(ids))).select() po = len(rijen) -- 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 web2py+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout.
[web2py] Re: need help with query
Thanks you almost had it, max = db.track_trace.created_on.max() having = (db.track_trace.id_status == 11) rij = db().select(db.track_trace.id_reparaties, db.track_trace.id_status, max, groupby=~db.track_trace.id_reparaties,having=(db.track_trace.id_status == 11)) The above code is working and gives me the correct amount of rows that have that status as their newest. However, when I want to do a join in this query the id_status column shows the first status entered and not the last: I use this query below, it gives the newest status for that reparaties_id(with join): rij = db().select(db.track_trace.id_reparaties, db.track_trace.id_status, db .track_trace.created_by, max, db.reparaties.created_by, groupby=~db. track_trace.id_reparaties) With join: track_trace.id_reparaties,track_trace.id_status,track_trace.created_by,MAX(track_trace.created_on),reparaties.created_by 12794,1,4,2014-03-13 09:05:33,1 12793,1,5,2014-03-12 14:14:38,1 12792,1,3,2014-03-12 13:20:43,1 12791,1,3,2014-03-12 13:20:34,1 12790,1,5,2014-03-12 15:47:22,1 query without join: rij = db().select(db.track_trace.id_reparaties, db.track_trace.id_status, db .track_trace.created_by, max, groupby=~db.track_trace.id_reparaties) without join: track_trace.id_reparaties,track_trace.id_status,track_trace.created_by,MAX(track_trace.created_on) 12794,1,4,2014-03-13 09:05:33 12793,1,5,2014-03-12 14:14:38 12792,3,8,2014-03-12 13:20:43 12791,3,8,2014-03-12 13:20:34 12790,13,8,2014-03-12 15:47:22 You can see that in the first example the second column is all 1. In the second example without the join it gives me the correct status numbers in the second column. Why does it behave differently with the join or am I missing something here? Op donderdag 6 maart 2014 16:10:02 UTC+1 schreef 黄祥: perhaps something like : *not tested* max = db.track_trace.created_on.max() max_result = db().select(max).first()[max] query_having = (db.track_trace.id_status == 11) db().select(db.track_trace.id_reparaties, db.track_trace.id_reparaties, max_result, groupby = db.track_trace.id_reparaties, having = query_having) best regards, stifan -- 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 web2py+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout.
[web2py] Re: need help with query
And again everything gets deleted.. I don't understand -- 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 web2py+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/groups/opt_out.
[web2py] Re: need help with query
hmm another reply just gets deleted, immediately after posting(something wrong with my other account I think) I tried somethings now and I am a step closer: max = db.track_trace.created_on.max() recs = db().select(db.track_trace.id_status, max, groupby=db.track_trace. id_reparaties) Now I get the newest created_on time/date but not the id_status that belongs to that date, I just get the first id_status that belongs to that id_reparaties -- 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 web2py+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/groups/opt_out.
[web2py] Re: need help with query
I tried some variations but no correct output so far max = db.track_trace.created_on.max() recs = db().select(max,db.track_trace.id_status, groupby=db.track_trace. id_reparaties) With this I get the max date, but not the id_status that belongs to that date -- 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 web2py+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/groups/opt_out.
[web2py] Re: need help with query
Hi, I made the query with SQL: select id_reparaties, id_status, max(created_on) from track_trace group by id_reparaties desc having id_status=11 this gives me the correct results as far as I can see. How do I translate this into a web2py dal query? Op donderdag 27 februari 2014 13:47:46 UTC+1 schreef Bastiaan: -- 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 web2py+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/groups/opt_out.
[web2py] Re: need help with query
perhaps something like : *not tested* max = db.track_trace.created_on.max() max_result = db().select(max).first()[max] query_having = (db.track_trace.id_status == 11) db().select(db.track_trace.id_reparaties, db.track_trace.id_reparaties, max_result, groupby = db.track_trace.id_reparaties, having = query_having) best regards, stifan -- 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 web2py+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/groups/opt_out.
[web2py] Re: need help with query
I'll try to make it better ;). For example I have the following list: id | id_reparaties | id_status | created_on 1 | 1 | 1 | 01-01-14 2 | 1 | 3 | 15-01-14 3 | 2 | 1 | 02-01-14 4 | 3 | 1 | 16-01-14 5 | 1 | 5 | 18-01-14 6 | 2 | 2 | 04-01-14 7 | 2 | 3 | 10-01-14 8 | 2 | 4 | 19-01-14 9 | 3 | 4 | 17-01-14 I want to count the number of id_reparaties that have status 4 as the newest. The answer would be 2 then(id_reparaties 2 and 3 both have status 4 as the newest item. id-reparaties 1 has status 5 as the newest.) Op vrijdag 28 februari 2014 21:33:23 UTC+1 schreef Niphlod: your request kinda sound bad. if you want to: - filter by id_status - group by id_reperaties - count lines then it's fine. That have the newest item is the part that doesn't sound right. The newest item is always going to be one and only one, so counting would just return 1 for every group ^___^ -- 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 web2py+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/groups/opt_out.
[web2py] Re: need help with query
hmm somehow my reply got deleted.. I want to count the groups that have a certain id_status as their newest item. The group will indeed give 1 item, but I want to know how many groups. -- 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 web2py+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/groups/opt_out.
[web2py] Re: need help with query
This must be a googlegroup bug. We did not delete them. On Tuesday, 4 March 2014 14:30:54 UTC-6, Bastiaan van der Veen wrote: Somehow my replies all got deleted. I want one item per group(the newest) and then filter by id_status and count how many groups have that certain id_status. This should be possible i think. I had a nice example in one of those deleted post, but i don't know how i can get that back. -- 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 web2py+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/groups/opt_out.
[web2py] Re: need help with query
your request kinda sound bad. if you want to: - filter by id_status - group by id_reperaties - count lines then it's fine. That have the newest item is the part that doesn't sound right. The newest item is always going to be one and only one, so counting would just return 1 for every group ^___^ -- 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 web2py+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/groups/opt_out.
[web2py] Re: Need help with query
On 24 jul, 19:01, weheh richard_gor...@verizon.net wrote: I have a list, x, with allowed values for field table.x. Is there a succinct way to do something like this? db(db.table.x in x).select() Hi, You must use the operator Belongs. Please see here [1] Regards Jose [1] http://www.web2py.com/book/default/section/6/10?search=belong
[web2py] Re: Need help with query
Thanks Jose, I just figured it out.
