Filter for lookup on object in many-to-many relationship

Question

I have two custom objects - A and B, which have many-to-many relationship, presented by a third custom object - A_B_Asoc. A ------> A_B_Asoc <------ B

In Opportunity, I have a lookup to A, whose value comes from the converted lead. I need to be able to select a value for B, the lookup being filtered only to B records, for which exists an association record in A_B_Asoc, for which A equals the value coming from the Lead. To clarify, the filter query would look like below in simple SQL:

SELECT Name from B WHERE B.ID IN (SELECT B_ID FROM A_B_Asoc WHERE A_ID = :Opportunity.A_ID)

The only working solution we have so far is embed a VF page in the standard layout, and in the extension controller get the list of relevant B records with a SOQL query. The downside is that in order to save the selected B value to the Opportunity record, we need to have a Save button in the VF page. Considering that the rest of the Opportunity record is presented in standard layout, which also has a default Save button, it is a very non-user-friendly solution.

What would be the best architectural/object-modelling approach to this use case, so that we could keep all in standard layout and yet achieve the requirement for such a filter?

I have looked at this similar question but no solution is provided there.

Answer 1

As I understand your question, you already have A.Id which is the Id of A_Id, and from the junction object that should provide you with one B.Id which is the Id of a B_ID record. At that point, it becomes a single record that you're looking for. If that's the case, I believe your query should look something like one of the two queries below:

B rcrdB = new rcrdB();

rcrdB = [SELECT B.Id, B.Name FROM B WHERE (B.Id =: B_ID__r) AND (B_ID__r IN: A_B_Asoc__r) 
     AND A_ID IN: A_B_Asoc__r) AND A_ID =: Opportunity.A_ID limit 1];

rcrdB = [SELECT B.Id, B.Name, A_ID__r, B_ID__r, A_B_Asoc__r FROM B WHERE (B.Id =: B_ID__r) 
     AND (A_ID__r =: Opportunity.A_ID) AND (B_ID__r IN: A_B_ASoc__r AND A_ID__r 
     IN: A_B_ASoc__r) limit 1]; 

I think the latter is closer to what you're looking for. Hopefully this will at least point you in the right direction.