php - How to integrate input from DateTimeInterface into Doctrine DBAL query builder?

I am very new to PHP and am working on a plugin for Shopware (5.6). The goal is to extend the shopware statistics by adding a new tab displaying a table of preordered items (article id, amount of preorders, $-value).

The plugin runs successfully and have been able to display the desired info, however I am unable to integrate input from the DateTimeInterface. I want the date input there to correspond with the release date of the preordered items, so I can filter for a specific timeframe and see which preordered articles are due to be delivered during that time.

The following code referrs referrs to the Shopware Controller:

Old code (working, but obviously no DateTimeInterface integration) As a result i get a table with ALL preordered items

declare(strict_types=1);


class Shopware_Controllers_Backend_CustomStatsController extends Shopware_Controllers_Backend_ExtJs {
  /**
   * calls the getPreorderSubsAction function that connects with the database and
   * delivers the content for the statistics table
   *
   * @return void
   */
  public function getPreorderSubsAction() {
    $connection = $this->container->get('dbal_connection');
    $query = $connection->createQueryBuilder();
    $query->select([
      'ps.abo',
      'smao.name',
      'ROUND(SUM(ps.preordered * ps.unit_price),2) AS preorder_value'
      ])
      ->from('vw_PreorderSubs', 'ps')
      ->join('ps', 'salt_model_abo', 'smao', 'ps.abo = smao.id')
      ->where('ps.latest_issue_esd <= NOW()')
      ->groupBy('ps.abo');

    $data = $query->execute()->fetchAll();

    $this->View()->assign([
        'success' => true,
        'data' => $data,
        'count' => count($data)
    ]);
  }
}
Copy code

New code: (not working), when choosing the statistics tab it looks like the query is simply empty, since nothing can be found. But for the chosen timeframe i should be getting a list of 13 items.

<?php
declare(strict_types=1);

class Shopware_Controllers_Backend_SaltCustomStatsController extends Shopware_Controllers_Backend_ExtJs {
  /**
   * calls the getPreorderSubsAction function that connects with the database and
   * delivers the content for the statistics table
   *
   * @return void
   */
  public function getPreorderSubsAction(\DateTimeInterface $from = null, \DateTimeInterface $to = null){
    $connection = $this->container->get('dbal_connection');
    $query = $connection->createQueryBuilder($from, $to);
    $query->select([
      'ps.abo',
      'smao.name',
      'ROUND(SUM(ps.preordered * ps.unit_price),2) AS preorder_value'
      ])
      ->from('vw_PreorderSubs', 'ps')
      ->join('ps', 's_model_abo', 'smao', 'ps.abo = smao.id')
      ->where('ps.latest_issue_esd <= NOW()')
      ->andWhere('ps.order_date <= "?"')
      ->andWhere('ps.order_date >= "?"')
      ->groupBy('ps.abo')
      ->setParameter(0, $from)
      ->setParameter(1, $to)
      ;

    $data = $query->execute()->fetchAll();

    $this->View()->assign([
        'success' => true,
        'data' => $data,
        'count' => count($data)
    ]);
  }
}
Copy code

Variation i tried unsuccessfully:

->from('vw_PreorderSubs', 'ps')
      ->join('ps', 's_model_abo', 'smao', 'ps.abo = smao.id')
      ->where('ps.latest_issue_esd <= NOW()')
      ->andWhere('ps.order_date between "?" and "?"')
      ->groupBy('ps.abo')
      ->setParameter(0, $from)
      ->setParameter(1, $to)
      ;
Copy code

I am sure it is something obvious. Since it doesn't throw an error, the code seems to be working, as if the date input itself is correct, but there are just no results to show.

How do i get the code to accept input from the DateTimeInterface in the Shopware backend correctly and insert it into the query?

The format of the ps.order_date field is YYYY-MM-DD. `

Didn't find the answer?

Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.