Vfrdtyky

I am having trouble figuring this out.

I have the following 3 tables: -

Transactions Table

• ID


• Date


• List item

Products Table

• ID




• Name




• Price

Products_Transactions Table

• Transaction_ID




• Product_ID




• Quantity

So the relationship is as follows - a transaction is made, and then the products_transactions table joins them together since a transaction can have multiple products and a product can have multiple transactions. The join_table also keeps track of the amount sold, so, for instance, a newspaper sells in transaction #1 and with a quantity of 2 (so 2 newspapers sold).

Now, I want to make a MySQL statement that finds all products sold, in a specific date interval, so I get something like this: -

• 3 x Newspapers


• 12 x Sodas


• 15 x Beer

So, it just counts and sums up all the products sold.

I have seriously tried everything - I am working with CakePHP so a solution provided in that would be helpful, but even just the plain SQL to achieve this might help me out.

So far, this is what I have: -

$productTransactionsTable = TableRegistry::get('products_transactions');

    $productsTransactions = $productTransactionsTable->find('all');

    $productsTransactions->matching('transactions', function ($q) {

        return $q->where([

            'transaction_date >=' => new DateTime('-1 week'),

            'transaction_date <=' => new DateTime('now'),

            'device_id IN' => $this->deviceIdsInDepartment(2)

        ]);

    });

    $productsTransactions->contain(['products']);



    $productsTransactions->select([

        'count' => $productsTransactions->func()->count('quantity'),

        'name' => 'products.name'

    ]);



    $productsTransactions->groupBy('products.id');

But this just gives out 1 single result that counts everything together into 1 row, like this:

/src/Controller/EconomyController.php (line 665)

[
(int) 0 => object(CakeORMEntity) {

    'count' => (int) 4504,

    'name' => 'D Morgenbrød',

    '[new]' => false,

    '[accessible]' => [

        '*' => true

    ],

    '[dirty]' => ,

    '[original]' => ,

    '[virtual]' => ,

    '[errors]' => ,

    '[invalid]' => ,

    '[repository]' => 'products_transactions'



}

]

Any help is appreciated! I am seriously stuck here!

Thank you!

I think you are counting the total number of sold quantities and grouping so you are getting above results. I think, you need to try the following approach:

$data = $query

    ->select([

      'name' => 'products.name',

      'quantity' => 'products.quantity',

    ])

    ->group('products.id');