Get usage count of foreign key

Get usage count of foreign key

alchemialchemi Posts: 11Questions: 3Answers: 0

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?

This question has an accepted answers - jump to answer

Answers

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

    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();
    
  • alchemialchemi Posts: 11Questions: 3Answers: 0

    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.

  • rf1234rf1234 Posts: 2,801Questions: 85Answers: 406
    edited February 2020

    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 :smile:

This discussion has been closed.