[SOLVED] What's wrong in my query ??

[nusilmar]

Sorry to bother you all with this question but i'm struggling on making this work and still wasn't able to.

What i have is the folowing :
Table A ( id, customer, pendingvalue) - a customer table
Table B (id,customerid,docpendvalue) - a document table with a pending value per document

i'm trying to update pendingvalue on Table A ( as a sum of all docpendvalue from Table B)

This is what i'm trying to do ( on Schedule , php : )

// Get the db object
$db = JFactory::getDbo();
 
// Build the query ? Tables names = CASE SENSITIVE
$query = $db->getQuery(true);
 
$query
      -> update('fab_customers AS a')
      -> join('INNER','(SELECT ('customerid, SUM(docpendvalue) AS totpend') FROM ('fab_penddocs')
                              GROUP BY ('customerid')) AS b')
      -> ON ('a.id = b.customerid')
      -> set('a.pendingvalue = (b.totpend)')
           
$db->setQuery($query);
 
// Run the query
$found = (int) $db->execute();

I know that is on the query that i have the problem, what i cannot figure it out is to transform from my sql query ( tested and working) to the query string in php

My sql query is this :

UPDATE      a
SET    a.pendingvalue=b.totpend           
FROM            fab_customers AS a INNER JOIN
                            (SELECT        customerid, SUM(docpendvalue) AS totpend
                              FROM            fab_penddocs
                              GROUP BY customerid) AS b ON a.id = b.customerid

 

[nusilmar]

i've changed my query to this :

// Get the db object
$db = JFactory::getDbo();
// Build the query ? Tables names = CASE SENSITIVE
$query = $db->getQuery(true);
$query1
      ->SELECT (('customerid, SUM(docpendvalue) AS totpend') AS b)
      ->FROM ('fab_penddocs')
      ->GROUP BY ('customerid');
$query
      ->UPDATE ('fab_customers AS a')
      ->SET ('a.pendingvalue=b.totpend')
      ->JOIN('INNER',$query1)
      ->ON ('a.id = b.customerid');
   
$db->setQuery($query);
// Run the query
$found = (int) $db->execute();

But still nothing. I really don't know how to pull this off. Any help ??

 

[nusilmar]

SUCCESS !!!!

new query is working :

// Get the db object
$db = JFactory::getDbo();
// Build the query ? Tables names = CASE SENSITIVE
$query = $db->getQuery(true);
$query="UPDATE fab_customers AS a \n"
    . "INNER JOIN (SELECT customerid, SUM(docpendvalue) AS totpend\n"
    . "FROM fab_penddocs\n"
    . "GROUP BY customerid) AS b\n"
    . "ON a.id = b.customerid\n"
    . "SET a.pendingvalue=b.totpend";
   
       
$db->setQuery($query);
// Run the query
$found = (int) $db->execute();