mJoin problem
mJoin problem
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
Hi Ken,
Thanks for the discription. The issue is with:
It won't automatically apply the
where
condition - in fact it just ends up with a query such asSELECT 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 theoptions
method for 1.5.2 which will be available in around two weeks time.Regards,
Allan
Ah - brilliant, thank you. That explains it perfectly.