Server side searching on Mjoin column

Restful Web Services

I am aware that currently server side searching on a Mjoin column is not supported out of the box.

However, I need this so I am trying to explore what the most efficient solution at this stage might be in terms of minimal code and ease of maintenance.

One idea I had was to create a new companion table to the primary table, called something like table_name_meta_data, and use the postCreate and postEdit events on the server side to insert the relevant joined data as a simple plain text string into table_name_meta_data.

Then, use a simple leftJoin on the primary key column from the primary table to the companion table table_name_meta_data and output the data in a field which matches the name of the Mjoin field. (Doing this means that the search from the client side just works, well it did in my experimentation anyway).

Obviously I would rather not have effectively the same piece of data in the database twice. Equally I do want a solution which is easily transferable to other datatables which doesn't require lots of bespoke client side code.

Just interested if anyone thinks this is a good solution or their are better ideas available?

Thanks

allan

The way I would approach this myself is to create a VIEW that builds a table that contains an array of the rows for the mjoin in the host row. If I were targeting a single db, then that's what I would have done with the Editor libraries, but since I need to support multiple different dbs, that wasn't possible.

When you have a view that matches what you need, you can simply query that like a normal table.

Allan

Restful Web Services

Hi Allan,

Great idea, I should have considered a VIEW. I have implemented this and it is working great.

Here is an example.

You should already have a Mjoin, like this for my categories.

                ->join(
                    Mjoin::inst( 'system_resource_category' )
                        ->link( 'system_resource.resource_id', 'system_resource_category_map.resource_id' )
                        ->link( 'system_resource_category.resource_category_id', 'system_resource_category_map.resource_category_id' )
                        ->order( 'name asc' )
                        ->fields(
                            Field::inst( 'resource_category_id' )
                                ->validator( Validate::required() )
                                ->options( Options::inst()
                                    ->table( 'system_resource_category' )
                                    ->value( 'resource_category_id' )
                                    ->label( 'name' )
                                    ->where( function ($q) {
                                        $q->where( 'type_id', '(1)', 'IN', false );
                                    })   
                                ),
                           Field::inst( 'name' )
                        )
                )  

Create a VIEW in MySQL, this example puts categories into a comma separated list within the column system_resource_category.

CREATE VIEW system_resource_meta AS 
SELECT system_resource.resource_id, GROUP_CONCAT(DISTINCT(system_resource_category.name)) as system_resource_category
FROM system_resource
LEFT JOIN system_resource_category_map ON system_resource_category_map.resource_id = system_resource.resource_id
LEFT JOIN system_resource_category ON system_resource_category.resource_category_id = system_resource_category_map.resource_category_id
GROUP BY system_resource.resource_id;

Add a leftJoin which links your primary table to the new VIEW.

->leftJoin( 'system_resource_meta', 'system_resource_meta.resource_id', '=', 'system_resource.resource_id' )

Finally, create a new field which uses the joined data and ensure it has the exact same name as the Mjoin. Make sure you use ->set( false ) otherwise editor operations will break.

                   Field::inst( 'system_resource_meta.system_resource_category as system_resource_category' )
                        ->set( false ),  

Hope that is useful to someone else.

Chris