Combining multiple select, leftjoin and render

Restful Web Services

I am attempting to change a standard single select into a multiple select. The value from the select is an Id which I then use a Leftjoin on to show a human readable value. Like so,

 "render" : function(data, type, row) {
    return row.cms_module_system_categories.name;
}

When I have multiple values separated by a comma this function fails. I have attempted to create a loop so I can access each of the Id values individually to run the Leftjoin function on but it doesn't seem to work.

"render" : function(data, type, row) {
    var text = '';
    var string = row.cms_module_system_activities.activity_category;
    var categoryArray = string.split(',');
    for (var i = 0, ien = categoryArray.length; i < ien; i++) {
        text += row.cms_module_system_categories.name;
    }
    return text;
}

Is what I am trying to do possible this way, or anyway?

Thanks

Chris

allan

Hi Chris,

Try:

{
  data: 'cms_module_system_categories[, ].name'
}

That should join an array of objects, plucking the 'name' property out and concatenating them with a comma+space.

I don't think you need to use a render function here - just use the data property for the column.

Allan

Restful Web Services

Hi Allan,

I have finally had a go at this and unfortunately it doesn't work as expected.

I have, for example, a database cell which contains these values 779,780,786 which I want to print in human readable form. With a single value I do this,

->leftJoin( 'cms_module_system_clients', 'cms_module_system_clients.Id', '=', 'cms_module_system_bookings.client_id' )

Field::inst( "cms_module_system_clients.forename" )
            ->set( false ),
Field::inst( "cms_module_system_clients.surname" )
            ->set( false )

Which gives me a single human readable name.

If I do this with multiple values it fails.

Is there any other way to loop a comma separated list of a values in a single cell and render the value into something else?

allan

There isn't really a way to do that with the Editor PHP libraries. I'm not even sure how you would do that with plain SQL to be honest. My suggestion would be to split that into a 'link table' which you can perform a one-to-many join on. That would be the "SQL" way of doing it I think.

The other option is to get a list of all names and then to a split and lookup either on the client-side or server-side.

Allan

Restful Web Services

Hi Allan,

Thanks for your reply. I guess the link table is the way to go. I tried setting up a link table with the columns Id, booking_code, client_id

The booking_code is the reference to find the client_ids and then for each client id I reference the clients table to get the name details.

This doesn't work though,

    ->join(
        Mjoin::inst( 'cms_module_system_clients' )
            ->set( false )
            ->link( 'cms_module_system_bookings.booking_code', 'cms_module_system_booking_members.booking_code' )
            ->link( 'cms_module_system_clients.Id', 'cms_module_system_booking_members.client_id' )
            ->fields(
                Field::inst( 'Id' ),
                Field::inst( 'forename' ),
                Field::inst( 'surname' )
            )
    )

I get this error "{"sError":"Join was performed on the field 'booking_code' which was not included in the Editor field list. The join field must be included as a regular field in the Editor instance."}"

What have I done wrong?

Thanks

allan

Sounds like you just need to add Field::inst( 'booking_code' ) into your main field list.

Allan

Restful Web Services

Great, thanks that did the trick. I should have figured that out!