MJoin Multiple Databases

MJoin Multiple Databases

Restful Web ServicesRestful Web Services Posts: 202Questions: 49Answers: 2

I have succesfully been using multiple databases in my application by simply adding the database name before the table name like so:

Editor::inst( $db, 'ridedata1-web1-uk.ride', 'ride_id' )

However, when attempting to use the same method with MJoin it is not working and I recieve the following SQL error:

An SQL error occurred: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax

Here is my code:

    ->join(
        Mjoin::inst( 'ridedata1-web1-uk.ride_carbon_data' )
            ->set( false )
            ->link( 'ride.ride_id', 'ride_carbon_data.ride_id' ) 
            ->fields(                                                        
                Field::inst( 'carbon_benefit' )
                    ->set( false )
                    ->getFormatter(function ($val, $data, $field) {
                        $val = ($val / 100);                    
                        return number_format($val, 2, '.', '').'<br> kg / miles';              
                    }),              
                Field::inst( 'distance_replace' )
                    ->set( false )
                    ->getFormatter(function ($val, $data, $field) {
                        $val = ($val / 100);                    
                        return number_format($val, 2, '.', '').'<br> miles';              
                    })                               
            )                                                
    )   

I have used many Mjoins in other places but always when trying to link a table in the same database. I have tried enclosing the database name in ` and " and ' but nothing helps.

Can anyone help me out?

Thanks

Chris

Answers

  • allanallan Posts: 61,439Questions: 1Answers: 10,053 Site admin

    Hi Chris,

    This is an area that isn't well tested in the Editor libraries I'm afraid.

    Could you add ->debug( true ) before the process() call in the Editor chain and then show me the JSON that is being returned by the server when the page is loaded? That should contain the SQL query and let me see why it is failing.

    Allan

  • rf1234rf1234 Posts: 2,801Questions: 85Answers: 406

    Had a similar issue even though it was in the same database. It was about linking two rows from my contract table. One being a "derivative" the other one being the "underlying". Aliasing the table name didn't work. Same problem that you have with this line

    Mjoin::inst( 'ridedata1-web1-uk.ride_carbon_data' )
    

    Obviously Editor doesn't really escape complex names properly in this statement and I needed a simple name that doesn't contain a period or an alias.
    So I created a view called "underlying" and used that in the Mjoin:

    Mjoin::inst( 'underlying' )
    

    This post shows that such a view can even be created across databases. You could call it "carbon_data_view". That should work.
    https://stackoverflow.com/questions/10694601/creating-view-across-different-databases
    Good luck!

  • Restful Web ServicesRestful Web Services Posts: 202Questions: 49Answers: 2

    Hi Both,

    Thanks for your responses. Here is the debug JSON requested by Allan.

    debugSql
    bindings
    [{name: ":where_1", value: "1", type: null}, {name: ":where_2", value: "2", type: null},…]
    
    query
    "SELECT  `freecyc7_ridedata1-web1-uk`.`ride`.`ride_id` as 'freecyc7_ridedata1-web1-uk.ride.ride_id', `ride`.`ride_id` as 'ride.ride_id', `ride`.`create_date` as 'ride.create_date', `ride`.`create_date` as 'ride.create_date', `ride`.`modified_date` as 'ride.modified_date', `ride`.`branch` as 'ride.branch', `ride`.`customer_id` as 'ride.customer_id', `ride`.`customer_id` as 'ride.customer_id', `cmfu`.`first_name` as 'cmfu.first_name', `cmfu`.`last_name` as 'cmfu.last_name', `ride`.`customer_contract_id` as 'ride.customer_contract_id', `cc`.`contract_code` as 'cc.contract_code', `ride`.`type_id` as 'ride.type_id', `ride_type`.`name` as 'ride_type.name', `ride`.`start_latitude` as 'ride.start_latitude', `ride`.`start_longitude` as 'ride.start_longitude', `ride`.`start_time` as 'ride.start_time', `ride`.`finish_latitude` as 'ride.finish_latitude', `ride`.`finish_longitude` as 'ride.finish_longitude', `ride`.`finish_time` as 'ride.finish_time', `ride`.`moving_time` as 'ride.moving_time', `ride`.`maximum_speed` as 'ride.maximum_speed', `ride`.`average_speed` as 'ride.average_speed', `ride`.`distance` as 'ride.distance', `ride`.`description` as 'ride.description' FROM  `freecyc7_ridedata1-web1-uk`.`ride` LEFT JOIN cms_module_feusers_users as cmfu ON `cmfu`.`id` = `ride`.`customer_id`  LEFT JOIN customer_contract as cc ON `cc`.`customer_contract_id` = `ride`.`customer_contract_id`  LEFT JOIN `freecyc7_ridedata1-web1-uk`.`ride_type` ON `ride_type`.`ride_type_id` = `ride`.`type_id` WHERE (`ride`.`branch` = :where_1 AND `ride`.`customer_contract_id` = :where_2 AND `ride`.`ride_id` != :where_3 AND `ride`.`ride_id` != :where_4 )"
    
    query
    "SELECT DISTINCT  `freecyc7_ridedata1-web1-uk`.`ride`.`ride_id` as 'dteditor_pkey', `freecyc7_ridedata1-web1-uk`.`ride_carbon_data`.`carbon_benefit` as 'carbon_benefit', `freecyc7_ridedata1-web1-uk`.`ride_carbon_data`.`distance_replace` as 'distance_replace' FROM  freecyc7_ridedata1-web1-uk.ride as freecyc7_ridedata1-web1-uk.ride  JOIN `freecyc7_ridedata1-web1-uk`.`ride_carbon_data` ON `freecyc7_ridedata1`-web1-uk.ride_carbon_data.ride_id = freecyc7_ridedata1-web1-uk.ride.ride_id "
    
    error
    "An SQL error occurred: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-web1-uk.ride as freecyc7_ridedata1-web1-uk.ride  JOIN `freecyc7_ridedata1-web1-' at line 1"
    fieldErrors
    

    Let me know if you need anything else?

    Thanks

    Chris

  • Restful Web ServicesRestful Web Services Posts: 202Questions: 49Answers: 2
    edited February 2018

    Hi Allan,

    It is clear that the error is on the second query, the SELECT DISTINCT, query. The name is not correctly being encapsulated by the ` escape. However, having had a brief look through the PHP for Editor I cant see the exact place where this encapsulation is being done. Can you help point me in the right direction?

    JOIN `freecyc7_ridedata1-web1-uk`.`ride_carbon_data` ON `freecyc7_ridedata1`-web1-uk.ride_carbon_data.ride_id = freecyc7_ridedata1-web1-uk.ride.ride_id
    

    Thanks

    Chris

  • allanallan Posts: 61,439Questions: 1Answers: 10,053 Site admin

    Thanks Chris,

    This is the statement that is failing:

    SELECT DISTINCT `freecyc7_ridedata1-web1-uk`.`ride`.`ride_id`                      AS 'dteditor_pkey', 
                    `freecyc7_ridedata1-web1-uk`.`ride_carbon_data`.`carbon_benefit`   AS 'carbon_benefit', 
                    `freecyc7_ridedata1-web1-uk`.`ride_carbon_data`.`distance_replace` AS 'distance_replace' 
    FROM            freecyc7_ridedata1-web1-uk.ride                                    AS freecyc7_ridedata1-web1-uk.ride 
    JOIN            `freecyc7_ridedata1-web1-uk`.`ride_carbon_data` 
    ON              `freecyc7_ridedata1`-web1-uk.ride_carbon_data.ride_id = freecyc7_ridedata1-web1-uk.ride.ride_id
    

    The FROM clause isn't being correctly escaped.

    Could you show me your full PHP script for the Editor initialisation please?

    Thanks,
    Allan

  • Restful Web ServicesRestful Web Services Posts: 202Questions: 49Answers: 2

    Thanks for your reply. Can I email it to you?

  • Restful Web ServicesRestful Web Services Posts: 202Questions: 49Answers: 2

    Hi Allan. I have sent it as PM message, let me know if you haven't received it.

  • Restful Web ServicesRestful Web Services Posts: 202Questions: 49Answers: 2

    Hi Allan. Sorry, the first PM I sent you was the wrong one. I have just sent the correct code.

  • allanallan Posts: 61,439Questions: 1Answers: 10,053 Site admin

    Thanks - I've got it now and will review shortly.

    Allan

  • rf1234rf1234 Posts: 2,801Questions: 85Answers: 406

    That looks pretty much like the same thing causing my issue.

    I had something like

    Mjoin::inst( 'contract AS underlying' )
    

    Sinc this wasn't escaped properly I devised the work around with the view. Would be good to hear back from you guys to know whether I'll still need the work around in the future. Many thanks.

  • allanallan Posts: 61,439Questions: 1Answers: 10,053 Site admin

    Yeah - as most certainly won't work in MJoin at the moment I'm afraid. That is something that needs to be added into the libraries.

    I don't believe that is the issue with @Restful Web Services's issue though. It looks like there might be an issue with the detection of the database fields. More soon.

    Allan

  • Restful Web ServicesRestful Web Services Posts: 202Questions: 49Answers: 2

    Thanks for the update.

This discussion has been closed.