QB - Update Statement Generating Incorrect Code

Box Modules qb
1

I am just getting into using “qb”. I am using PostgreSQL database. I have this code that for some reason is generating an update statement that doesn’t make sense and I’m struggling to do something that seems quite simple with regular SQL or even using the query raw features. Below is my code. I’ve hardcoded the company ID in the “where” below just for testing.

var query = wirebox.getInstance( "QueryBuilder@qb" );

  var companies = query.select( query.raw( "c.company_id, c.name,c.location, c.website, c.industry, c.company_size,c.featured,c.parent_company_id, bt.type_name, COUNT(job_id) as jobCount" ))
    .from( "company as c" )
    .join( "job as j", "c.company_id",  "j.company_id" )
    .join("business_type as bt", "c.business_type_id", "bt.business_type_id")
    .groupBy( "c.name, type_name, c.company_id, c.website" )
    .get();
    
      for (var company in companies) {
        query.from( "company" )
        .where("company_id", 55)
        .update( {
        "last_synced_at" = { value = now(), cfsqltype = "CF_SQL_TIMESTAMP" }
          
        });
        
      }
    }

The code does not work and when I debug I see this SQL generated

The column index is out of range: 4, number of columns: 3.
?
UPDATE "company" SET "last_synced_at" = {ts '2023-12-17 19:43:52'} WHERE "company_id" = {ts '2023-12-17 19:43:52'} AND "company_id" = 55

For some reason it’s adding “company_id” = the Now() value

I must be missing something very simple. Any help appreciated.

2

You can’t reuse qb instances. They are transients that hold state. You either need to inject a new qb instance or call newQuery.

3

I tried that as well actually before posting. This code yields the same result.

 var query = wirebox.getInstance( "QueryBuilder@qb" );


 var companies = query.select( query.raw( "c.company_id, c.name,c.location, c.website, c.industry, c.company_size,c.featured,c.parent_company_id, bt.type_name, COUNT(job_id) as jobCount" ))
    .from( "company as c" )
    .join( "job as j", "c.company_id",  "j.company_id" )
    .join("business_type as bt", "c.business_type_id", "bt.business_type_id")
    .groupBy( "c.name, type_name, c.company_id, c.website" )
    .get();

 var queryTwo = wirebox.getInstance( "QueryBuilder@qb" );
      
      for (var c in companies) {
          
        queryTwo.from( "company" )
        .where("company_id", 55)
        .update( {
         "last_synced_at" = { value = now(), cfsqltype = "CF_SQL_TIMESTAMP" }
          
        });
        
      }

error
error1150×337 68.6 KB

4

You are reusing queryTwo multiple times in your for loop.

5

Ah yes, thank you @elpete! I ended up just moving the:

 var queryTwo = wirebox.getInstance( "QueryBuilder@qb" );

inside my for loop so it creates a new instance at each iteration. I presume this is fine.

Thanks again!