How to show rows with specific value of one-to-many table join?

bilyejd

I would like to create a table that shows only rows from the database that contain a specific value from a one-to-many table join. I am using the one-to-many table join example from the examples, located on my local server here. What I'd like to do is show only rows that have "Printer" as the access (for example) when the table is first loaded. The end product I would like will be the table shown if you type "Printer" into the table search box.

To do this I have tried the where conditions, and I think the sub-selects is what I really need, but I just can't seem to get it.

I have tried in the joinArray.php file to add a where condition:

<?php

// DataTables PHP library
include( "../../php/DataTables.php" );

// Alias Editor classes so they are easy to use
use
    DataTables\Editor,
    DataTables\Editor\Field,
    DataTables\Editor\Format,
    DataTables\Editor\Mjoin,
    DataTables\Editor\Upload,
    DataTables\Editor\Validate;


/*
 * Example PHP implementation used for the join.html example
 */
Editor::inst( $db, 'users' )
    ->field( 
        Field::inst( 'users.first_name' ),
        Field::inst( 'users.last_name' ),
        Field::inst( 'users.site' )
            ->options( 'sites', 'id', 'name' ),
        Field::inst( 'sites.name' )
    )
    ->leftJoin( 'sites', 'sites.id', '=', 'users.site' )
    ->join(
        Mjoin::inst( 'access' )
            ->link( 'users.id', 'user_access.user_id' )
            ->link( 'access.id', 'user_access.access_id' )
            ->fields(
                Field::inst( 'id' )
                    ->validator( 'Validate::required' )
                    ->options( 'access', 'id', 'name' ),
                Field::inst( 'name' )
            )
        
            ->where('access.name', 'Printer')
            })
    )
    
    
    ->process($_POST)
    ->json();

but this shows all rows, and only the Printer value in the Access column. I've tried some other variations:

->where( function ( $q ) {
                $q->where( 'user_access.access_id', '(SELECT id FROM access WHERE name LIKE "%:value%")', 'IN', false );
                $q->bind(':value', 'Printer');
            })

->where( function ( $q ) {
                $q -> where('access.id', '(SELECT id FROM access WHERE name LIKE "%:value%")', 'IN', false);
                $q -> bind(':value', 'Printer');
            })

but those don't show anything in the Access column. I feel like I'm close. Any help would be appreciated.

allan

Hi,

What you would need here are two sub-selects (or possibly one with a left join)! The first to get the id of the printer, and the second to get the list of ids that contain that id.

So I think you basically have the correct idea with your sub-select, but you need to also consider the user_access table. Perhaps something like:

                $q -> where('access.id', '(SELECT user_id FROM user_access WHERE access_id = (SELECT id FROM access WHERE name = ":value"))', 'IN', false);
                $q -> bind(':value', 'Printer');

On the messy side, but that should work I think.

Allan

Falk

is what Allan has written? With me not.

Sorry, this is a Google translation. :-)

allan

@Falk - I'm afraid I don't understand. Could you expand upon your question please?