I already explained somewhere in this thread - or the other thread about dbjoin - why I was using a placeholder from an element in the same repeat group. I needed the dbjoin element to be populated with all the rows from the joined table EXCEPT rows that already contained a value already being used in one of the other repeat groups.
In other words, the value for that dbjoin element had to be unique in each of the repeat groups. Pretty basic stuff, or so I thought.
But, if it was an edit , when populating the dbjoin - you would want the where clause to also include the existing value in the list returned - else the dbjoin link would never work.
So since the "Only when new" option only works for a new record in the parent list, I had to make it so the "Apply Where when" option was set to 'Both'. And so, in that case, I had to including an additional OR to also include the current value of the dbjoin for that repeat group being edited in the list of rows returned by the plugin. Or so I thought. Where else would I get that value to compare except the placeholder for that element?
I posted this before too - the actual Where clause I was using in a dbjoin element named 'facility_type_id' - contained in the list 'fb_breakout_groups'. The table used for the dbjoin is named 'fb_facility_types' - and I was getting the 'id' as the Value and the 'facility_type' as the Label.
This was written assuming that the values for the record from fb_breakout_groups (the same group the dbjoin element was in) would be the values returned from for that current row (repeat group) - not the array of values from the parent list! And it works fine if those values are used in lieu of the placeholders.
WHERE
SELECT fb_member_details_repeat_facility_checkboxes.facility_checkboxes AS fid FROM fb_member_details_repeat_facility_checkboxes
LEFT JOIN fb_member_details ON fb_member_details_repeat_facility_checkboxes.parent_id = fb_member_details.id
WHERE fb_member_details.user_id={fb_breakout_groups___user_id})
AND (fb_facility_types.id={fb_breakout_groups___facility_type_id_raw} OR fb_facility_types.id
NOT IN (SELECT fb_breakout_groups.facility_type_id FROM fb_breakout_groups WHERE fb_breakout_groups.report_id={fb_breakout_groups___report_id}))
Now to play devil's advocate here. If the rule is that the values returned by the placeholder of a repeat group is a comma delimited string of the array values from the parent list (as it is), then I could use a WHERE clause that would be a bit simpler...
WHERE
SELECT fb_member_details_repeat_facility_checkboxes.facility_checkboxes AS fid FROM fb_member_details_repeat_facility_checkboxes
LEFT JOIN fb_member_details ON fb_member_details_repeat_facility_checkboxes.parent_id = fb_member_details.id
WHERE fb_member_details.user_id={$my>id})
AND fb_facility_types.id NOT IN ({fb_breakout_groups___facility_type_id})
BUT - How do I filter that so the final AND clause also includes an OR (to also include the current value of the record being edited - so the dbjoin will be able to pick-up that currently linked value and work as expected)?
Maybe the solution for this lies with naming placeholders? Could there be a placeholder used exclusively for the currently selected repeat group values? e.g. {fb_breakout_groups___fb_repeat___id}
Mine is a complex example - but I'm sure there are instances where a user would need to filter the values returned by a dbjoin list (using the Where feature of dbjoin) that required comparing a value in the existing table/record the dbjoin element is in.
I can't see how Rob doesn't see this as a problem.
