mJoin Limit

Restful Web Services

Hi,

I am using the mJoin class and wondering if anyone has figured out a way to add LIMIT? This doesn't work, but for example:

Editor::inst( $db, 'staff' )
    ->field(
        Field::inst( 'staff.name' ),
        Field::inst( 'staff.role' )
    )
    ->join(
        Mjoin::inst( 'access' )
            ->link( 'staff.id', 'staff_access.staff_id' )
            ->link( 'access.id', 'staff_access.access_id' )
            ->order( 'access.name asc' )
            **->LIMIT('1')**
            ->fields(
                Field::inst( 'id' )
                    ->options( 'access', 'id', 'name' )
                    ->validator( 'Validate::notEmpty' ),
                Field::inst( 'name' )
            )
    )
    ->process($_POST)
    ->json();

I only want to show the most recent item from the child table.

Any ideas?

Thanks

Restful Web Services

Sorry, found a working solution now. For anyone else interested I used double nesting in a subquery within the mJoin like this:

    ->join(
        Mjoin::inst( 'customer_commitment' )
            ->set( false )
            ->link( 'customer.customer_id', 'customer_commitment.customer_id' )            
            ->order( 'customer_commitment.create_date desc' ) 
            ->fields(
                    Field::inst( "create_date" )
                        ->set( false ),    
                    Field::inst( 'funding_amount' )
                        ->set( false )             
                        })        
            )
            ->where( function ( $q ) {
                $q->where( 'customer_commitment_id', '(SELECT customer_commitment_id from (select customer_commitment_id FROM `customer_commitment` ORDER BY `customer_commitment_id` DESC LIMIT 1) x)', 'IN', false );
            })       
    )  

allan

Sneaky! There isn't currently a properly supported API for that kind of thing, but that's a clever workaround.

Allan

Restful Web Services

Hi Allan,

Unfortunately, it doesn't actually work correctly. It appears that the subquery only runs once for the whole table as opposed to once per table row.

The above example will only give me data for the first row of the table. If I increase the limit to 2 and each customer only has one record I get the second rows data as well, and so on. However, if customer 1 has 2 records then I get both of those and nothing for row 2.

Anyway to make it work properly, i.e. make it row specific? At the moment I am using a count on the client side, i.e. per row if count equals 0 then show data and if not then don't. This works per row.

                        "render": function ( data, type, full ) {
                            if ( ! full.product_media || ! full.product_media.length ) {
                                return '-';
                            }else{
                                var count = 0;                            
                                $data = data.sort(function(a, b){return a.position > b.position? 1: -1;});                            
                                return $.map( data, function ( d, i ) {
                                    if (count == '0'){                                
                                    count++;
                                    return '<img src=' + d.path + ' />';
                                    }
                                }).join( '<br>' );                            
                            }                 
                        },

Chris

allan

It appears that the subquery only runs once for the whole table as opposed to once per table row.

Yes, that would be the case. Rather than having a query per row, which might result in thousands of queries, a single query is executed and the data marge is done in PHP. It is intentionally designed that way for performance and there isn't a way to override it I'm afraid.

What I think you would need here is a callback for that data merge. If you have a look in Join.php around like 570 you'll see a for loop. That is where the merge is done. Its using a pkey map to help optimise the performance, so its possible that might need to change, but currently that would be the only option when loading the data.

The only other thing I could suggest is that you Ajax load the Mjoined data separately. Perhaps something like in this blog post.

Allan