Server side searching on Mjoin column

Server side searching on Mjoin column

Restful Web ServicesRestful Web Services Posts: 198Questions: 48Answers: 2

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

Answers

  • allanallan Posts: 48,908Questions: 1Answers: 7,132 Site admin

    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 ServicesRestful Web Services Posts: 198Questions: 48Answers: 2
    edited February 7

    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

Sign In or Register to comment.