WHERE clause for field of the “one” side of a OneToMany relationship

  • Resolved webdevop

    (@webdevop)


    2 years, 6 months ago

    Hello,

    I struggle with this for some time now.
    The forum seems not to have an answer yet.

    Usually this means it is either a very special and complicated problem OR
    it’s so extremely simple that nobody else has a problem with it. ??

    I have a pod named “event” that has a one-to-many-relationship with another pod named “attendee“.
    So I added a multi select relationship field to “event” and a single select relationship field to “attendee”.

    It all works really well.

    When editing an event, the multi select field gives me a “List View input” of attendees assigned to the current event. Nice.
    I can reorder them, edit them, remove them and even add completely new attendees. Very nice.

    BUT:
    Above the attendee list I get an search/dropdown element where I can add existing attendees to the event.

    This list however contains ALL existing attendees. Even those that are already assigned to another event.
    I don’t want to re-assign attendees from their event to the event I’m currently editing.
    It could be a useful feature but I don’t need it.

    So the list should only contain attendees that are either NOT assigned to any event or attendees that are already assigned to my current event.

    So I thought I would simply limit the attendees in the dropdown by adding some conditions to the custom WHERE clause of the field.
    Since the SELECT statement tries to load “attendee” pods at this point, the logical WHERE clause would be “event.ID IS NULL OR event.ID = {???}”

    The “{???}” part is what I’m struggling with.
    It should reference the ID of the event I’m currently editing.
    But for some reason I can’t get that reference from the system.

    I tried to do it with magic tags ({@ID} and {@_pod.id} seemed obvious) but none of them worked.

    Do you have an idea if it is achievable at all and if yes how it should be done?

Viewing 6 replies - 1 through 6 (of 6 total)
  • Plugin Author Jory Hogeveen

    (@keraweb)

    Hi @webdevop

    This is not possible through our UI. You’ll need to manually filter the actual query made to the database where you check for an existing event ID.
    As you can imagine, this is considered advanced usage.

    Alternatively you can make both an multi-relationship and allow attendees to be added to multiple events. To be honest, this sounds logical to me ??

    Cheers, Jory

    Thread Starter webdevop

    (@webdevop)

    Hi Jory,

    thanks for your reply.

    It’s a pity.

    I also already thought about making it a ManyToMany relationship.
    But then I would have to make the attendee system wide unique and I could not store information on the attendee that differ from event to event.

    I will keep investigating. Maybe I can do some CSS tricks to hide the unwanted attendees.

    Anyways, the PODS plugin is great. Looking forward to v3.0 ??

    Cheers

    Thread Starter webdevop

    (@webdevop)

    But … um … if you could describe how you would manually filter the SQL query.

    Are there some hooks in the plugin code that I could use to design my own query maybe?

    • This reply was modified 2 years, 6 months ago by webdevop. Reason: corrected typo
    Plugin Author Jory Hogeveen

    (@keraweb)

    Hi @webdevop

    See this function for more info on how Pods handles getting object data: https://github.com/pods-framework/pods/blob/main/classes/fields/pick.php#L2218-L2843

    And then especially this filter: https://github.com/pods-framework/pods/blob/main/classes/fields/pick.php#L2292

    Cheers, Jory

    Thread Starter webdevop

    (@webdevop)

    This looks promising.
    I’ll dig into it.

    Thanks for the pointers. ??

    Thread Starter webdevop

    (@webdevop)

    Hello Jory ( @keraweb ),

    I’m happy to announce that I found a solution:

    function my_filter_pods_attendee_params($params) {
  if ($params['name'] == 'pods_meta_attendee') {
    $id = $params['pod']->id;
    $params['options']['_field_object']->pick_where = "(event.ID IS NULL OR event.ID='".$id."')";
  }
  return $params;
}
add_filter('pods_field_pick_object_data_params', 'my_filter_pods_attendee_params', 1, 1);

    It was a bit of a nightmare to figure this out, but it works, so it can’t be completely wrong. ??

Viewing 6 replies - 1 through 6 (of 6 total)
  • The topic ‘WHERE clause for field of the “one” side of a OneToMany relationship’ is closed to new replies.