mJoin Direct Link

Restful Web Services

I have two tables and I am trying to join them so that I can display in a single cell an array of values which have the same ID from one table which match a single ID from the second table. Basically one table contains property owners and the second table contains properties. A single owner might own multiple properties.

If I use leftJoin it works but my table displays a row for each property instead of combining the joined property data into an array in a single row.

Editor::inst( $db, "owners", "Id" )
    ->fields(
        Field::inst( "owners.Id" )
            ->set( false ),
        Field::inst( "owners.category" )
            ->validator( "Validate::notEmpty" ),
        Field::inst( "owners.create_date" )
            ->set( false )
            ->validator( "Validate::notEmpty" )
            ->getFormatter( function ($val, $data, $field) {
                        $val = date("d/m/Y", strtotime($val));
                        return $val;}), 
        Field::inst( "owners.modified_date" )
            ->set( false )
            ->validator( "Validate::notEmpty" )
            ->getFormatter( function ($val, $data, $field) {
                        $val = date("d/m/Y", strtotime($val));
                        return $val;}),     
        Field::inst( "owners.forename" )
            ->validator( "Validate::notEmpty" ),                            
        Field::inst( "owners.surname" )
            ->validator( "Validate::notEmpty" ),
        Field::inst( "owners.username" )
            ->set( false ),
        Field::inst( "owners.telephone" ),
        Field::inst( "owners.address" ),
        Field::inst( "owners.rules" ),
    Field::inst( "properties.property_name" )
        ->set( false )
    )
    ->where("owners.create_date", $from, ">=")
    ->where("owners.create_date", $to, "<=")
    ->where( "owners.category", $category)
    ->leftJoin( 'properties', 'properties.property_owner', '=', 'owners.Id' )
    ->process( $_POST )
    ->json();  

I think I need to use mJoin with a direct link but I cannot find any examples of how to do this? Is this the right approach or not?

I don't actually wish to edit the property names in the other table directly either, I just want it to display.

Many thanks

Chris

allan

Mjoin does sound like what you need. For a direct link you just specify the link() method once, rather than twice which results in the assumption of a link table. Just use something like:

        Mjoin::inst( 'properties' )
            ->link( 'properties.property_owner', 'owners.Id' )
            ->fields(

One thing I would say with a direct link, you want to add ->set( false ) to your Mjoin instance so that the submission can't possibly screw up your joined table. It could delete rows otherwise if you aren't careful :-).

Allan

Restful Web Services

Hi Allan,

That is great, thanks. It works in normal columns but I was hoping to use the data in a child row. When I add d.property_name in my child row function I get the result 'undefined'. Is it possible to add an mJoin result into a child row?

Thanks

Chris

allan

Yes certainly you can. I would suggest adding a console.log of the row's data (row().data()) so you can see what the row's data structure is. It will now include an array so you would need something more like d[i].property_name where i is a loop counter.

Allan

Restful Web Services

Thanks again for your help but I am sorry, I don't understand how to check the console log for the row data.

I have found this in the console,

properties: [{property_name: "11MOA"}, {property_name: "22MHR"}]
0: {property_name: "11MOA"}
property_name: "11MOA"
1: {property_name: "22MHR"}
property_name: "22MHR"

I am happy to share a link if that helps?

Thanks

Restful Web Services

If I use d.properties I get [object Object],[object Object], which is the linked items but obviously I need the names.

Restful Web Services

I have tried this, not sure if it is along the right line?

function format(d) {
var propertyArray = d.properties;
     for ( var i=0, ien=propertyArray.length ; i<ien ; i++ ) { 
            var text = propertyArray[i]['property_name'];
                    alert('value at index [' + i + '] is: [' + propertyArray[i]['property_name'] + ']');           
    };
         return text;
}

The alert works and show both linked properties but when I try and print the string I just get one value in my output.

allan

That looks fine to me.

If you want multiple strings in the output from the elements of the array, just build a string with the values required in your loop.

It is only returning the last item in the array at the moment since the loop runs and then it returns the last assignment to text. Just build a string from those values.

Allan

Restful Web Services

Great thanks, I have it working now.