-2

Please Help to build a query using CAKEPHP 3.X though i am using cakephp 3.8 now.

I have a table Like:

enter image description here

And Another Table such as:

enter image description here

They are not associated each other. I am not getting any idea of developing below results in CAKEPHP 3.

SQL QUERY is as follows:

SELECT *, SUM(t2.amount) AS last7DaysAmount FROM TableA AS t1 LEFT JOIN TableB AS t2 ON t1.customer = t2.customer AND t1.supplier = t2. supplier AND t2.billing_date >= new Date('-7 Days') AND t2.country = t1.country and t2.service = t1.service

How can i get the sum of Amounts from Table B to Select Statement of Table A For Last 7 Days Using CakePHP 3 ORM/Query Builder LIKE So.

enter image description here

Thanks For Helping me in advance

Bayezid Alam
  • 270
  • 5
  • 17
  • How are these 2 tables linked via Client or Vendor? – ascsoftw Sep 30 '19 at 05:10
  • client, vendor, service and country – Bayezid Alam Sep 30 '19 at 05:14
  • So you want to create a New column in Table A and populate it using values in Table B? – ascsoftw Sep 30 '19 at 05:17
  • I want to get the result [last7daysAmount] with table A in the select Query – Bayezid Alam Sep 30 '19 at 05:20
  • You can get all the Data only from Table B, there is no need for Table A. – ascsoftw Sep 30 '19 at 05:21
  • There are other columns available in Table A, which i need to. – Bayezid Alam Sep 30 '19 at 05:23
  • can it be possible to get the sum of last 7 day Amounts with the select statement of Table A From Table B? Please help – Bayezid Alam Sep 30 '19 at 07:15
  • Have any idea how plain sql query looks like? – Salines Sep 30 '19 at 07:22
  • @Salines, Thanks. i could not understand how to make this Query as the relationships are many LIKE client, vendor, service and country but i want it with table A. please assist. Thanks :) – Bayezid Alam Sep 30 '19 at 07:43
  • Yes, for idea read: https://stackoverflow.com/questions/4337993/mysql-query-for-summing-values-in-another-table and https://stackoverflow.com/questions/8018550/mysql-join-with-multiple-conditions and https://book.cakephp.org/3.0/en/orm/query-builder.html#adding-joins – Salines Sep 30 '19 at 08:08
  • Thanks, I did `SELECT *, SUM(t2.amount) AS last7DaysAmount FROM TableA AS t1 LEFT JOIN TableB AS t2 ON t1.customer = t2.customer AND t1.supplier = t2. supplier AND t2.billing_date >= new Date('-7 Days') AND t2.country = t1.country and t2.service = t1.service` but could not populate using CAKEPHP 3 – Bayezid Alam Sep 30 '19 at 09:08

1 Answers1

2

I give you a basic idea of what to do:

$a = TableRegistry::getTableLocator()->get('a', ['table' => 'TableA']); // use your table "TableA" as alias "a"
$query = $a->find();
$result = $query->enableAutoFields(true) // select all fields
    ->select(['Last7DaysAmount' => $query->func()->sum('b.amount')]) // select sum('b.amount') AS Last7DaysAmount
    ->where(['b.date >' => DATE(NOW() - INTERVAL 7 DAY)]) // where b.date greater than current minus 7 days
    ->join([ // join other table
        'table' => 'TableB',
        'alias' => 'b',
        'type' => 'left',
        'conditions' => [ // with conditions
            'a.client' => 'b.customer',
            'a.vendor' => 'b.supplier',
            'a.service' => 'b.service',
            'a.country' => 'b.country',
            ]
     ])
    ->group([ // group results by
            'a.client',
            'a.vendor',
            'a.service',
            'a.country',
    ])
    ->order(['a.client' => 'ASC'])
    ->limit(100);

    $this->set('results', $results);

This query looks like:

MariaDB [testdb]> SELECT a.id AS `a__id`, a.client AS `a__client`, a.vendor AS `a__vendor`, a.service AS `a__service`, a.country AS `a__country`, a.destination AS `a__destination`, (SUM(b.amount)) AS `Last7DaysAmount`
-> FROM tableA a 
-> LEFT JOIN tableB b ON (a.client = b.customer AND a.vendor = b.supplier AND a.service = b.service AND a.country = b.country)
-> GROUP BY a.client, a.vendor, a.service, a.country  
-> ORDER BY a.client 
-> ASC LIMIT 100;


+-------+-----------+-----------+------------+------------+----------------+-----------------+
| a__id | a__client | a__vendor | a__service | a__country | a__destination | Last7DaysAmount |
+-------+-----------+-----------+------------+------------+----------------+-----------------+
|     1 | client1   | vendor1   | Gold       | spain      | spain          |             106 |
|     2 | client1   | vendor3   | Gold       | spain      | spain          |              64 |
|     3 | client2   | vendor2   | silver     | germany    | germany        |              16 |
+-------+-----------+-----------+------------+------------+----------------+-----------------+

3 rows in set (0.001 sec)

But, why CakePHP return 'Last7DaysAmount' as null is another question.

Salines
  • 5,674
  • 3
  • 25
  • 50