addJoinQuery with addCondition

[Prasun2]

Hi Team,

I am trying to use addJoinQuery with gliderecord. 
Expectation is to get sysId based on multipple conditions of characteristic and characteristic_options.
Something like: Give all the Product offering from product offering tables where characteristic is color and characteristicoption is red OR characteristic is capacity and characteristicoption is 1200.

The addcondition() is working as expected with the below code but how do I use a OR operator to get above desired output. 

var po = new GlideRecord('sn_prd_pm_product_offering');

var poc = po.addJoinQuery('table1','sys_id','product_offering');

poc.addCondition('field1','abc');

poc.addCondition('field2','xyz');

var poc1 = po.addJoinQuery('table2','sys_id','product_offering');

poc1.addCondition('field1','bcd');

poc1.addCondition('field2','uvf');

po.query();

gs.info(po.getRowCount());

while(po.next()){

gs.info(po.number);

}

I want to achieve the following:

SELECT ib.SysId

  FROM InstallBase ib

 WHERE ib.SysId IN

       (SELECT IBSysId

          FROM InstallBaseAttrib

         WHERE attributeName = "COLOR"

           AND value = "RED")

   OR ib.SysId IN

       (SELECT IBSysId

          FROM InstallBaseAttrib

         WHERE attributeName = "MILEAGE"

           AND value > 10000)

[Prasun2]

Hi Team,

I am trying to use addJoinQuery with gliderecord. 
Expectation is to get sysId based on multipple conditions of characteristic and characteristic_options.
Something like: Give all the Product offering from product offering tables where characteristic is color and characteristicoption is red OR characteristic is capacity and characteristicoption is 1200.

The addcondition() is working as expected with the below code but how do I use a OR operator to get above desired output. 

var po = new GlideRecord('sn_prd_pm_product_offering');

var poc = po.addJoinQuery('table1','sys_id','product_offering');

poc.addCondition('field1','abc');

poc.addCondition('field2','xyz');

var poc1 = po.addJoinQuery('table2','sys_id','product_offering');

poc1.addCondition('field1','bcd');

poc1.addCondition('field2','uvf');

po.query();

gs.info(po.getRowCount());

while(po.next()){

gs.info(po.number);

}

I want to achieve the following:

SELECT ib.SysId

  FROM InstallBase ib

 WHERE ib.SysId IN

       (SELECT IBSysId

          FROM InstallBaseAttrib

         WHERE attributeName = "COLOR"

           AND value = "RED")

   OR ib.SysId IN

       (SELECT IBSysId

          FROM InstallBaseAttrib

         WHERE attributeName = "MILEAGE"

           AND value > 10000)

[Punit S]

Hi Prasun,

To achieve the desired query, you can use the addOrCondition method of the GlideRecord object to add the OR operator between the two join queries. Here's an example code snippet that should work:

var po = new GlideRecord('sn_prd_pm_product_offering');
var poc = po.addJoinQuery('sn_prd_pm_product_offering_characteristic', 'sys_id', 'product_offering');
poc.addCondition('characteristic', '11e639b20fb00110603bf634a7767e17');
poc.addCondition('characteristic_option', '451da6fe0f340110603bf634a7767e5c');
var poc1 = po.addJoinQuery('sn_prd_pm_product_offering_characteristic', 'sys_id', 'product_offering');
poc1.addCondition('characteristic', 'baa6b5b20fb00110603bf634a7767e3a');
poc1.addCondition('characteristic_option', 'a2ac6ebe0f340110603bf634a7767ec4');

// Add OR condition between the two join queries
po.addOrCondition(poc.getEncodedQuery(), poc1.getEncodedQuery());

po.query();
gs.info(po.getRowCount());
while (po.next()) {
  gs.info(po.number);
}

This code adds the OR condition between the two join queries using the addOrCondition method. The method takes the encoded queries for each join query as arguments. The encoded query can be obtained using the getEncodedQuery method of the GlideRecord object.

Please mark my answer correct/helpful in case it adds value and moves you a step closer to your desired ServiceNow solution goal. 

Thanks,
Punit

[Prasun2]

@Punit S Thanks for the answer. ALthough it doesn't seem to give the desired output. WHen I did a gs.trace(true) for above query it gives raw sql as

SELECT sn_prd_pm_product_offering0.`end_date`,
       sn_prd_pm_product_offering0.`short_description`,
       sn_prd_pm_product_offering0.`description`,
       sn_prd_pm_product_offering0.`external_id`,
       sn_prd_pm_product_offering0.`decision_table`,
       sn_prd_pm_product_offering0.`external_version`,
       sn_prd_pm_product_offering0.`sys_updated_on`,
       sn_prd_pm_product_offering0.`sys_class_name`,
       sn_prd_pm_product_offering0.`number`,
       sn_prd_pm_product_offering0.`initial_version`,
       sn_prd_pm_product_offering0.`sys_id`,
       sn_prd_pm_product_offering0.`sys_updated_by`,
       sn_prd_pm_product_offering0.`previous_version`,
       sn_prd_pm_product_offering0.`sys_created_on`,
       sn_prd_pm_product_offering0.`offer_type`,
       sn_prd_pm_product_offering0.`sys_created_by`,
       sn_prd_pm_product_offering0.`start_date`,
       sn_prd_pm_product_offering0.`product_model`,
       sn_prd_pm_product_offering0.`owner`,
       sn_prd_pm_product_offering0.`sys_mod_count`,
       sn_prd_pm_product_offering0.`display_name`,
       sn_prd_pm_product_offering0.`version`,
       sn_prd_pm_product_offering0.`u_category`,
       sn_prd_pm_product_offering0.`NAME`,
       sn_prd_pm_product_offering0.`product_specification`,
       sn_prd_pm_product_offering0.`status`,
       sn_prd_pm_product_offering0.`pricing_method`
FROM   sn_prd_pm_product_offering sn_prd_pm_product_offering0
WHERE  sn_prd_pm_product_offering0.`sys_id` IN
       (
              SELECT sn_prd_pm_product_o6g_c12c0.`product_offering`
              FROM   sn_prd_pm_product_o6g_c12c sn_prd_pm_product_o6g_c12c0
              WHERE  (
                            sn_prd_pm_product_o6g_c12c0.`characteristic_option` = '451da6fe0f340110603bf634a7767e5c'
                     AND    sn_prd_pm_product_o6g_c12c0.`characteristic` = '11e639b20fb00110603bf634a7767e17'))
AND    sn_prd_pm_product_offering0.`sys_id` IN
       (
              SELECT sn_prd_pm_product_o6g_c12c0.`product_offering`
              FROM   sn_prd_pm_product_o6g_c12c sn_prd_pm_product_o6g_c12c0
              WHERE  (
                            sn_prd_pm_product_o6g_c12c0.`characteristic_option` = 'a2ac6ebe0f340110603bf634a7767ec4'
                     AND    sn_prd_pm_product_o6g_c12c0.`characteristic` = 'baa6b5b20fb00110603bf634a7767e3a'))

Which is an AND

[Punit S]

Try this one, 

var po = new GlideRecord('sn_prd_pm_product_offering');
var poc = po.addJoinQuery('sn_prd_pm_product_offering_characteristic','sys_id','product_offering');
poc.addCondition('characteristic','11e639b20fb00110603bf634a7767e17');
poc.addCondition('characteristic_option','451da6fe0f340110603bf634a7767e5c');

var poc1 = po.addJoinQuery('sn_prd_pm_product_offering_characteristic','sys_id','product_offering');
poc1.addCondition('characteristic','baa6b5b20fb00110603bf634a7767e3a');
poc1.addCondition('characteristic_option','a2ac6ebe0f340110603bf634a7767ec4');

po.addOrCondition(poc, poc1);

po.query();
gs.info(po.getRowCount());

while(po.next()){
  gs.info(po.number);
}

[Prasun2]

Thanks for the answer Punit. 
But that doesn't work as well