mJoin problem

mJoin problem

kenraynerkenrayner Posts: 11Questions: 4Answers: 0

Hello

Here I have 3 tables: templates, breaks & template_breaks.

Templates has columns ID, name, network_avail.

Breaks has columns ID, time, network_avail.

Template_breaks has columns break & template. A template can contain any number of breaks.

Using one-to-many joins, https://editor.datatables.net/manual/php/mjoin, this seems to work. My problem, though, is in restricting the data based on the avail field. By using:

$editor = Editor::inst( $db, 'templates' )
    ->field(
        Field::inst( 'templates.name' ),
        Field::inst( 'templates.network_avail' )
  )
  ->join(
        Mjoin::inst( 'breaks' )
            ->link( 'breaks.id', 'template_breaks.template' )
            ->link( 'templates.id', 'template_breaks.break' )
            ->fields(
                Field::inst( 'id' )
                    ->validator( 'Validate::required' )
                    ->options( 'breaks', 'id', 'time' ),
                Field::inst( 'time' )
            )
            ->where('breaks.network_avail', $network ) 
    )
  ->where('templates.network_avail', $network)
  ->process( $_POST )
  ->json();

I'm able to restrict the ability to set a break to a template if the network_avail is not $network - but on the "new" or "edit" popup, we still see all the times in the "breaks" table. Effectively, I need to be able to, in the breaks field here (note this JS is rendered by PHP to allow the introduction of the $network variable):

$(document).ready(function() {
    editor = new $.fn.dataTable.Editor( {
        ajax: "../../../assets/et/php/templates.php?network=<?php echo $network ?>",
        table: "#templates-table",
        display: "bootstrap",
        fields: [ {
                label: "Name:",
                name: "templates.name"
            }, {
        "label": "Breaks:",
        "name": "breaks[].id",
        "type": "checkbox"
      }, {
              label: "Network Availability",
              name: "templates.network_avail",
              type: "hidden",
              default: <?php echo $network ?>
            }
        ]
    
    } );

Only displays breaks that have a network_avail of a specific number. It's odd - the setter won't allow you to set those other breaks to the join table (template_breaks) but still shows them as options on the "new" or "edit" popups.

What am I missing?!

Thanks

Ken

This question has an accepted answers - jump to answer

Answers

  • allanallan Posts: 63,356Questions: 1Answers: 10,444 Site admin
    Answer ✓

    Hi Ken,

    Thanks for the discription. The issue is with:

    ->options( 'breaks', 'id', 'time' ),
    

    It won't automatically apply the where condition - in fact it just ends up with a query such as SELECT DISTINCT id, time FROM breaks.

    For more complex queries you need to use a closure function. There is an example of that available on the site - click the "Server script" tab below the table.

    I'm going to add an option to pass where conditions to the options method for 1.5.2 which will be available in around two weeks time.

    Regards,
    Allan

  • kenraynerkenrayner Posts: 11Questions: 4Answers: 0

    Ah - brilliant, thank you. That explains it perfectly.

This discussion has been closed.