Join tables, displaying single value from second table

Restful Web Services

I am not at all sure if I am approaching this correctly so any advice will be great. I wish to combine information from two tables using server side processing. Presently my php script is as follows:

// Build our Editor instance and process the data coming from _POST
Editor::inst( $db, 'u_monitors', 'monitor_id' )
    ->fields(
        Field::inst( 'monitor_id' )
            ->validator( 'Validate::unique', array( 'empty' => false ) ),
        Field::inst( 'user_id' )
            ->validator( 'Validate::notEmpty' ),
        Field::inst( 'monitor_type' )
            ->validator( 'Validate::notEmpty' ),
        Field::inst( 'monitor_name' )
            ->validator( 'Validate::notEmpty' ),
        Field::inst( 'remark' ),
        Field::inst( 'active' )
            ->validator( 'Validate::notEmpty' ),
        Field::inst( 'create_time' )
            ->validator( 'Validate::notEmpty' ),
        Field::inst( 'modified_time' ),
    )
    ->where( 'user_id', $_POST['user_id'] ) 
    ->process( $_POST )
    ->json();

I wish to add a new column 'status' which can be found in a second table 'u_user_103'. I wish to show the latest entry in the column 'status' from that second table when the row matches the 'monitor_id' column in the first table.

Table 'u_monitors' structure:

monitor_id
user_id
monitor_type
monitor_name
remark
active
create_time
modified_time

Table 'u_user_103' structure:

Id
user_id
monitor_id
monitor_name
monitor_type
response_code
user_message
create_date
string
status 

When monitor_id in 'u_monitors' matches monitor_id in 'u_user_103' and it is the newest entry I wish to display the status entry.

Is this possible with JOIN LEFT? I am not concerned with being able to edit the 'status' entry, I just wish to display it in my datatable.

Many thanks

allan

it is the newest entry I wish to display the status entry.

It is this bit that the Editor libraries might struggle with a bit. I'm trying to think how you would determine what the latest enter is without using a sub-select. You can certainly do a where on the joined table, but that might not be enough here. I'll have a bit of a think about it, but if you have any ideas I might be able to explain who they could be used in the libraries (if they can be).

The other option is to use a one-to-many join and then on the client side sort the data to get the latest - but that wouldn't be very efficient...

Allan

Restful Web Services

Hi Allan,

What value would show be default in the joined 'status' column? The value is always either 'Good' or 'Warning'. When you join are multiple values shows or just one?

The table is sorted newest first.

Chris

allan

When you join are multiple values shows or just one?

If you do a one-to-many join then an array of all matching join values will be given.

However, that isn't optimal as I say since you are returning data that you don't need. Indeed if your joined table is large it could be a serious performance penalty. The best way is to do a left join that selects only the latest values - but I'm still struggling to think of a way to do that...

Allan

Restful Web Services

So there is no 'limit' option available with the join?

allan

No - the way Editor gets the one-to-many data is to use a single query rather than a query for every row. Tradeoff between performance and memory.

Allan

Restful Web Services

What about some kind of string match function? If find 'Good' do this or if find 'Warning' do this? Will that still grab every row?

allan

Applying a conditional operator to reduce the rows is the problem. If that were trivial we'd just take the latest row.

Allan

Restful Web Services

What about using multiple WHERE statements? The column create_date will we update every 10 minutes so could we use that in someway?

First we can say only show monitor_id from table 'u_user_103' which matches monitor_id from table 'u_monitors' and then only show results where 'create_date' equals TODAY in table 'u_user_103'?

That would significantly reduce the number of rows we are calling to a maximum of 140ish?

Restful Web Services

Hi Allan,

This post https://datatables.net/forums/discussion/24840/date-range-filter-with-join-server-side-throws-errors seems to suggest you can use a date range filter within a join statement however I am not sure how to implement this solution into my code. I haven't used a join statement in datatables before and my attempts are not working presently.

Thanks

Chris

allan

Yes you can do that. The where() method can be used to apply a query condition on the joined tables. I'm not sure that would be exactly what you are looking for, unless all records in the join table are updated in exactly 10 minute intervals. I'm sure there is a better way, but I need to find some time to experiment and time is being elusive at the moment I'm afraid.

Allan

Restful Web Services

Hi Allan,

Yes I hope there is a better way! If you do find some time to experiment I would really appreciate your input. Many thanks.

Chris

allan

Hi Chris,

Sorry for the slight delay in being able to dig into this properly. The DataTables and Editor releases yesterday took most of my time for the last little while!

Anyway, what you can do in Editor 1.4 is make use of the new ability to pass an anonymous function to the where() method. That function has access to the Query instance that Editor uses so we can directly manipulate the query using its own methods (rather than being limited to the Editor->where() method).

This is useful in this case as the Query->where() method provides the option to not bind the second part of the query condition. The binding is basically a safe escaping mechanism so if there is no user input we can bypass this, giving the ability to pass in good old plain SQL.

Finally we get to the point! This means that we can use a sub-select as the query condition :-).

Consider for example the following modification of my simple join examples:

    ->where( function ( $q ) {
        $q->where( 'sites.id', '(SELECT id FROM sites WHERE name LIKE "%e%")', 'IN', false );
    } )

In SQL this means we get:

WHERE sites.id IN (SELECT id FROM sites WHERE name LIKE "%e%")

A fairly trivial and relatively pointless example from the point of view of a query, but illustrative for how to use a sub-query in Editor.

So in your own case you would use the sub-select to select all of the rows from the joined table that you want:

    ->where( function ( $q ) {
        $q->where( 'u_user_103.Id', '(SELECT Id FROM u_user_103 GROUP BY monitor_id ORDER BY create_date DESC)', 'IN', false );
    } )

The sub-query should get the Id for the latest record for each monitor_id (I think - I don't have your data set to test it, so it might need some tweaking!).

Regards,
Allan

Restful Web Services

Hi Allan,

Many thanks for your help. I have had a go but no luck so far. Can you take a look through my code please and see if you can see where I am going wrong? (For your information I have decided to just try and use the join statement to display the 'create_time' value from the table u_user_103.

Editor::inst( $db, 'u_monitors', 'monitor_id' )
    ->fields(
        Field::inst( 'u_monitors.monitor_id' )
                ->set( false )
            ->validator( 'Validate::unique', array( 'empty' => false ) ),
        Field::inst( 'u_monitors.user_id' )
                ->set( false )
            ->validator( 'Validate::notEmpty' ),            
        Field::inst( 'u_monitors.monitor_type' )
            ->validator( 'Validate::notEmpty' ),
        Field::inst( 'u_monitors.monitor_name' )
            ->validator( 'Validate::notEmpty' ),
        Field::inst( 'u_monitors.string' ),         
        Field::inst( 'u_monitors.remark' ),
        Field::inst( 'u_monitors.active' )
            ->validator( 'Validate::notEmpty' ),
        Field::inst( 'u_monitors.create_time' )
            ->set( false )
            ->validator( 'Validate::notEmpty' )
            ->getFormatter( function ($val, $data, $field) {
                        $val = date("d/m/Y \a\\t H:i", strtotime($val));
                        return $val;}),
        Field::inst( 'u_user_103.create_date' )
            ->set( false )
            ->getFormatter( function ($val, $data, $field) {
                        $val = date("d/m/Y \a\\t H:i", strtotime($val));
                        return $val;}),
        Field::inst( 'u_monitors.status' )
            ->set( false )
    )
    ->where( 'user_id', $_POST['user_id'] ) 
    ->where( function ( $q ) {
            $q->where( 'u_user_103.Id', '(SELECT Id FROM u_user_103 GROUP BY monitor_id ORDER BY create_date DESC)', 'IN', false );
    })
    ->leftJoin( 'u_user_103', 'u_user_103.monitor_id', '=', 'u_monitors.monitor_id' );
    ->process( $_POST )
    ->json();

Many thanks

Chris

allan

Have you tried running the sub select that I suggested directly against the database? As I noted, I haven't tested it and that would appear to be the most obvious part to me where something could be going wrong.

Allan

Restful Web Services

Hi Allan,

Two things.

1. The leftJoin is sort of working. There is a problem with the GROUP BY in that the action is performed before the ORDER BY and therefore only the oldest row id's are captured.

2. I was under the impression I would be able to add a variable into the table name in the normal php way, i.e. SELECT Id FROM 'u_user_$user_id' where $user_id is defined from my POST var using $user_id = $_POST['user_id']; . However, this does not work? Can you not use variables in table names?

Thanks

Chris

Restful Web Services

I have managed to find a working solution to my initial problem using the following sub select statement:

SELECT Id FROM u_user_103 WHERE Id = ( SELECT Id FROM u_user_103 as alt WHERE alt.monitor_id = u_user_103.monitor_id ORDER BY create_date DESC LIMIT 1 ) ORDER BY create_date

Unfortunately, I still cannot find a way to dynamically set the table name.

allan

I was under the impression I would be able to add a variable into the table name in the normal php way

Sure - I don't see a problem with that (although remember to escape your user input). It won't work with a direct copy of my code above of course though since it uses single quotes and PHP variables are only expended when using double quotes. See the PHP documentation if you weren't aware of this.

Allan

Restful Web Services

Hi Allan,

It works fine here:

->leftJoin( "u_user_$user_id", "u_user_$user_id.monitor_id", "=", "u_monitors.monitor_id" )

But this won't work,

->where( function ( $q ) { $q->where( "u_user_$user_id.Id", "(SELECT Id FROM u_user_$user_id WHERE Id = ( SELECT Id FROM u_user_$user_id as alt WHERE alt.monitor_id = u_user_$user_id.monitor_id ORDER BY create_date DESC LIMIT 1 ) ORDER BY create_date)", "IN", false ); })

Any ideas why not?

Thanks

Restful Web Services

Found a solution, I had to re-declare my $user_id var within the ->where function. Don't know if this is the correct solution but it seems to be working?

->where( function ( $q ) {
$user_id = $_POST["user_id"];
$q->where( "u_user_$user_id.Id", "(SELECT Id FROM u_user_$user_id WHERE Id = ( SELECT Id FROM u_user_$user_id as alt WHERE alt.monitor_id = u_user_$user_id.monitor_id ORDER BY create_date DESC LIMIT 1 ) ORDER BY create_date)", "IN", false );
})

Do you think my sub select is a good, 'efficient', solution or not?

Many thanks for all your help

Chris

allan

I would suggest using the use() option of PHP's closure functions:

->where( function ( $q ) use ( $user_id ) {
  ...
} )

PHP manual.

Regarding the sub-sub select. that looks fine. There probably is a more efficient way of doing it, but I'm not sure what it is! Unless you have millions or rows likely that will be plenty fast enough.

Allan