Score:1

Schema insert row only if primary key doesn't exist

at flag

I'm doing an exercise where I have a table called visit_counter with fields uid (primary key that stores the user id) and user_visit_count (sum of user's page visits). The purpose of the table is to store the user id and to count the times the user visits the page that loads the controller.

My problem is that my if condition is not working well. I'm trying to insert the row with the uid (primary key, user id), however, I need to execute the insert only after I have validated that the uid doesn't exist.

The following graphic is what I expect. To be able to check the stored values in the visit_counter table (sorry if the image is too big, I don't know how to resize it). In this exercise I'm forced to use insert or update. PHP My Admin: table visit_counter

Mymodule.install

/**
 * Implements hook_schema().
 */
function mymodule_schema() {
  $schema['visit_counter'] = [
    'description' => 'Descripción counter',
    'fields' => [
      'uid' => [
        'type' => 'int',
        'not null' => TRUE,
        'default' => 0
      ],
      'user_visit_count' => [
        'type' => 'int',
        'not null' => TRUE,
        'default' => 0
      ]
    ],
    'primary key' => ['uid'],
  ];

  return $schema;
}

src/Controller/MyModuleController.php.

<?php

namespace Drupal\mymodule\Controller;
use Drupal\Core\Controller\ControllerBase;

use Symfony\Component\DependencyInjection\ContainerInterface;
use Drupal\Core\Session\AccountInterface;
use Drupal\Core\Database\Connection;

/**
 * Returns responses for MyModule Database routes.
 */
class MyModuleController extends ControllerBase {
  protected $database;
  protected $currentUser;

  public function __construct(Connection $database, AccountInterface $current_user) {
    $this->database = $database;
    $this->currentUser = $current_user;
  }
  
  public static function create(ContainerInterface $container) {
    return new static(
        $container->get('database'),
        $container->get('current_user'),
    );
  }

  /**
   * Builds the response.
   */
  public function build() {
    $connection = $this->database;
    $currentUser = $this->currentUser;
    $visit = 1;

    // Here I pretend to retrieve the row that contains the user uid 
    $query = $connection->select('visit_counter', 't1')
        ->fields('t1', ['uid'])
        ->condition('uid', $uid, '=');
    $result = $query->execute();

    // Try to check result
    dpm($result);

    // if $result is false, then execute insert. But the problem is that it never execute it.
    if (!$result) {
      $insert = $connection->insert($dbTable)
        ->fields(['uid', 'user_visit_count']);
    
      $insert->values([
        'uid' => $uid,
        'user_visit_count' => $visit
      ]);
    
      $insert->execute();
    }

    $update = $connection->update($dbTable)
      ->expression('user_count', 'user_count + :visit', [':visit' => $visit]);
    $update->execute();

    $build['content'] = [
      '#type' => 'item',
      '#markup' => $this->t('It works!'),
    ];

    return $build;
  }
Score:1
us flag

The shown code creates a new row, if a row for that user ID does not exist, or updates the existing row. For that, there is the Merge class.

In the question case, the code to update the database row would be the following one.

$connection->merge('visit_counter')
  ->key('uid', $currentUser->id())
  ->fields(['user_visit_count' => $visit])
  ->expression('user_visit_count', 'user_visit_count + :inc', [':inc' => 1])
  ->execute();

fields() is used to insert a database row, if a row for that user ID does not exist; expression() is used to update the existing database row, if there is already a row for that user ID.

I sit in a Tesla and translated this thread with Ai:

mangohost

Post an answer

Most people don’t grasp that asking a lot of questions unlocks learning and improves interpersonal bonding. In Alison’s studies, for example, though people could accurately recall how many questions had been asked in their conversations, they didn’t intuit the link between questions and liking. Across four studies, in which participants were engaged in conversations themselves or read transcripts of others’ conversations, people tended not to realize that question asking would influence—or had influenced—the level of amity between the conversationalists.