One-to-many where condition

we0038

Hi Allan,
I searched everything related to using where condition with Mjoin and it seems not supported although it’s listed in the php documentation under methods inherited https://editor.datatables.net/docs/1.8.0/php/class-DataTables.Editor.MJoin.html

Anyway, to overcome this issue, whenever I need where condition I just create a view with that condition. Then do my Mjoin on that view.

However, I’ve got into a new use-case where my where condition is dependent on another field. Here’s a simplified explanation:

Assume I have a table tbl_services_provided

id, customer_id, service_provided, price, is_paid

tbl_invoices

id, customer_id, total_price

tbl_invoice_services

invoice_id, service_id

My approach is to create a new invoice in table tbl_invoices which has the Mjoin
My form has two fields
1. customer_id
2. tbl_services_provided[].service_provided (as checkbox)

If I was able to use where condition with Mjoin I could use $_POST[‘customer_id’] so on create form’s second field should be empty. Then on edit, I should get a list of unpaid services for this customer to choose from.

Can anyone suggest a workaround or advise.
Thanks.

allan

An Mjoin condition should work okay - e.g.:

    ->join(
        Mjoin::inst( 'permission' )
            ->link( 'users.id', 'user_permission.user_id' )
            ->link( 'permission.id', 'user_permission.permission_id' )
            ->order( 'name asc' )
            ->fields(
                Field::inst( 'id' )
                    ->validator( Validate::required() )
                    ->options( Options::inst()
                        ->table( 'permission' )
                        ->value( 'id' )
                        ->label( 'name' )
                    ),
                Field::inst( 'name' )
            )
            ->where( 'permission.name', 'Access' )
    )

It worth noting that this will limit the values joined, but it will not limit the rows retrieve from the parent table. Is that the issue you are running into?

The only way you can do what you are looking for now, as far as I am aware, is to use a Common Table Expression as a VIEW since that allows you to do a sub-select based on the values of the rows. That is not something that Editor does out of the box I'm afraid.

The way I typically approach this sort of thing myself is like this. Its a bit larger in terms of the UI presented to the user, but it will work out of the box.

Allan

we0038

Thanks Allan for replying,
considering your example, what I am trying to do is manipulating the options returned based on user.id

So, my approach now is using dependent and ajax
1- initCreate: clear all options
2- dependent: will update the options via ajax call according to user.id selected
it works fine, and it will show checkbox ticks if the value is already in the Mjoin table.

However, there's this weird thing, when alternating between rows the first time edit all checkboxes are unchecked. when close and click edit again then it will match the Mjoin table.

allan

what I am trying to do is manipulating the options returned based on user.id

AH!

In that case yes, using dependent() is the way to do it. Editor's libraries don't have a way to load all of the options per row (which would likely be really slow even if it did).

However, there's this weird thing, when alternating between rows the first time edit all checkboxes are unchecked. when close and click edit again then it will match the Mjoin table.

That I can't explain! Can you give me a link to a test case so I can take a look please?

Thanks,
Allan

we0038

Hi Allan,
thank you for the support.

I think I understand what's going on here. I replicated the issue using one-to-many example here https://editor.datatables.net/examples/advanced/joinArray.html
by adding this to the js
editor.dependent( 'users.site', '../../test.php' );

where the test.php is

<?php 

switch($_REQUEST['values']['users.site']){
  case 6: // 2 is Los Angeles
    $data = [ ["value"=>3, "label"=>"Desktop"], ["value"=>1, "label"=>"Printer"], ["value"=>5, "label"=>"Web-site"] ];
    break;
  case 5: // 5 is SINGAPORE
    $data = [ ["value"=>4, "label"=>"VMs"], ["value"=>6, "label"=>"Accounts"] ]; 
    break;
    
  default: 
    $data = [ ];
}

echo json_encode( [
  'options' => [
    'permission[].id' => $data
  ]
]);

My goal here (in this example) is that each site has different list of permissions than the others. In this case, if you try to edit (Los Angeles [row6]) you should see only 3 options (set by dependent api). However, the first time you click edit all options are unchecked. If you close and click edit again they all are checked (which is the way it should be).

I liked the way dependent works with ajax, really simple and straight forward but I don't it's the right option for my case (please correct me if I'm wrong)
the best thing I came up with is updating the options on row select like this

table.on( 'select', function ( e, dt, type, indexes ) {
    if ( type === 'row' ) {   
      site_id = table.rows( indexes ).data()[0].user.site;
      // do my ajax call 
      // then
      // editor.field('permission[].id').update(options_array);
    }