Get usage count of foreign key

alchemi

Hi,

I have this file called table.ref.php that sets up the editor for my list of references (as in journal articles) in table 'ref' like so:

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

// Build our Editor instance and process the data coming from _POST
Editor::inst( $db, 'ref', 'id' )
    ->fields(
        Field::inst( 'id' ),
        Field::inst( 'author' ),
        Field::inst( 'year' ),
        Field::inst( 'title' ),
        Field::inst( 'journal' ),
        Field::inst( 'etcetera' ),
        Field::inst( 'count' )->getValue( 9 ),
        Field::inst( 'user' )
    )
    ->where( 'user', $_SESSION['user'] )
    ->process( $_POST )
    ->json();

Now for the field 'count' I would like to return the number of times each reference is used in another table called 'cases'. Each case only ever has one reference, but one reference can be used for multiple cases. Is there an easy way to replace the number 9 in above code snippet with the actual number of cases that the reference is cited for?

rf1234

This should do the job:

// Alias Editor classes so they are easy to use
use
    DataTables\Editor,
    DataTables\Editor\Field,
    DataTables\Editor\Format,
    DataTables\Editor\Mjoin,
    DataTables\Editor\Options,
    DataTables\Editor\Upload,
    DataTables\Editor\Validate,
    DataTables\Editor\ValidateOptions;
 
// Build our Editor instance and process the data coming from _POST
Editor::inst( $db, 'ref', 'id' )
    ->fields(
        Field::inst( 'id' ),
        Field::inst( 'author' ),
        Field::inst( 'year' ),
        Field::inst( 'title' ),
        Field::inst( 'journal' ),
        Field::inst( 'etcetera' ),
        Field::inst( 'id AS count' )->set( false )
            ->getFormatter( function ( $val, $data, $opts ) use ( $db ) {
                $result = $db->raw()
                    ->bind( ':ref_id', $val )
                    ->exec( 'SELECT COUNT(*) AS countFK
                               FROM cases
                              WHERE ref_id = :ref_id');
                $row = $result->fetch(PDO::FETCH_ASSOC);
                return $row["countFK"];   
            } ),
        Field::inst( 'user' )
    )
    ->where( 'user', $_SESSION['user'] )
    ->process( $_POST )
    ->json();

alchemi

Dear rf1234,

thank you so much for your quick and detailed response. I really like that you show how to use SQL to count the rows, it may come in useful in the future to know this technique.

In the mean time I decided to go with another solution I found on the datatables blog:

// Alias Editor classes so they are easy to use
use
    DataTables\Editor,
    //...code omitted for readability

// Build our Editor instance and process the data coming from _POST
Editor::inst( $db, 'ref', 'id' )
    ->fields(
        Field::inst( 'id' ),
        Field::inst( 'author' ),
        Field::inst( 'year' ),
        Field::inst( 'title' ),
        Field::inst( 'journal' ),
        Field::inst( 'etcetera' ),
        Field::inst( 'user' )
    )
    ->join(
        Mjoin::inst( 'cases' )
            ->link( 'ref.id', 'cases.ref_id' )
            ->fields(
                Field::inst( 'id' )
            )
    )
    ->where( 'user', $_SESSION['user'] )
    ->process( $_POST )
    ->json();

This returns an array of ids of cases for each row in the json response like

cases:[{"id":"123"}]

and in my table.ref.js file I show the count with a very simple function:

{
    "data": "cases",
    render: function ( data ) {
        return data.length;
    }
}

If you like, I'd appreciate you explaining the different advantages/disadvantages between the two approaches for anyone visiting this forum question in the future, so people can decide what will work best for their use case.

rf1234

Good to hear you found a solution!
The only theoretical disadvantage is that you are sending all the ids to the client and make the client count them.
The SQL-solution avoids this and could therefore be a little faster. (and the mjoin executes SQL as well of course)
Knowing SQL I find my solution very simple while yours requires reading the docs