WEB2PY: Displaying data from DB with JOIN but with some condition

Question

I am having trouble with some logic.

1. I have 3 tables: db.service_request, db.technician and db.assigned
2. Many Service Request can be assigned to a Technician
3. The table db.assigned is where all assigned service_requestare saved together with the Technician's ID
4. Now I want to display all data in service_request EXCEPT for the service_request that was not assigned to the current logged In Technician

How someone please tell me how to make this work ?

What I have is this, I get all service request that has been assigned to the current logged in Technician, but does not get all the other service request that has not yet been assigned.

query=db.service_request.id==db.assigns.sr_id
query2=db.assigns.technician==user_id
query4=((query)&(query2))
get_assigned=db(query4).select(orderby=~db.service_request.date_time)

Answer 1

You can do a left outer join and specify the condition that either db.assigned.technician matches the current user or there is no matching record in db.assigned:

left_join = db.assigned.on(db.service_request.id == db.assigned.sr_id)
current_user_assigned = db.assigned.technician == user_id
request_not_assigned = db.assigned.id == None
rev_request_time = ~db.service_request.date_time
rows = db(current_user_assigned | request_not_assigned).select(left=left_join,
                                                               orderby=rev_request_time)